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

SHOW PROCESSLIST Version 2The “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 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:

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

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.

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

“SHOW PROCESSLIST” output from “PERFORMANCE_SCHEMA”: