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

数据运维 2024-02-23 穿过生命散发芬芳手机阅读

1.参数说明

#action:执行此存储过程的动作描述
Snapshot：创建快照，默认events_statements_summary_by_digest，可通过table参数修改
Overall：基于table参数指定的表生成分析报告，
Delta：生成增量分析报告，增量是基于table参数与已有的快照进行对比生成
create_tmp：创建临时表，可用于后续计算增量
create_table：创建普通表，可用于后续计算增量
save：保存快照到table参数指定的表中
cleanup：移除用于快照和增量的临时表

#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:使用自定义视图

2.方法一：生成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

root@db 15:25: [(none)]> CALL sys.statement_performance_analyzer('delta', 'monitor.tmp_ini', 'analysis')\G
*************************** 1. row ***************************
Next Output: Top 100 Queries Ordered by Total Latency
1 row in set (0.01 sec)

*************************** 1. row ***************************
query: SHOW TABLES
db: testdb
full_scan:
exec_count: 1
err_count: 0
warn_count: 0
total_latency: 1.29 ms
max_latency: 2.41 ms
avg_latency: 1.29 ms
lock_latency: 5.00 us
cpu_latency: 0 ps
rows_sent: 2
rows_sent_avg: 2
rows_examined: 8
rows_examined_avg: 8
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 2
sort_merge_passes: 0
max_controlled_memory: 624.06 KiB
max_total_memory: 922.77 KiB
digest: f5f9379fdd2f199049426ccb51bcabedfca9117cc96c58f51b155f7f2390450f
first_seen: 2024-02-21 17:03:30.064456
last_seen: 2024-02-23 15:24:59.696712
*************************** 2. row ***************************
query: SHOW SCHEMAS
db: NULL
full_scan: *
exec_count: 1
err_count: 0
warn_count: 0
total_latency: 1.02 ms
max_latency: 6.54 ms
avg_latency: 1.02 ms
lock_latency: 5.00 us
cpu_latency: 0 ps
rows_sent: 7
rows_sent_avg: 7
rows_examined: 29
rows_examined_avg: 29
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 7
sort_merge_passes: 0
max_controlled_memory: 1.45 MiB
max_total_memory: 1.62 MiB
digest: dbea8921b559b8cdd4976d57d70304daa8a242fbbfe6df94427006b54ead6b0f
first_seen: 2024-02-21 17:03:21.826757
last_seen: 2024-02-23 15:24:52.229621
*************************** 3. row ***************************
query: SELECT SCHEMA ( )
db: NULL
full_scan:
exec_count: 1
err_count: 0
warn_count: 0
total_latency: 178.26 us
max_latency: 216.32 us
avg_latency: 178.26 us
lock_latency: 0 ps
cpu_latency: 0 ps
rows_sent: 1
rows_sent_avg: 1
rows_examined: 1
rows_examined_avg: 1
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
max_controlled_memory: 1.02 MiB
max_total_memory: 1.19 MiB
digest: ce04eda080d3f5605113e1d0d47c2f20e23b091e2487ba586f07b32cda423942
first_seen: 2024-02-21 17:03:25.898857
last_seen: 2024-02-23 15:24:56.864840
*************************** 4. row ***************************
query: SELECT @@`version_comment` LIMIT ?
db: NULL
full_scan:
exec_count: 1
err_count: 0
warn_count: 0
total_latency: 141.72 us
max_latency: 307.69 us
avg_latency: 141.72 us
lock_latency: 0 ps
cpu_latency: 0 ps
rows_sent: 1
rows_sent_avg: 1
rows_examined: 1
rows_examined_avg: 1
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
max_controlled_memory: 16.33 KiB
max_total_memory: 62.13 KiB
digest: 44e35cee979ba420eb49a8471f852bbe15b403c89742704817dfbaace0d99dbb
first_seen: 2024-02-21 17:03:06.022671
last_seen: 2024-02-23 15:24:09.357682
*************************** 5. row ***************************
query: SELECT SYSTEM_USER ( )
db: NULL
full_scan:
exec_count: 1
err_count: 0
warn_count: 0
total_latency: 68.92 us
max_latency: 76.22 us
avg_latency: 68.92 us
lock_latency: 0 ps
cpu_latency: 0 ps
rows_sent: 1
rows_sent_avg: 1
rows_examined: 1
rows_examined_avg: 1
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
max_controlled_memory: 20.33 KiB
max_total_memory: 58.38 KiB
digest: c9871d1fb65a3d7607814b7e30013cad125215e23e190a170c9f2451ed6fb163
first_seen: 2024-02-21 17:03:06.023732
last_seen: 2024-02-23 15:24:09.358718
5 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

6).清理环境

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

3.方法二：自定义视图

使用自定义视图显示按总执行时间排序的前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监控

[root@node1 ~]# watch -n 60 "mysql -uroot -pP@ssw0rd --table -D sys -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');
> \""

Every 60.0s: mysql -uroot -pP@ssw0rd --table -D sys -e " Fri Feb 23 15:13:02 2024

mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+
| summary |
+-------------------+
| Disabled 1 thread |
+-------------------+
+------------------+
| summary |
+------------------+
| Enabled 1 thread |
+------------------+
+-------------------+
| summary |
+-------------------+
| Disabled 1 thread |
+-------------------+
+----------------------------------+
| Next Output |
+----------------------------------+
| Top 10 Queries Using Custom View |
+----------------------------------+
+-------------------------------------------------------------------+--------+------------+---------------+-------------+---------------+----------------
---+-------------------+------------------------------------------------------------------+
| query | db | exec_count | total_latency | avg_latency | rows_sent_avg | rows_examined_a
vg | rows_affected_avg | digest |
+-------------------------------------------------------------------+--------+------------+---------------+-------------+---------------+----------------
---+-------------------+------------------------------------------------------------------+
| CALL `insert_t1` ( ) | testdb | 3 | 8.49 h | 2.83 h | 0 |
0 | 0 | 6a32a3e132e7b15efde27d53fad0a7bb999b96b9af7d54eaae3efacc399dbc48 |
| SELECT ? WHERE ? = `sleep` (?) | NULL | 4 | 5.18 m | 1.3 m | 1 |
1 | 0 | a3057e577ccd89963ebb001ddae81b2a8d81e6e3e0b30368571cfeb596d1ade1 |
| CALL `sys` . `ps_trace_stateme ... est` ( @?, ... , TRUE , TRUE ) | NULL | 2 | 1.98 m | 59.31 s | 0 |
0 | 0 | c5fd10a8bb321e74f4992da5bc9b1033e4aa4ff888ef7d1de9aaf480ab4969e0 |
| CALL `sys` . `diagnostics` (...) | NULL | 1 | 31.13 s | 31.13 s | 0 |
0 | 0 | e88a55326684dfa58465b63031e0874fc70c382c5fd55b9c8a708479a6e948ac |