故障分析 | show processlist 引起的性能问题

爱可生开源社区

发布于 2022-09-26 10:16:02

4070

发布于 2022-09-26 10:16:02

文章被收录于专栏：爱可生开源社区

作者：王祥

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

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

背景信息

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

故障分析

首先对比查看了交易正常时段与出现异常的时段各项监控指标（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)