Avoiding Global Mutex Contention in MySQL SHOW PROCESSLIST with performance_schema_show_processlist

Background: Monitoring revealed that transaction response time doubled; investigation pointed to MySQL 8.0.18 with one master and three semi‑synchronous replicas.

Fault analysis: No CPU, QPS, TPS, or disk I/O changes were observed; the slow‑log showed many INSERT statements taking over 1 second, occurring in bursts every two minutes. The pattern suggested a scheduled task, which was traced to a monitoring script running every two minutes. Using mysqlslap on a replica reproduced the issue.

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

The investigation identified queries that read from information_schema.processlist as the root cause. According to MySQL documentation, the default SHOW PROCESSLIST implementation iterates over active threads while holding a global mutex, which can severely degrade performance on busy systems.

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 and does not require a mutex.

Solution: Enable the variable performance_schema_show_processlist=ON in the MySQL configuration (under the [mysqld] section). After enabling, SHOW PROCESSLIST uses the Performance Schema table, avoiding the global lock. Verify the setting with SHOW VARIABLES LIKE 'performance_schema_show_processlist'; and inspect the process list via SELECT * FROM performance_schema.processlist\G .

Summary: On MySQL versions prior to 8.0.22, executing SHOW PROCESSLIST on a busy system can cause significant contention. From MySQL 8.0.22 onward, turning on performance_schema_show_processlist mitigates this issue, though frequent session‑information queries should still be minimized.