Movatterモバイル変換

MySQL 9.3 Reference Manual

10.14.1 Accessing the Process List

The following discussion enumerates the sources of process information, the privileges required to see process information, and describes the content of process list entries.

Sources of Process Information

Process information is available from these sources:

TheSHOW PROCESSLIST statement:Section 15.7.7.32, “SHOW PROCESSLIST Statement”
Themysqladmin processlist command:Section 6.5.2, “mysqladmin — A MySQL Server Administration Program”
TheINFORMATION_SCHEMAPROCESSLIST table:Section 28.3.24, “The INFORMATION_SCHEMA PROCESSLIST Table”
The Performance Schemaprocesslist table:Section 29.12.22.9, “The processlist Table”
The Performance Schemathreads table columns with names having a prefix ofPROCESSLIST_:Section 29.12.22.10, “The threads Table”
Thesys schemaprocesslist andsession views:Section 30.4.3.22, “The processlist and x$processlist Views”, andSection 30.4.3.33, “The session and x$session Views”

Thethreads table compares toSHOW PROCESSLIST,INFORMATION_SCHEMAPROCESSLIST, andmysqladmin processlist as follows:

Access to thethreads table does not require a mutex and has minimal impact on server performance. The other sources have negative performance consequences because they require a mutex.
Note
An alternative implementation forSHOW PROCESSLIST is available based on the Performance Schemaprocesslist table, which, like thethreads table, does not require a mutex and has better performance characteristics. For details, seeSection 29.12.22.9, “The processlist Table”.
Thethreads table displays background threads, which the other sources do not. It also provides additional information for each thread that the other sources do not, such as whether the thread is a foreground or background thread, and the location within the server associated with the thread. This means that thethreads table can be used to monitor thread activity the other sources cannot.
You can enable or disable Performance Schema thread monitoring, as described inSection 29.12.22.10, “The threads Table”.

For these reasons, DBAs who perform server monitoring using one of the other thread information sources may wish to monitor using thethreads table instead.

Thesys schemaprocesslist view presents information from the Performance Schemathreads table in a more accessible format. Thesys schemasession view presents information about user sessions like thesys schemaprocesslist view, but with background processes filtered out.

Privileges Required to Access the Process List

For most sources of process information, 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.

The Performance Schemathreads table also provides thread information, but table access uses a different privilege model. SeeSection 29.12.22.10, “The threads Table”.

Content of Process List Entries

Each process list entry contains several pieces of information. The following list describes them using the labels fromSHOW PROCESSLIST output. Other process information sources use similar labels.

Id is the connection identifier for the client associated with the thread.
User andHost indicate the account associated with the thread.
db is the default database for the thread, orNULL if none has been selected.
Command andState indicate what the thread is doing.
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.
The following sections list the possibleCommand values, andState values grouped by category. The meaning for some of these values is self-evident. For others, additional description is provided.
Note
Applications that examine process list information should be aware that the commands and states are subject to change.
Time indicates how long the thread has been in its current state. The thread's notion of the current time may be altered in some cases: The thread can change the time withSET TIMESTAMP =value. 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”.
Info indicates the statement the thread is executing, orNULL if it is executing no statement. ForSHOW PROCESSLIST, this value contains only the first 100 characters of the statement. To see complete statements, useSHOW FULL PROCESSLIST (or query a different process information source).

PREV HOME UP NEXT