Troubleshooting | Performance issues caused by show processlist

Author: Wang Xiang

A member of the DBA team of Akson, mainly responsible for MySQL fault handling and performance optimization. Persistence in technology, responsible for customers.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

--

Background Information

Business monitoring found that the average response time of transactions was nearly twice as slow as before. It is necessary to check whether the database is responding slowly. The production MySQL version is 8.0.18, with one master and 3 slaves semi-synchronous replication.

Failure analysis

First, we compared and checked the monitoring indicators (cpu, qps, tps, disk IO, etc.) during the normal trading period and the abnormal period, and found no obvious changes. Next, look at the slow log and find more slow SQL, and it is a common insert statement, which takes more than 1 second to execute. Further observation and comparison found that each insert slow occurs in the same second, the number of slow insert statements is basically about 30, and the interval between them is two minutes or a multiple of two minutes. According to this rule, the first feeling is whether it is a problem caused by timed tasks. After checking the scheduled tasks, the monitoring script is finally located, and the monitoring script is executed every two minutes. Next, we need to investigate, which part is causing the insert to be slow. In order to quickly reproduce the problem, use mysqlslap directly on a slave library for pressure testing. It is known from the business that the problem insert statement will be written 60-80 times per second. The stress test statement is as follows:

 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);

Execute the monitoring script during the stress test and check the slow log, which can stably reproduce the production phenomenon. Through the exclusion method, several statements using the information_schema.processlist table were finally located, which caused the insert to be slow. Why does information_schema.processlist cause the insert to be slow? Take this question to see the official description of 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.

According to the official instructions: Using the default show processlist will hold a global mutex, which will cause performance problems on busy systems. At the same time, a solution is also given, using the processlist in the Performance Schema instead, this method will not generate a global mutex.

performance_schema_show_processlist is a new feature introduced in MySQL version 8.0.22. Next, let's take a look at the official description of the processlist in the Performance Schema.

 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.

If the parameter performance_schema_show_processlist is turned on, show processlist uses processlist in Performance Schema to avoid the problem of global mutex. If this parameter is not turned on, show processlist uses information_schema.processlist to generate global locks.

Add the performance_schema_show_processlist=on configuration under the configuration file [mysqld]. After the configuration is complete, check the processlist under performance_schema.

 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)

Summarize

1. Using MySQL versions earlier than 8.0.22, it is necessary to execute show processlist with caution on sensitive systems with busy business operations.

2. After MySQL 8.0.22, you can enable performance_schema_show_processlist to avoid this problem. However, it is still not recommended to frequently query session information.

In addition, querying the processlist table causes the MySQL instance to crash. Please refer to the article: https://mp.weixin.qq.com/s/qRc6mGk4_jvc2rHBIKojiQ

refer to:

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-processlist-table.html

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html#sysvar_performance_schema_show_processlist