|
|
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
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.
来源:https://www.cnblogs.com/xuliuzai/p/18225451
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！ |
|
