WL#9090: Reimplement SHOW PROCESSLIST as a view on PERFORMANCE_SCHEMA.THREADS
SHOW PROCESSLIST builds a virtual table of process information by collecting thread data from all active threads. The current implementation iterates across active threads from within the thread manager while holding a global mutex, which can be prohibitively intrusive on busy systems.
This worklog task defines an implementation of SHOW PROCESSLIST where the active thread data is queried from the Performance Schema rather than the thread manager. This implementation is offered as an alternative to the legacy method.
CURRENT-VERSION = 8.0.22 PREVIOUS-VERSION = 8.0+
FR-1 A fresh MySQL installation of CURRENT-VERSION must create the following table:
FR-2 An upgrade from PREVIOUS-VERSON to CURRENT-VERSION must create the following table:
FR-3 Security privileges for
performance_schema.processlist are enforced. Legal operation is SELECT.
performance_schema.processlist is visible in the information_schema.
performance_schema.processlist is visible in SHOW TABLES.
Server Start Options and Variables
FR-5 A new server start option is available,
performance-schema-show-processlist. Valid values are ON/OFF. The default value is OFF.
When ON, the new SHOW PROCESSLIST is enabled and will reference the contents of
When OFF, the SHOW PROCESSLIST command will revert to the legacy implementation.
performance_schema.processlist table is unaffected by either setting.
NFR-6 The new SHOW PROCESSLIST requires a minimum server configuration to be functional. The documentation will detail the configuration required.
SHOW PROCESSLIST can be invoked in the following ways:
A command issued from a MySQL client program:
mysql> SHOW [FULL] PROCESSLIST; mysqladmin --verbose processlist
A query on the Informaton Schema processlist table, for example:
SELECT * FROM information_schema.processlist
This is allows for more complex queries, including JOINs, etc.
From the MySQL client/server protocol, for example a connector that issues the COM_PROCESS_INFO command.
The new SHOW PROCESSLIST executes an internal query of a new table,
peformance_schema.processlist, rather than iterate across threads in the thread manager. The
processlist table consists of thread data collected by the Performance Schema instrumentation. Queries on
performance_schema.processlist use non-locking methods to access the thread data, obviating the need for a global mutex lock.
When enabled, the new SHOW PROCESSLIST implementation will replace the SHOW PROCESSLIST command, but will not affect other methods of viewing process information. Methods 2 and 3 above will continue to use the legacy implementation.
Minimum Required Configuration
The new SHOW PROCESSLIST requires a minimum server configuration, otherwise the data returned might be incomplete:
The Performance Schema must be enabled:
performance-schema = ON
The MySQL server must be built with thread instrumentation. Thread instrumentation is enabled by default, and can be enabled or disabled using the CMAKE option:
The new system variable,
performance-schema-show-processlist, can be used to dynamically enable or disable the new SHOW PROCESSLIST. If the minimum configuration above is not met, then
performance-schema-show-processlist may not return all data.
The following configuration parameters should be set to ensure that output from the new SHOW PROCESSLIST is complete (-1 = autosize):
Thread instance memory, otherwise some threads may be ignored:
performance-schema-max-thread-instances= -1 or >=
Thread instrument class memory:
Stage class memory for the STATE column:
The legacy SHOW PROCESSLIST will be enabled by default.
The new SHOW PROCESSLIST must be explicitly enabled, which presumes the DBA understands the required configuration and memory implications.
The new SHOW PROCESSLIST supports the existing command syntax such that the INFO column is truncated to 100 characters unless the FULL keyword is used.
CREATE TABLE performance_schema.processlist 'ID' BIGINT UNSIGNED, 'USER' VARCHAR(32), 'HOST' VARCHAR(255) CHARACTER SET ASCII default null, 'DB' VARCHAR(64), 'COMMAND' VARCHAR(16), 'TIME' BIGINT, 'STATE' VARCHAR(64), 'INFO' LONGTEXT, PRIMARY KEY (ID) USING HASH ENGINE = PERFORMANCE_SCHEMA
SELECT is supported. TRUNCATE is not supported. The PROCESS privilege is required to see all threads, otherwise only threads associated with the current account are visible.
This table contains the currently active user threads.
ID is the connection identifier, same as given by
USER is the MySQL user who issued the statement
HOST is the name and port of the client issuing the statement, except for system_user, for which there is no host
DB is the default database if one is selected, otherwise NULL
COMMAND is the type of command the thread is executing. The value corresponds to the
COM_xxxcommands of the client/server protocol and
TIME is the time in seconds that the thread has been in its current state
STATE is the action, event or state that indicates what the thread is doing
INFO is the statement the thread is executing, or NULL if not executing any statements
New System Variable
Name: performance-schema-show-processlist Values: "ON", "OFF" Default: "OFF" Scope: Global Dynamic: Yes