7.1.1 Using the MySQL Performance Schema

As of MySQL Enterprise Monitor 3.0.0, Query Analyzer data is automatically collected and displayed by simply monitoring MySQL Server 5.6.14 or greater, and without any additional plugins required. This ability comes from the Performance Schema Statement Digests feature (Performance Schema Statement Digests) that was added in MySQL 5.6. If you are using an earlier version of MySQL Server (5.6.13 or below), then you can continue to use a Connector Plugin or MySQL Proxy to provide performance information to the Query Analyzer.

Note

MySQL server versions prior to MySQL 5.6.14 are disabled due to a crashing bug within Statement Digests that could be triggered by collecting the data from the Agent.

Collecting Query Analyzer data from Performance Schema, rather than at the wire protocol (which is how the other sources of Query Analyzer data work) provides data about what the statements do to generate their result sets that other sources cannot provide:

There is also information not available when operating in this mode that is provided to the Query Analyzer when using Connector Plugins and MySQL Proxy:

When enabled (which is the default), the MySQL Enterprise Monitor Agent will poll the performance_schema.events_statements_summary_by_digest table (every minute, by default) and continually compute 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

The 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. This differs from the MySQL Proxy or Connector Plugins, which report the maximum run time per the aggregated snapshot period.

Note

The digested Performance Schema query limit is 1,024 characters in length, in that queries exceeding this length will be truncated. For more information, see Section B.1, “MySQL Enterprise Monitor Limitations”.

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 system 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 that 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 will automatically start fresh.

When 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 there will always be an example statement provided since by default the events_statements_history_long table is a ring buffer of the last 1000 statements executed. This too differs from the Connector Plugin and MySQL Proxy sources, which always provide an example per normalized statement, per snapshot, when enabled. We collect in this way with 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 (approximatly 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 will attempt 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 will not parse correctly when running the EXPLAIN.