Movatterモバイル変換

MySQL 5.7 Reference Manual

25.12.16.3 The processlist Table

Note

Theprocesslist table is automatically created in the Performance Schema for new installations of MySQL 5.7.39, or higher. It is also created automatically by an upgrade.

The MySQL process list indicates the operations currently being performed by the set of threads executing within the server. Theprocesslist table is one source of process information. For a comparison of this table with other sources, seeSources of Process Information.

Theprocesslist table can be queried directly. If you have thePROCESS privilege, you can see all threads, even those belonging to other users. Otherwise (without thePROCESS privilege), nonanonymous users have access to information about their own threads but not threads for other users, and anonymous users have no access to thread information.

Note

If theperformance_schema_show_processlist system variable is enabled, theprocesslist table also serves as the basis for an alternative implementation underlying theSHOW PROCESSLIST statement. For details, see later in this section.

Theprocesslist table contains a row for each server process:

mysql> SELECT * FROM performance_schema.processlist\G*************************** 1. row ***************************     ID: 5   USER: event_scheduler   HOST: localhost     DB: NULLCOMMAND: Daemon   TIME: 137  STATE: Waiting on empty queue   INFO: NULL*************************** 2. row ***************************     ID: 9   USER: me   HOST: localhost:58812     DB: NULLCOMMAND: Sleep   TIME: 95  STATE:   INFO: NULL*************************** 3. row ***************************     ID: 10   USER: me   HOST: localhost:58834     DB: testCOMMAND: Query   TIME: 0  STATE: executing   INFO: SELECT * FROM performance_schema.processlist...

Theprocesslist table has these columns:

ID
The connection identifier. This is the same value displayed in theId column of theSHOW PROCESSLIST statement, displayed in thePROCESSLIST_ID column of the Performance Schemathreads table, and returned by theCONNECTION_ID() function within the thread.
USER
The MySQL user who issued the statement. A value ofsystem user refers to a nonclient thread spawned by the server to handle tasks internally, for example, a delayed-row handler thread or an I/O or SQL thread used on replica hosts. Forsystem user, there is no host specified in theHost column.unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet occurred.event_scheduler refers to the thread that monitors scheduled events (seeSection 23.4, “Using the Event Scheduler”).
Note
AUSER value ofsystem user is distinct from theSYSTEM_USER privilege. The former designates internal threads. The latter distinguishes the system user and regular user account categories (seeAccount Categories).
HOST
The host name of the client issuing the statement (except forsystem user, for which there is no host). The host name for TCP/IP connections is reported inhost_name:client_port format to make it easier to determine which client is doing what.
DB
The default database for the thread, orNULL if none has been selected.
COMMAND
The type of command the thread is executing on behalf of the client, orSleep if the session is idle. For descriptions of thread commands, seeSection 8.14, “Examining Server Thread (Process) Information”. The value of this column corresponds to theCOM_xxx commands of the client/server protocol andCom_xxx status variables. SeeSection 5.1.9, “Server Status Variables”
TIME
The time in seconds that the thread has been in its current state. For a replica SQL thread, the value is the number of seconds between the timestamp of the last replicated event and the real time of the replica host. SeeSection 16.2.3, “Replication Threads”.
STATE
An action, event, or state that indicates what the thread is doing. For descriptions ofSTATE values, seeSection 8.14, “Examining Server Thread (Process) Information”.
Most states correspond to very quick operations. If a thread stays in a given state for many seconds, there might be a problem that needs to be investigated.
INFO
The statement the thread is executing, orNULL if it is executing no statement. The statement might be the one sent to the server, or an innermost statement if the statement executes other statements. For example, if aCALL statement executes a stored procedure that is executing aSELECT statement, theINFO value shows theSELECT statement.
EXECUTION_ENGINE
The query execution engine. The value is eitherPRIMARY orSECONDARY. For use with MySQL HeatWave Service and MySQL HeatWave, where thePRIMARY engine isInnoDB and theSECONDARY engine is MySQL HeatWave (RAPID). For MySQL Community Edition Server, MySQL Enterprise Edition Server (on-premise), and MySQL HeatWave Service without MySQL HeatWave, the value is alwaysPRIMARY. This column was added in MySQL 8.0.29.

TRUNCATE TABLE is not permitted for theprocesslist table.

As mentioned previously, if theperformance_schema_show_processlist system variable is enabled, theprocesslist table serves as the basis for an alternative implementation of other process information sources:

TheSHOW PROCESSLIST statement.
Themysqladmin processlist command (which usesSHOW PROCESSLIST statement).

The defaultSHOW 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 alternativeSHOW PROCESSLIST implementation is based on the Performance Schemaprocesslist table. This implementation queries active thread data from the Performance Schema rather than the thread manager and does not require a mutex.

MySQL configuration affectsprocesslist table contents as follows:

Minimum required configuration:
- The MySQL server must be configured and built with thread instrumentation enabled. This is true by default; it is controlled using theDISABLE_PSI_THREADCMake option.
- The Performance Schema must be enabled at server startup. This is true by default; it is controlled using theperformance_schema system variable.
With that configuration satisfied,performance_schema_show_processlist enables or disables the alternativeSHOW PROCESSLIST implementation. If the minimum configuration is not satisfied, theprocesslist table (and thusSHOW PROCESSLIST) may not return all data.
Recommended configuration:
- To avoid having some threads ignored:
  - Leave theperformance_schema_max_thread_instances system variable set to its default or set it at least as great as themax_connections system variable.
  - Leave theperformance_schema_max_thread_classes system variable set to its default.
- To avoid having someSTATE column values be empty, leave theperformance_schema_max_stage_classes system variable set to its default.
The default for those configuration parameters is-1, which causes the Performance Schema to autosize them at server startup. With the parameters set as indicated, theprocesslist table (and thusSHOW PROCESSLIST) produce complete process information.

The preceding configuration parameters affect the contents of theprocesslist table. For a given configuration, however, theprocesslist contents are unaffected by theperformance_schema_show_processlist setting.

The alternative process list implementation does not apply to theINFORMATION_SCHEMAPROCESSLIST table or theCOM_PROCESS_INFO command of the MySQL client/server protocol.

PREV HOME UP NEXT