The Performance Schema Reports feature from MySQL Workbench show summaries of the many statistics gathered by the MySQL performance_schema. By inspecting these reports, you can get some insight of what’s happening in a MySQL server in aspects such as:
- I/O by amount of data
- I/O by latency/time spent
- Index usage
- Performance critical operations performed by queries of the same type (table scans, index scans, temporary tables, sorts etc)
The MySQL SYS Schema
MySQL 5.5 introduced the performance_schema feature. performance_schema contains tables that log server performance and activity statistics. You can inspect it to have a clearer understanding about what kind of work is the server doing, how much time is spent doing that, resources used globally or by individual queries etc. MySQL 5.6 expanded on it and adds even more information, making it a lot more powerful.
However, the performance_schema tables mostly contain raw data and require some processing and SQL magic to make it palatable so you can get the most of it. And that is what the new SYS schema project provides. The SYS schema has many views that process data in performance_schema providing high-level reports and stored procedures that make it easier for advanced users to work with the performance_schema.
The SYS schema is bundled in MySQL Workbench 6.1 and is – after confirmation – automatically installed in supported MySQL servers. Currently, only MySQL 5.6 and 5.7 are supported, because the performance_schema in MySQL 5.5 still lacked maturity and did not yet provide enough information to be very useful.
Requirements
To view the reports, the SYS schema must be installed and the appropriate P_S instrumentation has to be enabled. If not, Workbench will prompt you to click a button to enable it.
The required instrumentation is (if you choose to change it manually):
- current and history_long consumers must be enabled for all events
- all wait instruments must be enabled and timed
- all stage instruments must be enabled and timed
- all statement instruments must be enabled and timed
Reports
The currently available reports are listed below. As more views are added to the SYS schema, Workbench will also be updated to list them.
- Top File I/O Activity Report
Show the Files doing the most IOs in bytes - Top I/O by File by Time
Show 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 various aggregated statistics - Statements in Highest 5 Percent by Runtime
List all statements who’s average runtime, in microseconds is in highest 5 percent - Using Temp Tables
Lists all statements that use temporary tables – access the highest # of disk temporary tables, then memory temp tables - With Sorting
List all normalized statements that have done sorts, access in the following priority order – sort_merge_passes, sort_scans and sort_rows - Full Table Scans
Lists statements that have performed a full table scan. Access query performance and the where clause(s) and if no index is used, consider adding indexes for large tables - Errors or Warnings
List statements that have raised errors or warnings. - Schema Object Overview (High Overhead)
Shows count by object type for each schema.
Note: On instances with a large number of objects, this can take some time to execute. - Schema Index Statistics
- Schema Table Statistics
- Schema Table Statistics (with InnoDB buffer)
- Tables with Full Table Scans
Find tables that are being accessed by full table scans ordering by the number of rows scanned descending - 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 may be very large) - Waits by User by Time
Lists the top wait events by their total time, ignoring idle (this may be very large) - Wait Classes by Time
Lists the top wait classes by total time, ignoring idle (this may be very large) - Waits Classes by Average Time
Lists the top wait classes by average time, ignoring idle (this may be very large) - InnoDB Buffer Stats by Schema
Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, aggregating by schema - InnoDB Buffer Stats by Table
Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, aggregating by schema and table name
