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

ゞ浴缸里的玫瑰 2023-03-01 12:58 8阅读 0赞

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

## 简介 ##

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

## 参数 ##

参数如下表

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

## 常见使用用法 ##

### 用法一 生成`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