Summary

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+

Install

FR-1 A fresh MySQL installation of CURRENT-VERSION must create the following table:

performance_schema.processlist

Upgrade

FR-2 An upgrade from PREVIOUS-VERSON to CURRENT-VERSION must create the following table:

performance_schema.processlist

Privileges

FR-3 Security privileges for performance_schema.processlist are enforced. Legal operation is SELECT.

Observability

FR-4.1 Table performance_schema.processlist is visible in the information_schema.

FR-4.2 Table 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 performance_schema.processlist.

When OFF, the SHOW PROCESSLIST command will revert to the legacy implementation.

The 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.

Feature Overview

SHOW PROCESSLIST can be invoked in the following ways:

1. A command issued from a MySQL client program:

   mysql> SHOW [FULL] PROCESSLIST;
   mysqladmin --verbose processlist
   

2. 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.

3. 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.

Feature Configuration

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:

• -DDISABLE_PSI_THREAD=OFF/ON

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.

Recommended Configuration

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 >= max-connections

• Thread instrument class memory:

  performance-schema-max-thread-classes = -1

• Stage class memory for the STATE column:

  performance-schema-max-stage-classes = -1

Feature Operation

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.

New table

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

Operations

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.

Semantic

This table contains the currently active user threads.

• ID is the connection identifier, same as given by connection_id()

• 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_xxx commands of the client/server protocol and COM_xxx status variables

• 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