MySQL 8.0.22: SHOW PROCESSLIST Version 2 – Now Available From PERFORMANCE_SCHEMA

January 15, 2021

Author

Sri Sakthivel

Insight for DBAs

Insight for Developers

MySQL

Share this Post:

SHOW PROCESSLIST Version 2 The “SHOW PROCESSLIST” command is very famous and very useful for MySQL DBAs. It will help you to understand the ongoing thread activities and their current states. By default, the “show processlist” output details will be collected from the thread manager, and it needs the global mutex. From MySQL 8.0.22, we have an alternative way to get the process details from the PERFORMANCE_SCHEMA. It doesn’t need the global mutex. 🙂

Note: We also have the non-blocking SYS schema views “processlist” and “x$processlist”, which provide more complete information than the SHOW PROCESSLIST statement and the INFORMATION_SCHEMA.PROCESSLIST and PERFORMANCE_SCHEMA.PROCESSLIST. But, we can’t integrate this with the “SHOW PROCESSLIST” command.

In this blog, I am going to explain the complete details about the new processlist implementation using PERFORMANCE_SCHEMA.

“SHOW PROCESSLIST” Using Thread Manager (default)

This is the default method.

The default “show processlist” implementation iterates across active threads from within the thread manager while holding a global mutex.

Negatively impacts performance.

Particularly impacts the busy systems quite badly.

The INFORMATION_SCHEMA.PROCESSLIST is one of the sources of process information. This will also use the thread manager to collect the metrics.

By default, “mysqladmin processlist” also uses the thread manager to get the details.

The following statements are equivalent:

SHOW FULL PROCESSLIST;<br>SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;<br>Mysqladmin processlist --verbose

1	SHOW FULL PROCESSLIST;<br>SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;<br>Mysqladmin processlist --verbose

“SHOW PROCESSLIST” Using Performance Schema

Available from MySQL 8.0.22.

It collects the thread details from the PERFORMANCE_SCHEMA>PROCESSLIST table.

Global mutex is not needed.

Helps to avoid the performance impact during querying the “show processlist”, particularly in busy systems.

The implementation also applies to “mysqladmin processlist”

The following statements are equivalent:

SHOW FULL PROCESSLIST;<br>SELECT * FROM PERFORMANCE_SCHEMA.PROCESSLIST;<br>Mysqladmin processlist --verbose

1	SHOW FULL PROCESSLIST;<br>SELECT * FROM PERFORMANCE_SCHEMA.PROCESSLIST;<br>Mysqladmin processlist --verbose

“PERFORMANCE_SCHEMA.PROCESSLIST” table has similar columns as “INFORMATION_SCHEMA.PROCESSLIST”

mysql> desc performance_schema.processlist;<br>+---------+-----------------+------+-----+---------+-------+<br>| Field   | Type            | Null | Key | Default | Extra |<br>+---------+-----------------+------+-----+---------+-------+<br>| ID      | bigint unsigned | NO   | PRI | NULL    |       |<br>| USER    | varchar(32)     | YES  |     | NULL    |       |<br>| HOST    | varchar(255)    | YES  |     | NULL    |       |<br>| DB      | varchar(64)     | YES  |     | NULL    |       |<br>| COMMAND | varchar(16)     | YES  |     | NULL    |       |<br>| TIME    | bigint          | YES  |     | NULL    |       |<br>| STATE   | varchar(64)     | YES  |     | NULL    |       |<br>| INFO    | longtext        | YES  |     | NULL    |       |<br>+---------+-----------------+------+-----+---------+-------+<br>8 rows in set (0.00 sec)<br><br>mysql> desc information_schema.processlist;<br>+---------+-----------------+------+-----+---------+-------+<br>| Field   | Type            | Null | Key | Default | Extra |<br>+---------+-----------------+------+-----+---------+-------+<br>| ID      | bigint unsigned | NO   |     |         |       |<br>| USER    | varchar(32)     | NO   |     |         |       |<br>| HOST    | varchar(261)    | NO   |     |         |       |<br>| DB      | varchar(64)     | YES  |     |         |       |<br>| COMMAND | varchar(16)     | NO   |     |         |       |<br>| TIME    | int             | NO   |     |         |       |<br>| STATE   | varchar(64)     | YES  |     |         |       |<br>| INFO    | varchar(65535)  | YES  |     |         |       |<br>+---------+-----------------+------+-----+---------+-------+<br>8 rows in set (0.00 sec)

Implementation

Make sure the PERFORMANCE_SCHEMA is enabled at the server startup.

Make sure MySQL was configured and built with the thread instrumentations enabled.

MySQL provides a variable “performance_schema_show_processlist” to enable this feature. Once we enable the variable, the “SHOW PROCESSLIST” command will start to show the details from the “PERFORMANCE_SCHEMA.PROCESSLIST” table instead of the thread manager.

The variable has a global scope, no need to restart the MySQL server.

mysql> show global variables like 'performance_schema_show_processlist';<br>+-------------------------------------+-------+<br>| Variable_name                       | Value |<br>+-------------------------------------+-------+<br>| performance_schema_show_processlist | OFF   |<br>+-------------------------------------+-------+<br>1 row in set (0.08 sec)<br><br>mysql> set global performance_schema_show_processlist='ON';<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> r<br>Connection id:    23<br>Current database: *** NONE ***<br><br>mysql> show global variables like 'performance_schema_show_processlist';<br>+-------------------------------------+-------+<br>| Variable_name                       | Value |<br>+-------------------------------------+-------+<br>| performance_schema_show_processlist | ON    |<br>+-------------------------------------+-------+<br>1 row in set (0.00 sec)

mysql> show global variables like 'performance_schema_show_processlist'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | performance_schema_show_processlist | OFF | +-------------------------------------+-------+ 1 row in set (0.08 sec) mysql> set global performance_schema_show_processlist='ON'; Query OK, 0 rows affected (0.00 sec) mysql> r Connection id: 23 Current database: *** NONE *** mysql> show global variables like 'performance_schema_show_processlist'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | performance_schema_show_processlist | ON | +-------------------------------------+-------+ 1 row in set (0.00 sec)

Yes, the PERFORMANCE_SCHEMA.PROCESSLIST table is activated for the “SHOW PROCESSLIST”.

“SHOW PROCESSLIST” output from “PERFORMANCE_SCHEMA”:

mysql> show processlistG<br>*************************** 1. row ***************************<br>     Id: 5<br>   User: event_scheduler<br>   Host: localhost<br>     db: NULL<br>Command: Daemon<br>   Time: 2461<br>  State: Waiting on empty queue<br>   Info: NULL<br>*************************** 2. row ***************************<br>     Id: 23<br>   User: root<br>   Host: localhost<br>     db: NULL<br>Command: Query<br>   Time: 0<br>  State: executing<br>   Info: show processlist<br>2 rows in set (0.00 sec)

mysql> show processlistG *************************** 1. row *************************** Id: 5 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 2461 State: Waiting on empty queue Info: NULL *************************** 2. row *************************** Id: 23 User: root Host: localhost db: NULL Command: Query Time: 0 State: executing Info: show processlist 2 rows in set (0.00 sec)

You can also query the “performance_schema.processlist” table to get the thread information.

mysql> select * from performance_schema.processlistG<br>*************************** 1. row ***************************<br>     ID: 5<br>   USER: event_scheduler<br>   HOST: localhost<br>     DB: NULL<br>COMMAND: Daemon<br>   TIME: 2448<br>  STATE: Waiting on empty queue<br>   INFO: NULL<br>*************************** 2. row ***************************<br>     ID: 23<br>   USER: root<br>   HOST: localhost<br>     DB: NULL<br>COMMAND: Query<br>   TIME: 0<br>  STATE: executing<br>   INFO: select * from performance_schema.processlist<br>2 rows in set (0.00 sec)

mysql> select * from performance_schema.processlistG *************************** 1. row *************************** ID: 5 USER: event_scheduler HOST: localhost DB: NULL COMMAND: Daemon TIME: 2448 STATE: Waiting on empty queue INFO: NULL *************************** 2. row *************************** ID: 23 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)

“mysqladmin processlist” output from “performance_schema”:

[root@mysql8 vagrant]# mysqladmin processlist<br>+----+-----------------+-----------+----+---------+------+------------------------+------------------+<br>| Id | User            | Host      | db | Command | Time | State                  | Info             |<br>+----+-----------------+-----------+----+---------+------+------------------------+------------------+<br>| 5  | event_scheduler | localhost |    | Daemon  | 2631 | Waiting on empty queue |                  |<br>| 24 | root            | localhost |    | Query   | 0    | executing              | show processlist |<br>+----+-----------------+-----------+----+---------+------+------------------------+------------------+

Recommendations

To avoid having some threads ignored, leave the “performance_schema_max_thread_instances” and “performance_schema_max_thread_classes” system variables set to their default value (default = -1, meaning the parameter will be autosized during the server startup).

To avoid having some STATE column values be empty, leave the “performance_schema_max_stage_classes” system variable set to its default (default = -1, meaning the parameter will be autosized during the server startup).