MySQL运行时的可观测性 _MySQL

文章插图
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;INFOCREATING DATABASE STRUCTUREINFOstorage engine: InnoDBINFOLOADING departmentsINFOLOADING employeesINFOLOADING dept_empINFOLOADING dept_managerINFOLOADING titlesINFOLOADING salariesdata_load_time_diff00:00:37

MySQL还提供了相应的使用文档：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 10G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: salariespartitions: NULLtype: indexpossible_keys: PRIMARYkey: PRIMARYkey_len: 7ref: NULLrows: 2838426filtered: 100.00Extra: Using temporary; Using filesort

看到需要全索引扫描（其实也等同于全表扫描，因为是基于PRIMARY索引），并且还需要生成临时表，以及额外的filesort 。
在正式运行该SQL之前，在另外的窗口中新建一个连接会话，执行下面的SQL先观察该连接/会话当前的内存分配情况：

greatsql> select * from sys.x$memory_by_thread_by_current_bytes where thread_id = 207G*************************** 1. row ***************************thread_id: 207user: root@localhostcurrent_count_used: 9 current_allocated: 26266 current_avg_alloc: 2918.4444 current_max_alloc: 16464total_allocated: 30311

等到该SQL执行完了，再一次查询内存分配情况：

greatsql> select * from sys.x$memory_by_thread_by_current_bytes where thread_id = 207G*************************** 1. row ***************************thread_id: 207user: root@localhostcurrent_count_used: 13 current_allocated: 24430 current_avg_alloc: 1879.2308 current_max_alloc: 16456total_allocated: 95719

我们注意到几个数据的变化情况，用下面表格来展示：
指标
运行前
运行后
total_allocated
30311
95719
也就是说， SQL运行时，需要分配的内存是：95719 - 30311 = 65408 字节。
3.2 观测SQL运行时的其他开销通过观察 performance_schema.status_by_thread 表，可以知道相应连接/会话中SQL运行的一些状态指标。在SQL运行结束后，执行下面的SQL命令即可查看：