适用范围

mysql5.7 mysql8.0

问题概述

同事反馈SQM收集mysql性能信息不准，相关描述如下：

1. 当前slowlog中存在较多的慢查询，且属于topsql/高频sql
2. SQM从perfomance_schema(简称【ps】)库的events_statements_summary_by_digest(简称【digest】)表中收集sql性能信息
3. 【digest】表数据有变化，但却收集不到当前slowlog中的topsql

问题原因

排查思路：

1. slowlog不会说谎
2. 可能是ps库采集项/存储项没有打开
3. 可能是【digest】表采集有问题

逐项排查： #查看采集项(正常)：

mysql> select * from setup_instruments where NAME LIKE '%events_statements%';
+------------------------------------------------------------------------------+---------+-------+
| NAME                                                                         | ENABLED | TIMED |
+------------------------------------------------------------------------------+---------+-------+
| memory/performance_schema/events_statements_summary_by_account_by_event_name | YES     | NO    |
| memory/performance_schema/events_statements_summary_global_by_event_name     | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_host_by_event_name    | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name  | YES     | NO    |
| memory/performance_schema/events_statements_history                          | YES     | NO    |
| memory/performance_schema/events_statements_history.tokens                   | YES     | NO    |
| memory/performance_schema/events_statements_history.sqltext                  | YES     | NO    |
| memory/performance_schema/events_statements_current                          | YES     | NO    |
| memory/performance_schema/events_statements_current.tokens                   | YES     | NO    |
| memory/performance_schema/events_statements_current.sqltext                  | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_user_by_event_name    | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_digest                | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_digest.tokens         | YES     | NO    |
| memory/performance_schema/events_statements_history_long                     | YES     | NO    |
| memory/performance_schema/events_statements_history_long.tokens              | YES     | NO    |
| memory/performance_schema/events_statements_history_long.sqltext             | YES     | NO    |
| memory/performance_schema/events_statements_summary_by_program               | YES     | NO    |
+------------------------------------------------------------------------------+---------+-------+

#查看存储项(正常)：

mysql> select * from setup_consumers where name like '%statements%';
+--------------------------------+---------+
| NAME                           | ENABLED |
+--------------------------------+---------+
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | NO      |
| statements_digest              | YES     |
+--------------------------------+---------+

#查看相关事件信息表：

mysql> show tables like 'events_statement%';
+----------------------------------------------------+
| Tables_in_performance_schema (events_statement%)   |
+----------------------------------------------------+
| events_statements_current                          |
| events_statements_history                          |
| events_statements_history_long                     |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest                |
| events_statements_summary_by_host_by_event_name    |
| events_statements_summary_by_program               |
| events_statements_summary_by_thread_by_event_name  |
| events_statements_summary_by_user_by_event_name    |
| events_statements_summary_global_by_event_name     |
+----------------------------------------------------+

#了解相关事件信息表特性(细节参考文末链接)：

1. *_current表中记录正在运行的线程，每个线程保留一条记录。
2. *_history表中记录每个线程已经执行完的事件信息，每个线程的历史事件信息只记录10条，超过则覆盖。
3. *_history_long表中记录所有线程的历史事件信息，总记录数是10000行，超过则覆盖。
4. *_summary_by_digest表中记录SQL替换了绑定变量后的累积信息。总记录超过10000条，则不再新插入数据。

到这儿，基本已经定位到问题了，由于【digest】表的存储规则限制，导致了新采集的数据无法插入。

实验验证

#在my.cnf中修改【digest】表的最大记录条数，重启mysql

[mysqld]
performance_schema_digests_size=5

#查看变量值

mysql> show variables like 'performance_schema_digests_size';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| performance_schema_digests_size | 5     |
+---------------------------------+-------+

#窗口1: 清空【digest】表

mysql> truncate table events_statements_summary_by_digest;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from events_statements_summary_by_digest;