MySQL8.0使用sys.statement_performance_analyzer()排查性能问题

简介

在MySQL8.0中提供了提供了许多性能排查的表，视图，工具等。其中statement_performance_analyzer()则是sys库下的一个存储过程，用于生成events_statements_summary_by_digest表的两个快照，并对比两个快照，生成增量报告，这对于查看高峰时系统在执行哪些查询非常有用。

参数

参数如下表

参数	有效值	描述
action	Snapshot：创建快照，默认events_statements_summary_by_digest，可通过table参数修改 Overall：基于table参数指定的表生成分析报告， Delta：生成增量分析报告，增量是基于table参数与已有的快照进行对比生成 create_tmp：创建临时表，可用于后续计算增量 create_table：创建普通表，可用于后续计算增量 save：保存快照到table参数指定的表中 cleanup：移除用于快照和增量的临时表	执行此存储过程的动作描述
table	<schema>.<table>	用于需要表名的操作，格式为<schema>.<table>,不能使用反引号，schema与table之间不能出现点
views	with_runtimes_in_95th_percentile:使用statements_with_runtimes_in_95th_percentile视图 analysis:使用statement_analysis视图 with_errors_or_warnings：使用statements_with_errors_or_warnings视图 with_full_table_scans:使用 statements_with_full_table_scans视图. with_sorting:使用statements_with_sorting视图 with_temp_tables:使用statements_with_temp_tables视图 custom:使用自定义视图	可以包含多个视图名，用逗号隔开

常见使用用法

用法一 生成statement_analysis增量报告

创建一个statement_analysis增量报告，，具体步骤如下：

1. 创建临时表存储初始化的快照

   -- 不记录当前线程操作
   CALL sys.ps_setup_disable_thread(CONNECTION_ID());
   -- 创建存放快照的schema
   create database if not exists monitor;
   -- 创建临时表
   CALL sys.statement_performance_analyzer('create_tmp', 'monitor.tmp_ini', NULL);
   

2. 生成初始化快照

   CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
   

3. 保存初始化快照在临时表中

   CALL sys.statement_performance_analyzer('save', 'monitor.tmp_ini', NULL);
   

4. 等待一分钟

   DO SLEEP(60);
   

5. 创建新快照

   CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
   

6. 基于新快照与初始化快照进行增量性能分析

   CALL sys.statement_performance_analyzer('delta', 'monitor.tmp_ini', 'analysis')\G
   

7. 清理环境

   CALL sys.statement_performance_analyzer('cleanup', NULL, NULL);
   DROP TEMPORARY TABLE monitor.tmp_ini;
   CALL sys.ps_setup_enable_thread(CONNECTION_ID());
   

用法二 自定义视图

使用自定义视图显示按总执行时间排序的前10个查询，在Linux中使用watch命令每分钟刷新视图,具体步骤如下：

1. 创建自定义视图

   DROP DATABASE IF EXISTS monitor;
   CREATE DATABASE monitor;
   CREATE OR REPLACE VIEW monitor.my_statements AS
          SELECT sys.format_statement(DIGEST_TEXT) AS query,
                 SCHEMA_NAME AS db,
                 COUNT_STAR AS exec_count,
                 sys.format_time(SUM_TIMER_WAIT) AS total_latency,
                 sys.format_time(AVG_TIMER_WAIT) AS avg_latency,
                 ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
                 ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
                 ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg,
                 DIGEST AS digest
            FROM performance_schema.events_statements_summary_by_digest
          ORDER BY SUM_TIMER_WAIT DESC;
   

2. 生成普通表

   CALL sys.statement_performance_analyzer('create_table', 'monitor.digests_prev', NULL);
   

3. watch监控

   shell> watch -n 60 "mysql sys --table -e \"
    SET @sys.statement_performance_analyzer.view = 'monitor.my_statements';
    SET @sys.statement_performance_analyzer.limit = 10;
    CALL statement_performance_analyzer('snapshot', NULL, NULL);
    CALL statement_performance_analyzer('delta', 'monitor.digests_prev', 'custom');
    CALL statement_performance_analyzer('save', 'monitor.digests_prev', NULL);
    \""
   

参考

• https://dev.mysql.com/doc/refman/8.0/en/sys-statement-performance-analyzer.html