The following discussion enumerates the sources of process information, the privileges required to see process information, and describes the content of process list entries.
Process information is available from these sources:
PROCESSLISTtable: Section 26.3.23, “The INFORMATION_SCHEMA PROCESSLIST Table”
sessionviews: Section 22.214.171.124, “The processlist and x$processlist Views”, and Section 126.96.36.199, “The session and x$session Views”
Access to the
threadstable 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
As of MySQL 8.0.22, an alternative implementation for
SHOW PROCESSLISTis available based on the Performance Schema
processlisttable, which, like the
threadstable, does not require a mutex and has better performance characteristics. For details, see Section 188.8.131.52, “The processlist Table”.
threadstable 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 the
threadstable can be used to monitor thread activity the other sources cannot.
You can enable or disable Performance Schema thread monitoring, as described in Section 184.108.40.206, “The threads Table”.
For these reasons, DBAs who perform server monitoring using
one of the other thread information sources may wish to
monitor using the
processlist view presents
information from the Performance Schema
threads table in a more
accessible format. The
session view presents
information about user sessions like the
processlist view, but with
background processes filtered out.
For most sources of process information, if you have the
PROCESS privilege, you can see
all threads, even those belonging to other users. Otherwise
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.
Each process list entry contains several pieces of
information. The following list describes them using the
output. Other process information sources use similar labels.
Idis the connection identifier for the client associated with the thread.
Hostindicate the account associated with the thread.
dbis the default database for the thread, or
NULLif none has been selected.
Stateindicate 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 possible
Statevalues 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.
Timeindicates 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 with
SET TIMESTAMP =. 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. See Section 17.2.3, “Replication Threads”.
Infoindicates the statement the thread is executing, or
NULLif it is executing no statement. For
SHOW PROCESSLIST, this value contains only the first 100 characters of the statement. To see complete statements, use
SHOW FULL PROCESSLIST(or query a diffferent process information source).