江苏龙网

  1. 主页 > 生活百科 > >

MySQL运行时的可观测性( 二 )

mysql


greatsql> select * from performance_schema.status_by_thread where thread_id = 207;...|207 | Created_tmp_disk_tables| 0||207 | Created_tmp_tables| 0|...|207 | Handler_read_first| 1||207 | Handler_read_key| 1||207 | Handler_read_last| 0||207 | Handler_read_next| 2844047||207 | Handler_read_prev| 0||207 | Handler_read_rnd| 0||207 | Handler_read_rnd_next| 0||207 | Handler_rollback| 0||207 | Handler_savepoint| 0||207 | Handler_savepoint_rollback| 0||207 | Handler_update| 0||207 | Handler_write| 0||207 | Last_query_cost| 286802.914893||207 | Last_query_partial_plans| 1|...|207 | Select_full_join| 0||207 | Select_full_range_join| 0||207 | Select_range| 0||207 | Select_range_check| 0||207 | Select_scan| 1||207 | Slow_launch_threads| 0||207 | Slow_queries| 1||207 | Sort_merge_passes| 0||207 | Sort_range| 0||207 | Sort_rows| 1||207 | Sort_scan| 1|...上面我们只罗列了部分比较重要的状态指标 。从这个结果也可以佐证slow query log中的结果 ， 确实没创建临时表 。
作为参照 ， 查看这条SQL对应的slow query log记录：
# Query_time: 0.585593Lock_time: 0.000002 Rows_sent: 10Rows_examined: 2844057 Thread_id: 110 Errno: 0 Killed: 0 Bytes_received: 115 Bytes_sent: 313 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 2844047 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 10 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2023-07-06T10:06:01.438376+08:00 End: 2023-07-06T10:06:02.023969+08:00 Schema: employees Rows_affected: 0# Tmp_tables: 0Tmp_disk_tables: 0Tmp_table_sizes: 0# InnoDB_trx_id: 0# Full_scan: YesFull_join: NoTmp_table: NoTmp_table_on_disk: No# Filesort: YesFilesort_on_disk: NoMerge_passes: 0#InnoDB_IO_r_ops: 0InnoDB_IO_r_bytes: 0InnoDB_IO_r_wait: 0.000000#InnoDB_rec_lock_wait: 0.000000InnoDB_queue_wait: 0.000000#InnoDB_pages_distinct: 4281use employees;SET timestamp=1688609161;select emp_no, sum(salary) as total_salary from salaries group by emp_no order by total_salary desc limit 10;可以看到 ， Created_tmp_disk_tables, Created_tmp_tables, Handler_read_next, Select_full_join, Select_scan, Sort_rows, Sort_scan, 等几个指标的数值是一样的 。
还可以查看该SQL运行时的I/O latency情况 ， SQL运行前后两次查询对比：
greatsql> select * from sys.io_by_thread_by_latency where thread_id = 207;+----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+| user| total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |+----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+| root@localhost |7 | 75.39 us| 5.84 us| 10.77 us| 22.12 us|207 |110 |+----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+...greatsql> select * from sys.io_by_thread_by_latency where thread_id = 207;+----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+| user| total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |+----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+| root@localhost |8 | 85.29 us| 5.84 us| 10.66 us| 22.12 us|207 |110 |+----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+可以看到这个SQL运行时的I/O latency是：85.29 - 75.39 = 9.9us 。
3.3 观测SQL运行进度我们知道 ， 运行完一条SQL后 ， 可以利用PROFLING功能查看它各个阶段的耗时 ， 但是在运行时如果也想查看各阶段耗时该怎么办呢？
从MySQL 5.7版本开始 ， 可以通过 performance_schema.events_stages_% 相关表查看SQL运行过程以及各阶段耗时 ， 需要先修改相关设置：
# 确认是否对所有主机&用户都启用greatsql> SELECT * FROM performance_schema.setup_actors;+------+------+------+---------+---------+| HOST | USER | ROLE | ENABLED | HISTORY |+------+------+------+---------+---------+| %| %| %| NO| NO|+------+------+------+---------+---------+# 修改成对所有主机&用户都启用greatsql> UPDATE performance_schema.setup_actors SET ENABLED = 'YES', HISTORY = 'YES' WHERE HOST = '%' AND USER = '%'; # 修改 setup_instruments & setup_consumers 设置greatsql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%'; greatsql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';这就实时可以观测SQL运行过程中的状态了 。
在SQL运行过程中 ， 从另外的窗口查看该SQL对应的 EVENT_ID：


推荐阅读


上一篇：Python 字符串深度总结

下一篇：Linux NFSD软件架构与代码解析