MySQL 8.0 Reference Manual（读书笔记61节--Examining Server Thread (Process) Information(1)）

To ascertain【ˌæsərˈteɪn 查明;弄清;】 what your MySQL server is doing, it can be helpful to examine【ɪɡˈzæmɪn 检查;审查;(仔细地)检验;调查;考察;考查;测验(某人);（尤指在法庭上）审问，查问;】 the process list, which indicates the operations currently being performed by the set of threads executing within the server. For example:

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
 Id: 5
 User: event_scheduler
 Host: localhost
 db: NULL
Command: Daemon
 Time: 2756681
 State: Waiting on empty queue
 Info: NULL
*************************** 2. row ***************************
 Id: 20
 User: me
 Host: localhost:52943
 db: test
Command: Query
 Time: 0
 State: starting
 Info: SHOW PROCESSLIST

Threads can be killed with the KILL statement.

1 Accessing the Process List

The following discussion enumerates【ɪˈnuːməreɪts 枚举;列举;】 the sources of process information, the privileges required to see process information, and describes the content of process list entries.

1.1 Sources of Process Information

Process information is available from these sources:

• The SHOW PROCESSLIST statement.

• The mysqladmin processlist command.

• The INFORMATION_SCHEMA PROCESSLIST table.

• The Performance Schema processlist table.

• The Performance Schema threads table columns with names having a prefix of PROCESSLIST_.

• The sys schema processlist and session views.

The threads table compares to SHOW PROCESSLIST, INFORMATION_SCHEMA PROCESSLIST, and mysqladmin processlist as follows:

• Access to the threads table does not require a mutex and has minimal impact on server performance. The other sources have negative【ˈneɡətɪv 负面的;消极的;负极的;否定的;有害的;坏的;结果为阴性的（或否定的）;缺乏热情的;含有否定词的;】 performance consequences【ˈkɑnsəˌkwɛnsəz 后果;结果;重要性;】 because they require a mutex.

【As of MySQL 8.0.22, an alternative implementation for SHOW PROCESSLIST is available based on the Performance Schema processlist table, which, like the threads table, does not require a mutex and has better performance characteristics.】

• The threads 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 the threads table can be used to monitor thread activity the other sources cannot.

• You can enable or disable Performance Schema thread monitoring.

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

The sys schema processlist view presents【prɪˈzents 提出;(以某种方式)展现，显示，表现;提交;颁发;授予;把…交给;】 information from the Performance Schema threads table in a more accessible format. The sys schema session view presents information about user sessions like the sys schema processlist view, but with background processes filtered out.

1.2 Privileges Required to Access the Process List

For most sources of process information, if you have the PROCESS privilege, you can see all threads, even those belonging to other users. Otherwise (without the PROCESS privilege), nonanonymous users have access to information about their own threads but not threads for other users, and anonymous 【ə'nɑnəməs 匿名的，无名的；无个性特征的】 users have no access to thread information.

The Performance Schema threads table also provides thread information, but table access uses a different privilege model.

1.3 Content of Process List Entries

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

• Id is the connection identifier for the client associated with the thread.

• User and Host indicate the account associated with the thread.

• db is the default database for the thread, or NULL if none has been selected.

• Command and State 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【ɪnˈvestɪɡeɪtɪd 研究的；调查的;】.

The following sections list the possible Command values, and State values grouped by category. The meaning for some of these values is self-evident. For others, additional description is provided.

【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【ˈnoʊʃn 概念;观念;理解;信念;】 of the current time may be altered in some cases: The thread can change the time with SET 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.

• Info indicates the statement the thread is executing, or NULL if 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 different process information source).

2 Thread Command Values

A thread can have any of the following Command values:

 • Binlog Dump

This is a thread on a replication source for sending binary log contents to a replica.

• Change user

The thread is executing a change user operation.

•Close stmt

The thread is closing a prepared statement.

• Connect

Used by replication receiver threads connected to the source, and by replication worker threads.

• Connect Out

A replica is connecting to its source.

• Create DB

The thread is executing a create database operation.

• Daemon

This thread is internal to the server, not a thread that services a client connection.

• Debug

The thread is generating debugging information.

• Delayed insert

The thread is a delayed insert handler【ˈhændlər 操作者;搬运工;顾问;(尤指)驯犬员;驯兽员;组织者;】.

• Drop DB

The thread is executing a drop database operation.

• Error

• Execute

The thread is executing a prepared statement.

• Fetch

The thread is fetching the results from executing a prepared statement.

• Field List

The thread is retrieving information for table columns.

• Init DB

The thread is selecting a default database.

• Kill

The thread is killing another thread.

• Long Data

The thread is retrieving long data in the result of executing a prepared statement.

• Ping

The thread is handling a server ping request.

• Prepare

The thread is preparing a prepared statement.

• Processlist

The thread is producing information about server threads.

• Query

Employed for user clients while executing queries by single-threaded replication applier threads, as well as by the replication coordinator thread.

• Quit

The thread is terminating.

• Refresh

The thread is flushing table, logs, or caches, or resetting status variable or replication server information.

 • Register Slave

The thread is registering a replica server.

• Reset stmt

The thread is resetting a prepared statement.

• Set option

The thread is setting or resetting a client statement execution option.

• Shutdown

The thread is shutting down the server.

• Sleep

The thread is waiting for the client to send a new statement to it.

• Statistics

The thread is producing server status information.

• Time

Unused.

3. NDB Cluster Thread States

• Committing events to binlog

• Opening mysql.ndb_apply_status

• Processing events

The thread is processing events for binary logging.

• Processing events from schema table

The thread is doing the work of schema replication.

• Shutting down

• Syncing ndb table schema operation and binlog

This is used to have a correct binary log of schema operations for NDB.

• Waiting for allowed to take ndbcluster global schema lock

The thread is waiting for permission to take a global schema lock.

• Waiting for event from ndbcluster

The server is acting as an SQL node in an NDB Cluster, and is connected to a cluster management node.

• Waiting for first event from ndbcluster

• Waiting for ndbcluster binlog update to reach current position

• Waiting for ndbcluster global schema lock

The thread is waiting for a global schema lock held by another thread to be released.

• Waiting for ndbcluster to start

• Waiting for schema epoch

The thread is waiting for a schema epoch (that is, a global checkpoint).

4.Event Scheduler Thread States

These states occur for the Event Scheduler thread, threads that are created to execute scheduled events, or threads that terminate【ˈtɜːrmɪneɪt 终止;结束;(使)停止;到达终点站;】 the scheduler.

• Clearing

The scheduler thread or a thread that was executing an event is terminating and is about to end.

• Initialized

The scheduler thread or a thread that executes an event has been initialized.

• Waiting for next activation

The scheduler has a nonempty event queue but the next activation is in the future.

• Waiting for scheduler to stop

The thread issued SET GLOBAL event_scheduler=OFF and is waiting for the scheduler to stop.

• Waiting on empty queue

The scheduler's event queue is empty and it is sleeping.