MySQL :: MySQL 9.0 参考手册 :: 30.4.4.25 statement_performance_analyzer() 过程

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / statement_performance_analyzer() 过程

30.4.4.25 statement_performance_analyzer() 过程

创建服务器上运行的语句的报告。这些视图是根据整体和/或增量活动计算的。

此过程通过操作 sql_log_bin 系统变量的会话值来禁用其执行期间的二进制日志记录。这是一个受限操作，因此此过程需要足够的权限来设置受限会话变量。请参阅第 7.1.9.1 节，“系统变量权限”。

参数

in_action ENUM('snapshot', 'overall', 'delta', 'create_tmp', 'create_table', 'save', 'cleanup'): 要执行的操作。允许以下值
- snapshot: 存储快照。默认情况下，会创建 Performance Schema events_statements_summary_by_digest 表的当前内容的快照。通过设置 in_table，可以将其覆盖为复制指定表的内容。快照存储在 sys 模式 tmp_digests 临时表中。
- overall: 根据 in_table 指定的表内容生成分析。对于整体分析，in_table 可以是 NOW() 以使用最新的快照。这会覆盖现有的快照。使用 NULL 作为 in_table 以使用现有的快照。如果 in_table 是 NULL 且不存在快照，则会创建一个新的快照。 in_views 参数和 statement_performance_analyzer.limit 配置选项会影响此过程的操作。
- delta: 生成增量分析。增量是在 in_table 指定的参考表和必须存在的快照之间计算的。此操作使用 sys 模式 tmp_digests_delta 临时表。 in_views 参数和 statement_performance_analyzer.limit 配置选项会影响此过程的操作。
- create_table: 创建一个适合存储快照以供日后使用的常规表（例如，用于计算增量）。
- create_tmp: 创建一个适合存储快照以供日后使用的临时表（例如，用于计算增量）。
- save: 将快照保存到 in_table 指定的表中。该表必须存在且具有正确的结构。如果不存在快照，则会创建一个新的快照。
- cleanup: 删除用于快照和增量的临时表。
in_table VARCHAR(129): 用于 in_action 参数指定的一些操作的表参数。使用格式 db_name.tbl_name 或 tbl_name，无需使用任何反引号 (`) 标识符引号字符。数据库和表名称不支持句点 (.)。
每个 in_action 值的 in_table 值的含义在各个 in_action 值说明中详细介绍。
in_views SET ('with_runtimes_in_95th_percentile', 'analysis', 'with_errors_or_warnings', 'with_full_table_scans', 'with_sorting', 'with_temp_tables', 'custom'): 要包含的视图。此参数是一个 SET 值，因此它可以包含多个视图名称，用逗号分隔。默认情况下，包括除 custom 之外的所有视图。允许以下值
- 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_performance_analyzer.view 配置选项指定查询或现有视图的名称。

配置选项

statement_performance_analyzer() 操作可以使用以下配置选项或其对应的用户定义变量修改（参见第 30.4.2.1 节，“sys_config 表”）

debug, @sys.debug
如果此选项为 ON，则生成调试输出。默认值为 OFF。
statement_performance_analyzer.limit, @sys.statement_performance_analyzer.limit
对于没有内置限制的视图，要返回的最大行数。默认值为 100。
statement_performance_analyzer.view, @sys.statement_performance_analyzer.view
要使用的自定义查询或视图。如果选项值包含空格，则将其解释为查询。否则，它必须是查询 Performance Schema events_statements_summary_by_digest 表的现有视图的名称。如果 statement_performance_analyzer.limit 配置选项大于 0，则查询或视图定义中不能有任何 LIMIT 子句。如果指定视图，请使用与 in_table 参数相同的格式。默认值为 NULL（没有定义自定义视图）。

示例

要创建一份报告，其中包含自上次截断 events_statements_summary_by_digest 以来处于第 95 个百分位的查询，以及具有 1 分钟的增量周期。

创建一个临时表来存储初始快照。
创建初始快照。
将初始快照保存到临时表中。
等待 1 分钟。
创建新的快照。
根据新的快照执行分析。
根据初始快照和新的快照之间的增量执行分析。

mysql> CALL sys.statement_performance_analyzer('create_tmp', 'mydb.tmp_digests_ini', NULL);
Query OK, 0 rows affected (0.08 sec)

mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> CALL sys.statement_performance_analyzer('save', 'mydb.tmp_digests_ini', NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> DO SLEEP(60);
Query OK, 0 rows affected (1 min 0.00 sec)

mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile');
+-----------------------------------------+
| Next Output                             |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.05 sec)

...

mysql> CALL sys.statement_performance_analyzer('delta', 'mydb.tmp_digests_ini', 'with_runtimes_in_95th_percentile');
+-----------------------------------------+
| Next Output                             |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.03 sec)

...

创建第 95 个百分位查询和执行完全表扫描的排名前 10 个查询的总体报告

mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> SET @sys.statement_performance_analyzer.limit = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile,with_full_table_scans');
+-----------------------------------------+
| Next Output                             |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.01 sec)

...

+-------------------------------------+
| Next Output                         |
+-------------------------------------+
| Top 10 Queries with Full Table Scan |
+-------------------------------------+
1 row in set (0.09 sec)

...

使用自定义视图显示按总执行时间排序的排名前 10 个查询，并使用 Linux 中的 watch 命令每分钟刷新一次视图

mysql> CREATE OR REPLACE VIEW mydb.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;
Query OK, 0 rows affected (0.10 sec)

mysql> CALL sys.statement_performance_analyzer('create_table', 'mydb.digests_prev', NULL);
Query OK, 0 rows affected (0.10 sec)

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

Every 60.0s: mysql sys --table -e "        ...  Mon Dec 22 10:58:51 2014

+----------------------------------+
| Next Output                      |
+----------------------------------+
| Top 10 Queries Using Custom View |
+----------------------------------+
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
| query             | db    | exec_count | total_latency | avg_latency | rows_sent_avg | rows_examined_avg | rows_affected_avg | digest                           |
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
...