MySQL運行時的可觀測性
作者：YeJinrong/葉金榮
這條SQL的運行進度展示，以及各個階段的耗時，和PROFILING的輸出一樣，當我們了解一條SQL運行所需要經歷的各個階段時，從上面的輸出結果中也就能估算出該SQL大概還要多久能跑完，決定是否要提前kill它。
1. 說在前面的話
在MySQL里，一條SQL運行時產生多少磁盤I/O，占用多少內存，是否有創建臨時表，這些指標如果都能觀測到，有助于更快發現SQL瓶頸，撲滅潛在隱患。
從MySQL 5.7版本開始，performance_schema就默認啟用了，并且還增加了sys schema，到了8.0版本又進一步得到增強提升，在SQL運行時就能觀察到很多有用的信息，實現一定程度的可觀測性。
下面舉例說明如何進行觀測，以及主要觀測哪些指標。
2. 安裝employees測試庫
安裝MySQL官方提供的employees測試數據庫，戳此鏈接(https://dev.mysql.com/doc/index-other.html)下載，解壓縮后開始安裝：
$ mysql -f < employees.sql;
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:37MySQL還提供了相應的使用文檔：https://dev.mysql.com/doc/employee/en/
本次測試采用GreatSQL 8.0.32-24版本，且運行在MGR環境中：
greatsql> \s
...
Server version: 8.0.32-24 GreatSQL, Release 24, Revision 3714067bc8c
...
greatsql> select MEMBER_ID, MEMBER_ROLE, MEMBER_VERSION from performance_schema.replication_group_members;
+--------------------------------------+-------------+----------------+
| MEMBER_ID | MEMBER_ROLE | MEMBER_VERSION |
+--------------------------------------+-------------+----------------+
| 2adec6d2-febb-11ed-baca-d08e7908bcb1 | SECONDARY | 8.0.32 |
| 2f68fee2-febb-11ed-b51e-d08e7908bcb1 | ARBITRATOR | 8.0.32 |
| 5e34a5e2-feb6-11ed-b288-d08e7908bcb1 | PRIMARY | 8.0.32 |
+--------------------------------------+-------------+----------------+3. 觀測SQL運行狀態
查看當前連接/會話的連接ID、內部線程ID：
greatsql> select processlist_id, thread_id from performance_schema.threads where processlist_id = connection_id();
+----------------+-----------+
| processlist_id | thread_id |
+----------------+-----------+
| 110 | 207 |
+----------------+-----------+查詢得到當前的連接ID=110，內部線程ID=207。
P.S，由于本文整理過程不是連續的，所以下面看到的 thread_id 值可能會有好幾個，每次都不同。
3.1 觀測SQL運行時的內存消耗
執行下面的SQL，查詢所有員工的薪資總額，按員工號分組，并按薪資總額倒序，取前10條記錄：
greatsql> explain select emp_no, sum(salary) as total_salary from salaries group by emp_no order by total_salary desc limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: salaries
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 7
ref: NULL
rows: 2838426
filtered: 100.00
Extra: Using temporary; Using filesort看到需要全索引掃描（其實也等同于全表掃描，因為是基于PRIMARY索引），并且還需要生成臨時表，以及額外的filesort。
在正式運行該SQL之前，在另外的窗口中新建一個連接會話，執行下面的SQL先觀察該連接/會話當前的內存分配情況：
greatsql> select * from sys.x$memory_by_thread_by_current_bytes where thread_id = 207\G
*************************** 1. row ***************************
thread_id: 207
user: root@localhost
current_count_used: 9
current_allocated: 26266
current_avg_alloc: 2918.4444
current_max_alloc: 16464
total_allocated: 30311等到該SQL執行完了，再一次查詢內存分配情況：
greatsql> select * from sys.x$memory_by_thread_by_current_bytes where thread_id = 207\G
*************************** 1. row ***************************
thread_id: 207
user: root@localhost
current_count_used: 13
current_allocated: 24430
current_avg_alloc: 1879.2308
current_max_alloc: 16456
total_allocated: 95719我們注意到幾個數據的變化情況，用下面表格來展示：
指標 | 運行前 | 運行后 |
total_allocated | 30311 | 95719 |
也就是說，SQL運行時，需要分配的內存是：95719 - 30311 = 65408 字節。
3.2 觀測SQL運行時的其他開銷
通過觀察 performance_schema.status_by_thread 表，可以知道相應連接/會話中SQL運行的一些狀態指標。在SQL運行結束后，執行下面的SQL命令即可查看：
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.585593 Lock_time: 0.000002 Rows_sent: 10 Rows_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: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# InnoDB_trx_id: 0
# Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
# InnoDB_pages_distinct: 4281
use 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：
greatsql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history WHERE thread_id = 85 order by event_id desc limit 5;
+----------+----------+-------------------------------------------------------------------------------------------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT |
+----------+----------+-------------------------------------------------------------------------------------------------------------------------------+
| 149845 | 0.6420 | select emp_no, sum(salary) as total_salary, sleep(0.000001) from salaries group by emp_no order by total_salary desc limit 10 |
| 149803 | 0.6316 | select emp_no, sum(salary) as total_salary, sleep(0.000001) from salaries group by emp_no order by total_salary desc limit 10 |
| 149782 | 0.6245 | select emp_no, sum(salary) as total_salary, sleep(0.000001) from salaries group by emp_no order by total_salary desc limit 10 |
| 149761 | 0.6361 | select emp_no, sum(salary) as total_salary, sleep(0.000001) from salaries group by emp_no order by total_salary desc limit 10 |
| 149740 | 0.6245 | select emp_no, sum(salary) as total_salary, sleep(0.000001) from salaries group by emp_no order by total_salary desc limit 10 |
+----------+----------+-------------------------------------------------------------------------------------------------------------------------------+
# 再根據 EVENT_ID 值去查詢 events_stages_history_long
greatsql> SELECT thread_id ,event_Id, event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID = 149845 order by event_id;
+-----------+----------+------------------------------------------------+----------+
| thread_id | event_Id | Stage | Duration |
+-----------+----------+------------------------------------------------+----------+
| 85 | 149846 | stage/sql/starting | 0.0000 |
| 85 | 149847 | stage/sql/Executing hook on transaction begin. | 0.0000 |
| 85 | 149848 | stage/sql/starting | 0.0000 |
| 85 | 149849 | stage/sql/checking permissions | 0.0000 |
| 85 | 149850 | stage/sql/Opening tables | 0.0000 |
| 85 | 149851 | stage/sql/init | 0.0000 |
| 85 | 149852 | stage/sql/System lock | 0.0000 |
| 85 | 149854 | stage/sql/optimizing | 0.0000 |
| 85 | 149855 | stage/sql/statistics | 0.0000 |
| 85 | 149856 | stage/sql/preparing | 0.0000 |
| 85 | 149857 | stage/sql/Creating tmp table | 0.0000 |
| 85 | 149858 | stage/sql/executing | 0.6257 |
| 85 | 149859 | stage/sql/end | 0.0000 |
| 85 | 149860 | stage/sql/query end | 0.0000 |
| 85 | 149861 | stage/sql/waiting for handler commit | 0.0000 |
| 85 | 149862 | stage/sql/closing tables | 0.0000 |
| 85 | 149863 | stage/sql/freeing items | 0.0000 |
| 85 | 149864 | stage/sql/logging slow query | 0.0000 |
| 85 | 149865 | stage/sql/cleaning up | 0.0000 |
+-----------+----------+------------------------------------------------+----------+上面就是這條SQL的運行進度展示，以及各個階段的耗時，和PROFILING的輸出一樣，當我們了解一條SQL運行所需要經歷的各個階段時，從上面的輸出結果中也就能估算出該SQL大概還要多久能跑完，決定是否要提前kill它。
如果想要觀察DDL SQL的運行進度，可以參考這篇文章：不用MariaDB/Percona也能查看DDL的進度。
更多的觀測指標、維度還有待繼續挖掘，以后有機會再寫。
另外，也可以利用MySQL Workbench工具，或MySQL Enterprise Monitor，都已集成了很多可觀測性指標，相當不錯的體驗。
責任編輯：武曉燕
來源：
GreatSQL社區
