关于mysql:故障分析-show-processlist-引起的性能问题

作者：王祥

爱可生 DBA 团队成员，次要负责 MySQL 故障解决和性能优化。对技术执着，为客户负责。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

—

背景信息

业务监控发现交易的均匀响应工夫比之前慢了近一倍，须要排查一下数据库是不是响应慢了。生产 MySQL 版本为 8.0.18，一主 3 从半同步复制。

故障剖析

首先比照查看了交易失常时段与出现异常的时段各项监控指标（cpu、qps、tps、磁盘 IO 等）都未发现显著的变动。接下来查看 slow log 发现了较多的慢 SQL，而且是一般的 insert 语句，执行时长超过 1 秒。进一步察看比照发现，每次 insert 慢都是呈现在同一秒，insert 慢语句条数根本在 30 条左右，而且呈现的距离都是两分钟或两分钟的倍数。依据这个法则第一感觉是不是定时工作引起的问题。通过对定时工作的排查最终定位到监控脚本，监控脚本为两分钟执行一次。接下来须要排查一下，具体是哪局部导致 insert 慢。为了疾速复现问题，间接在一个从库上应用 mysqlslap 进行压测。从业务那得悉问题 insert 语句每秒会有 60-80 次的写入量，压测语句如下：

mysqlslap -h127.0.0.1 -uroot -p --concurrency=80 --iterations=10 --create-schema=userdb --query=/root/test.sql --engine=innodb --number-of-queries=50000

#test.sql
insert into userdb.ps (clo1, clo2, clo3, clo4, clo4, clo5, clo6) values (substring(MD5(RAND()),1,20), 'fffffdddddddddd', '0', '','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaddddddddd','2022-06-17 16:00:38.145', 34);

在压测期间执行监控脚本，一边查看 slow log，能稳固复现生产的景象。通过排除法，最终定位到几个应用 information_schema.processlist 表的语句导致了 insert 慢。那 information_schema.processlist 为什么会导致 insert 慢呢？带着这个问题去查看一下官网对 information_schema.processlist 的形容。

The default SHOW PROCESSLIST implementation iterates across active threads from within the thread manager while holding a global mutex. This has negative performance consequences, particularly on busy systems. The alternative SHOW PROCESSLIST implementation is based on the Performance Schema processlist table. This implementation queries active thread data from the Performance Schema rather than the thread manager and does not require a mutex.

依据官网的阐明：在应用默认的 show processlist 会持有全局互斥锁，在业务忙碌的零碎上会导致性能问题。同时也给出了解决办法，应用 Performance Schema 中的 processlist 代替，此形式不会产生全局互斥锁。

performance_schema_show_processlist 是 MySQL 8.0.22 版本引入的新性能。接下来咱们来看看官网对 Performance Schema 中的 processlist 形容。

The SHOW PROCESSLIST statement provides process information by collecting thread data from all active threads. The performance_schema_show_processlist variable determines which SHOW PROCESSLIST implementation to use:
The default implementation iterates across active threads from within the thread manager while holding a global mutex. This has negative performance consequences, particularly on busy systems.

The alternative SHOW PROCESSLIST implementation is based on the Performance Schema processlist table. This implementation queries active thread data from the Performance Schema rather than the thread manager and does not require a mutex.

如果开启参数 performance_schema_show_processlist，show processlist 应用 Performance Schema 中的 processlist 防止了全局互斥锁的问题，如果不开启该参数则 show processlist 应用 information_schema.processlist 会产生全局锁。

在配置文件 [mysqld] 下加上 performance_schema_show_processlist=on 配置。配置实现后，查看 performance_schema 下的 processlist。

root@localhost:mysql.sock [(none)]> show variables like 'performance_schema_show_processlist';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| performance_schema_show_processlist | ON    |
+-------------------------------------+-------+
#信息与 information_schema.processlist 下保持一致
root@localhost:mysql.sock [(none)]> select * from performance_schema.processlist\G
*************************** 1. row ***************************
     ID: 5
   USER: event_scheduler
   HOST: localhost
     DB: NULL
COMMAND: Daemon
   TIME: 354
  STATE: Waiting on empty queue
   INFO: NULL
*************************** 2. row ***************************
     ID: 8
   USER: root
   HOST: localhost
     DB: NULL
COMMAND: Query
   TIME: 0
  STATE: executing
   INFO: select * from performance_schema.processlist
2 rows in set (0.00 sec)

总结

1. 应用 MySQL 8.0.22 之前的版本，在业务忙碌的敏感零碎上执行 show processlist 须要审慎。

2. 应用 MySQL 8.0.22 之后版本，能够开启 performance_schema_show_processlist 防止该问题。但仍旧不倡议频繁查问会话信息。

另外查问 processlist 表导致 MySQL 实例 crash 问题，请参考文章：https://mp.weixin.qq.com/s/qR…

参考：

https://dev.mysql.com/doc/ref…

https://dev.mysql.com/doc/ref…