8.14 检查线程信息
When you are attempting to ascertain what your MySQL server is doing, it can be helpful to examine the process list, which is the set of threads currently executing within the server. 进程列表信息可以从以下来源中获得:
SHOW [FULL] PROCESSLIST
语句: Section 13.7.6.29, “SHOW PROCESSLIST 语法SHOW PROFILE
语句: Section 13.7.6.31, “SHOW PROFILES 语法INFORMATION_SCHEMA
PROCESSLIST
表: Section 25.19, “The INFORMATION_SCHEMA PROCESSLIST Table”- The mysqladmin processlist 命令: Section 4.5.2, “mysqladmin — 管理 MySQL 服务器的客户端”
- The Performance Schema
threads
table, stage tables, and lock tables: Section 26.12.17, “Performance Schema Miscellaneous Tables”, Section 26.12.5, “Performance Schema Stage Event Tables”, Section 26.12.12, “Performance Schema Lock Tables”.
Access to threads
does not require a mutex and has minimal impact on server performance. INFORMATION_SCHEMA.PROCESSLIST
and SHOW PROCESSLIST
have negative performance consequences because they require a mutex. threads
also shows information about background threads, which INFORMATION_SCHEMA.PROCESSLIST
and SHOW PROCESSLIST
do not. This means that threads
can be used to monitor activity the other thread information sources cannot.
You can always view information about your own threads. To view information about threads being executed for other accounts, you must have the PROCESS
privilege.
Each process list entry contains several pieces of information:
Id
是线程关联的客户端的连接标识符.User
和Host
表示与线程关联的账户.db
是线程默认的数据库, 如果没有选择, 则为 NULL.Command
和State
表示线程正在做什么.大多数状态对应非常快速的操作, 如果一个线程在给定的状态中停留了许多秒, 那么可能需要研究一下这个问题.
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 thread running on a slave that is processing events from the master, the thread time is set to the time found in the events and thus reflects current time on the master and not the slave.Info
contains the text of the statement being executed by the thread, or NULL if it is not executing one. By default, this value contains only the first 100 characters of the statement. To see the complete statements, useSHOW FULL PROCESSLIST
.The
sys
schema processlist view, which presents information from the Performance Schemathreads
table in a more accessible format: Section 27.4.3.22, “The processlist and x$processlist Views”- The
sys
schemasession
view, which presents information about user sessions (like thesys
schemaprocesslist
view, but with background processes filtered out): [Section 27.4.3.33, “The session and x$session Views”(https://dev.mysql.com/doc/refman/8.0/en/sys-session.html)]
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.