MySQL8.0使用sys.statement_performance_analyzer()排查性能问题
#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());
