MySQL Workbench Manual  /  Performance Tools  /  Performance Schema Reports

7.2 Performance Schema Reports

Performance schema based reports provide insight into the MySQL server operations through helpful high-level reports. MySQL Workbench uses the SYS views on the Performance Schema to generate over 20 reports to help analyze the performance of your MySQL databases. Reports help analyze IO hotspots, discover high cost SQL statements, and review wait statistics and InnoDB engine metrics. For additional information about the SYS schema, see MySQL sys Schema.

Note

This feature requires MySQL Server 5.6 or higher.

Installation and Configuration

A GUI for configuring and fine tuning the Performance Schema instrumentation. Initially, this loads an "Easy Setup" page that is enough for most users. Slide the "Performance Schema Full Enabled" slider to YES to enable all available Performance Schema instruments.

The SYS schema is bundled with MySQL Server 5.7 and above, and MySQL Workbench uses that version. However, for MySQL Server 5.6, Workbench installs its own bundled version of the SYS schema.

Note

This feature requires MySQL Server 5.6 or higher.

Note

The size of the saved digested query is determined by the MySQL server.

Figure 7.2 Performance Schema Setup: Easy Setup

Performance Schema Setup: Easy Setup

Clicking Show Advanced provides methods to fine tune the Performance Schema instrumentation.

Figure 7.3 Performance Schema Setup: Introduction

Performance Schema Setup: Introduction

Performance Report Controls

Performance report data can be viewed and exported using the following controls:

  • Export: Export all entries and associated data (and column headings) from the current performance report, which includes all queries and values. Opens a file dialog for export.

  • Copy Selected: Copies a single entry and associated data (and column headings) from the current performance report. Saves to the system's clipboard. An example:

  • Copy Query: Copies the SQL query that generated the performance report. Saves to the system's clipboard.

  • Refresh: Refreshes (reloads) the performance report.

Performance Report Descriptions

Figure 7.4 Performance Reports: Top I/0 By Bytes

Performance Reports: Top I/0 By Bytes

The available performance reports include (this is a non-exhaustive list):

  • Top File I/O Activity Report: Show the files performing the most IOs (in bytes)

  • Top I/O by File by Time: Show the highest IO usage by file and latency

  • Top I/O by Event Category: Show the highest IO Data usage by event categories

  • Top I/O in Time by Event Categories: Show the highest IO time consumers by event categories

  • Top I/O Time by User/Thread: Show the top IO time consumers by user/thread

  • Statement Analysis: Lists statements with aggregated statistics

  • Statements in Highest 5 Percent by Runtime: Lists the top 5% statements with the highest runtime (in microseconds),

  • Using Temp Tables: Lists all statements that use temporary tables -- accesses the highest number of disk temporary tables, then memory temp tables

  • With Sorting: List all normalized statements that have done sorts, and accesses them in the following priority order -- sort_merge_passes, sort_scans, then sort_rows

  • Full Table Scans: Lists statements that performed a full table scan. Accesses query performance and the where clause(s), and if no index is used then it recommends adding indexes for large tables

  • Errors or Warnings: Lists statements that have raised errors or warnings

  • Schema Object Overview (High Overhead): Shows counts by object type for each schema

    Note

    This can take a long time to execute on instances with a large number of objects.

  • Schema Index Statistics

  • Schema Table Statistics

  • Schema Table Statistics (with InnoDB buffer)

  • Tables with Full Table Scans: Finds tables that are being accessed by full table scans, ordering them by the number of rows scanned (DESC)

  • Unused Indexes: List of indexes that were never used since the server started or since P_S data collection started

  • Waits by Time: Lists the top wait events by their total time, ignoring idle (this often contain large values)

  • Waits by User by Time: Lists the top wait events by their total time, ignoring idle (this often contain large values)

  • Wait Classes by Time: Lists the top wait classes by total time, ignoring idle (this often contain large values)

  • Waits Classes by Average Time: Lists the top wait classes by average time, ignoring idle (this often contain large values)

  • InnoDB Buffer Stats by Schema: Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, aggregating it by schema

  • InnoDB Buffer Stats by Table: Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, aggregating it by schema and table name


User Comments
Sign Up Login You must be logged in to post a comment.