MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
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:
The
SHOW PROCESSLISTstatement:Section 13.7.7.29, “SHOW PROCESSLIST Statement”Themysqladmin processlistcommand:Section 4.5.2, “mysqladmin — A MySQL Server Administration Program”
The
INFORMATION_SCHEMAPROCESSLISTtable:Section 26.3.23, “The INFORMATION_SCHEMA PROCESSLIST Table”The Performance Schema
processlisttable:Section 27.12.21.5, “The processlist Table”The Performance Schema
threadstable columns with names having a prefix ofPROCESSLIST_:Section 27.12.21.6, “The threads Table”The
sysschemaprocesslistandsessionviews:Section 28.4.3.22, “The processlist and x$processlist Views”, andSection 28.4.3.33, “The session and x$session Views”
Thethreadstable compares toSHOW PROCESSLIST,INFORMATION_SCHEMAPROCESSLIST, andmysqladmin processlistas follows:
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.NoteAs of MySQL 8.0.22, an alternative implementation for
SHOW PROCESSLISTis available based on the Performance Schemaprocesslisttable, which, like thethreadstable, does not require a mutex and has better performance characteristics. For details, seeSection 27.12.21.5, “The processlist Table”.The
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 thethreadstable can be used to monitor thread activity the other sources cannot.You can enable or disable Performance Schema thread monitoring, as described inSection 27.12.21.6, “The threads Table”.
For these reasons, DBAs who perform server monitoring using one of the other thread information sources may wish to monitor using thethreadstable instead.
Thesysschemaprocesslistview presents information from the Performance Schemathreadstable in a more accessible format. Thesysschemasessionview presents information about user sessions like thesysschemaprocesslistview, but with background processes filtered out.
For most sources of process information, if you have thePROCESSprivilege, you can see all threads, even those belonging to other users. Otherwise (without thePROCESSprivilege), 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 Schemathreadstable also provides thread information, but table access uses a different privilege model. SeeSection 27.12.21.6, “The threads Table”.
Each process list entry contains several pieces of information. The following list describes them using the labels fromSHOW PROCESSLIST输出。其他处理信息来源使用思米lar labels.
Idis the connection identifier for the client associated with the thread.UserandHostindicate the account associated with the thread.dbis the default database for the thread, orNULL如果没有被选中。CommandandStateindicate 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
Commandvalues, andStatevalues grouped by category. The meaning for some of these values is self-evident. For others, additional description is provided.NoteApplications 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 withSET 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. SeeSection 17.2.3, “Replication Threads”.valueInfoindicates the statement the thread is executing, orNULLif 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 diffferent process information source).