mysql show processlist


MySQL线程列表
MySQL分析数据运行状态利器
SHOW PROCESSLIST

show processlist和show full processlist
processlist命令的输出结果显示 哪些线程在运行
可以查看当前所有的连接数
可以查看当前的连接状态 帮助识别出有问题的查询语句等

root帐号 能看到所有用户的当前连接
普通帐号 只能看到自己占用的连接
showprocesslist只能列出当前100条
可以使用SHOW FULL PROCESSLIST命令 全部列出

各个列的含义
id列 用户登录mysql时 系统分配的 connection_id  使用函数connection_id()查看
user列  当前用户 不是root 只显示用户权限范围的sql语句
host列  命令是从哪个ip的哪个端口上发的  用来跟踪出现问题语句的用户
db列    进程目前连接的是哪个数据库
command列 显示当前连接的执行的命令 一般取值为休眠(sleep) 查询(query) 连接(connect)等
time列   显示这个状态持续的时间 单位是秒
state列  显示使用当前连接的sql语句的状态 很重要的列
state 描述的是语句执行中的某一个状态 sql语句 以查询为例 可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
info列 显示这个sql语句 是判断问题语句的一个重要依据

SHOW [FULL] PROCESSLIST
官方文档的描述
SHOW PROCESSLIST shows you which threads are running. You can also get this information from the
INFORMATION_SCHEMA PROCESSLIST table or the mysqladmin processlist command. If you have
the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is,
threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only
the first 100 characters of each statement are shown in the Info field.
意思就是说上述指令是用来查看那些线程正在运行
也可以得到这些信息 从INFORMATION_SCHEMA PROCESSLIST这个表
或者通过mysqladmin processlist指令
如果有PROCESS权限  可以查看所有的线程
否则 只能查看自己当前账户的线程
如果没有使用FULL关键字
只能查看每个记录中Info字段里面的前100个字符

通过查询information_schema的processlist表
select * from information_schema.processlist;
或者mysqladmin processlist指令
mysqladmin processlist -u root -p

官方的介绍
关于这个命令的价值
The SHOW PROCESSLIST statement is very useful if you get the “too many connections” error message
and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that
have the SUPER privilege, to ensure that administrators should always be able to connect and check the
system (assuming that you are not giving this privilege to all your users).
Threads can be killed with the KILL statement.
运行的线程 可以通过KILL指令将其杀掉
这个信息非常重要。
SHOW PROCESSLIST指令里面的输出内容 有几个字段
Id
The connection identifier. This is the same type of value displayed in the ID column of the
INFORMATION_SCHEMA.PROCESSLIST table, the PROCESSLIST_ID column of the Performance
Schema threads table, and returned by the CONNECTION_ID() function.

User
The MySQL user who issued the statement. If this is system user, it refers to a nonclient thread
spawned by the server to handle tasks internally. This could be the I/O or SQL thread used on replication
slaves or a delayed-row handler. unauthenticated user refers to a thread that has become
associated with a client connection but for which authentication of the client user has not yet been done.
event_scheduler refers to the thread that monitors scheduled events. For system user, there is no
host specified in the Host column.

Host
The host name of the client issuing the statement (except for system user where there is no host).
SHOW PROCESSLIST reports the host name for TCP/IP connections in host_name:client_port
format to make it easier to determine which client is doing what.

db
The default database, if one is selected, otherwise NULL.

Command
The type of command the thread is executing. 例如上面的例子中 Sleep 或者Query

Time
The time in seconds that the thread has been in its current state. For a slave SQL thread, the value is
the number of seconds between the timestamp of the last replicated event and the real time of the slave
machine.

State
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.
An action, event, or state that indicates what the thread is doing.

Info
The statement the thread is executing, or NULL if it is not executing any statement. The statement might
be the one sent to the server, or an innermost statement if the statement executes other statements. For
example, if a CALL statement executes a stored procedure that is executing a SELECT statement, the
Info value shows the SELECT statement.

分析问题 重要的是Id,Host,Time,State以及Info字段
能看出问题的是Time State和Info字段

更详细的Info字段 请用SHOW FULL PROCESSLIST指令


线上的一个问题 加索引加不上去 将应用停掉还是加不上去
通过SHOW PROCESSLIST指令 发现有好多线程Command处于Query状态
最长的Time字段显示达到230172seconds 换算成小时 63个小时

为了解决问题 调用KILL指令
KILL [CONNECTION | QUERY] processlist_id

官方介绍
Each connection to mysqld runs in a separate thread. You can kill a thread with the KILL
processlist_id statement.

参数中的processlist_id 来源于show processlist结果列表中的id字段
kill指令支持两个可选参数 CONNECTION以及QUERY
• KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with
the given processlist_id, after terminating any statement the connection is executing.
• KILL QUERY terminates the statement the connection is currently executing, but leaves the connection
itself intact.

connection选项 kill的时候 将连接也断掉
而query选项 kill的过程只是将该指令杀掉 连接还保持
kill指令不指定connection或者query选项时 默认是connection