32.1.1 Using the MySQL Performance Schema

Query Analyzer data is automatically collected and displayed by monitoring MySQL Server 5.6.14 or greater, using the Performance Schema Statement Digests feature (Performance Schema Statement Digests and Sampling) added in MySQL 5.6.

Note

It is not possible to retrieve statement digest data from MySQL server versions prior to MySQL 5.6.14.

Collecting Query Analyzer data from Performance Schema provides data about how statements generate their result sets:

• Table Lock time

• How many rows were examined versus returned

• How many temporary tables were created, and whether any were created on disk

• Whether range scans were done, and in what form they were done

• Whether sorting happened, how many rows were sorted, and what form the sort took

MySQL Enterprise Monitor Agent polls the performance_schema.events_statements_summary_by_digest table (every minute, by default) and continually computes the deltas for each of the normalized statements that are exposed during the snapshot window. This is dependent on the Performance Schema setup having the "statements_digest" consumer enabled within performance_schema.setup_consumers, which is enabled by default in MySQL 5.6:

mysql> SELECT * FROM performance_schema.setup_consumers WHERE name = 'statements_digest';
+-------------------+---------+
| NAME              | ENABLED |
+-------------------+---------+
| statements_digest | YES     |
+-------------------+---------+

If this is not enabled, then enable it with:

UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'statements_digest';

Note

MySQL Enterprise Monitor Agent does not TRUNCATE the performance_schema.events_statements_summary_by_digest table each time it reads from it, as it is possible there may be other processes/tools consuming this data. Because of this, the Max Latency statistic that is reported per a normalized statement within Query Analyzer is actually the maximum since either the MySQL Server started, or since a TRUNCATE TABLE performance_schema.events_statements_summary_by_digest was executed.

Note

The maximum space available for digest computation is 1024 bytes by default; queries exceeding this length are truncated.

As of MySQL 5.7.8, and later, and 5.6.26, and later, this value can be changed at server startup by setting the performance_schema_max_digest_length system variable. In MySQL 5.6.24, 5.6.24, 5.7.6, and 5.7.7, use max_digest_length instead. For MySQL 5.7 versions prior to 5.7.6, the value cannot be changed. Nor can it be changed for MySQL 5.6 versions prior to 5.6.24.

The performance_schema.events_statements_summary_by_digest table is a sized table in memory within the Performance Schema, and its size is auto-configured. To check the current size:

mysql> SHOW GLOBAL VARIABLES LIKE 'performance_schema_digests_size';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| performance_schema_digests_size | 5000  |
+---------------------------------+-------+

If your application executes more than this number of normalized statements, then it is possible that you may begin losing some statement instrumentation. You can monitor this situation with the Performance_schema_digest_lost variable:

mysql> SHOW GLOBAL STATUS LIKE 'Performance_schema_digest_lost';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Performance_schema_digest_lost | 0     |
+--------------------------------+-------+

If you detect that this counter variable is growing, consider increasing the performance_schema_digests_size system variable. It is also possible that your statement profile has changed over time, and you are now executing different statements than were originally tracked (this is especially possible in very long running instances). In this case, you can simply TRUNCATE TABLE performance_schema.events_statements_summary_by_digest, and the Query Analyzer collection automatically starts again.

If the Example Query feature is enabled, Query Analyzer attempts to get an example of the longest running statement during the snapshot interval by doing a LEFT JOIN with a groupwise-max on the performance_schema.events_statements_summary_by_digest table to the performance_schema.events_statements_history_long table. Using this method does not guarantee that an example statement is always provided because, by default, the events_statements_history_long table is a ring buffer of the last 1000 statements executed. Data is collected in this way from Performance Schema to minimize load on the monitored instance rather than polling the performance_schema.events_statements_history_long table at too high a frequently to try and gather statistics.

Note

A small subset (approximately 2MB of data) of the snapshot of known prior values is retained in-memory, and the rest is spooled to disk. The spool is stored in $MYSQL_AGENT_HOME/spool/queryAnalysis.

The Example Query feature requires that the events_statements_history_long table is enabled within performance_schema.setup_consumers (this is disabled by default within MySQL 5.6):

mysql> SELECT * FROM performance_schema.setup_consumers where name = 
'events_statements_history_long';
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_statements_history_long | NO      |
+--------------------------------+---------+

If this is not enabled, then enable it with:

UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 
'events_statements_history_long';

When Example Query and Example Explain are enabled, the MySQL Enterprise Monitor Agent attempts to run an EXPLAIN for each example statement that is discovered and ran for longer than the Auto-Explain Threshold. Due to the way that Performance Schema exposes normalized statements, truncating any normalized statement that is longer than 1024 bytes due to memory concerns within the MySQL Server means it is possible that an EXPLAIN may fail because the truncated statements do not parse correctly when running the EXPLAIN.