31.1.1 使用 MySQL 性能模式

Query Analyzer数据是通过监控MySQL Server 5.6.14或更高版本自动收集和显示的，使用MySQL 5.6中添加 的Performance Schema Statement Digests特性（ Performance Schema Statement Digests and Sampling ）。

笔记

无法从 MySQL 5.6.14 之前的 MySQL 服务器版本检索语句摘要数据。

从 Performance Schema 收集查询分析器数据提供了有关语句如何生成其结果集的数据：

  • 表锁定时间

  • 检查了多少行与返回了多少行

  • 创建了多少个临时表，以及是否在磁盘上创建了任何临时表

  • 是否进行了范围扫描，以何种形式进行

  • 排序是否发生，排序了多少行，排序采用什么形式

MySQL Enterprise Monitor Agent 轮询 performance_schema.events_statements_summary_by_digest 表（默认情况下每分钟一次）并持续计算在快照窗口期间公开的每个规范化语句的增量。这取决于 performance_schema.setup_consumers在 MySQL 5.6 中启用了“statements_digest”消费者的性能模式设置： 

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

如果未启用，则启用它： 

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

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.