Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 297.3Kb
Man Pages (Zip) - 402.5Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

15.7.7.29 SHOW PROCESSLIST Statement

SHOW [FULL] PROCESSLIST
Important

The INFORMATION SCHEMA implementation ofSHOW PROCESSLIST is deprecated and subject to removal in a future MySQL release. It is recommended to use the Performance Schema implementation ofSHOW PROCESSLIST instead.

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

Note

As of MySQL 8.0.22, an alternative implementation forSHOW PROCESSLIST is available based on the Performance Schemaprocesslist table, which, unlike the defaultSHOW PROCESSLIST implementation, does not require a mutex and has better performance characteristics. For details, seeSection 29.12.21.7, “The processlist Table”.

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.

Without theFULL keyword,SHOW PROCESSLIST displays only the first 100 characters of each statement in theInfo field.

TheSHOW PROCESSLIST statement is very useful if you get the“too many connections” error message and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that have theCONNECTION_ADMIN privilege (or the deprecatedSUPER privilege), to ensure that administrators should always be able to connect and check the system (assuming that you are not giving this privilege to all your users).

Threads can be killed with theKILL statement. SeeSection 15.7.8.4, “KILL Statement”.

Example ofSHOW PROCESSLIST output:

mysql> SHOW FULL PROCESSLIST\G*************************** 1. row ***************************     Id: 1   User: system user   Host:     db: NULLCommand: Connect   Time: 1030455  State: Waiting for source to send event   Info: NULL*************************** 2. row ***************************     Id: 2   User: system user   Host:     db: NULLCommand: Connect   Time: 1004  State: Has read all relay log; waiting for the replica         I/O thread to update it   Info: NULL*************************** 3. row ***************************     Id: 3112   User: replikator   Host: artemis:2204     db: NULLCommand: Binlog Dump   Time: 2144  State: Has sent all binlog to replica; waiting for binlog to be updated   Info: NULL*************************** 4. row ***************************     Id: 3113   User: replikator   Host: iconnect2:45781     db: NULLCommand: Binlog Dump   Time: 2086  State: Has sent all binlog to replica; waiting for binlog to be updated   Info: NULL*************************** 5. row ***************************     Id: 3123   User: stefan   Host: localhost     db: apollonCommand: Query   Time: 0  State: NULL   Info: SHOW FULL PROCESSLIST

SHOW PROCESSLIST output has these columns:

  • Id

    The connection identifier. This is the same value displayed in theID column of theINFORMATION_SCHEMAPROCESSLIST table, 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 (receiver) or SQL (applier) 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 27.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 (seeSection 8.2.11, “Account 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 10.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 7.1.10, “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 19.2.3, “Replication Threads”.

  • State

    An action, event, or state that indicates what the thread is doing. For descriptions ofState values, seeSection 10.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.