mysql执行状态分析show processlist

mysql> show processlist;

 

+—–+————-+——————–+

执行状态分析

| Id | User | Host | db | Command | Time| State | Info

l Sleep状态

+—–+————-+——————–+

n
通常代表资源未释放,如果是通过连接池,sleep状态应该恒定在一定数量范围内

|207|root |192.168.0.2:51621 |mytest | Sleep | 5 | | NULL

n
实战范例:因前端数据输出时(特别是输出到用户终端)未及时关闭数据库连接,导致因网络连接速度产生大量sleep连接,在网速出现异常时,数据库too
many connections挂死。

美高梅电子游戏 ,|208|root |192.168.0.2:51622 |mytest | Sleep | 5 | | NULL

n
简单解读,数据查询和执行通常只需要不到0.01秒,而网络输出通常需要1秒左右甚至更长,原本数据连接在0.01秒即可释放,但是因为前端程序未执行close操作,直接输出结果,那么在结果未展现在用户桌面前,该数据库连接一直维持在sleep状态!

|220|root |192.168.0.2:51676 |mytest |Query | 84 | locked |

l Waiting for net, reading from net, writing to net

select name,culture,value,type from book where id=1

n 偶尔出现无妨

说明各列的含义和用途,

n 如大量出现,迅速检查数据库到前端的网络连接状态和流量

id列:一个标识,你要kill 一个语句的时候很有用。

n
案例:因外挂程序,内网数据库大量读取,内网使用的百兆交换迅速爆满,导致大量连接阻塞在waiting
for net,数据库连接过多崩溃

user列:
显示当前用户,如果不是root,这个命令就只显示你权限范围内的sql语句。

l Locked状态

host列:显示这个语句是从哪个ip
的哪个端口上发出的。可用来追踪出问题语句的用户。

n 有更新操作锁定

db列:显示这个进程目前连接的是哪个数据库。

n
通常使用innodb可以很好的减少locked状态的产生,但是切记,更新操作要正确使用索引,即便是低频次更新操作也不能疏忽。如上影响结果集范例所示。

command列:显示当前连接的执行的命令,一般就是休眠(sleep),查询(query),连接(connect)。

n
在myisam的时代,locked是很多高并发应用的噩梦。所以mysql官方也开始倾向于推荐innodb。

time列:此这个状态持续的时间,单位是秒。

l Copy to tmp table

state列:显示使用当前连接的sql语句的状态,很重要的列,后续会有所有的状态的描述,请注意,state只是语句执行中的某一个状态,一个sql语句,以查询为例,可能需要经过copying
to tmp table,Sorting result,Sending data等状态才可以完成。

n
索引及现有结构无法涵盖查询条件,才会建立一个临时表来满足查询要求,产生巨大的恐怖的i/o压力。

info列:显示这个sql语句,因为长度有限,所以长的sql语句就显示不全,但是一个判断问题语句的重要依据。

n
很可怕的搜索语句会导致这样的情况,如果是数据分析,或者半夜的周期数据清理任务,偶尔出现,可以允许。频繁出现务必优化之。

这个命令中最关键的就是state列,mysql列出的状态主要有以下几种:

n Copy to tmp table通常与连表查询有关,建议逐渐习惯不使用连表查询。

Checking table

n 实战范例:

正在检查数据表(这是自动的)。

u
某社区数据库阻塞,求救,经查,其服务器存在多个数据库应用和网站,其中一个不常用的小网站数据库产生了一个恐怖的copy
to tmp table操作,导致整个硬盘i/o和cpu压力超载。Kill掉该操作一切恢复。

Closing tables

l Sending data

正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。

n Sending
data并不是发送数据,别被这个名字所欺骗,这是从物理磁盘获取数据的进程,如果你的影响结果集较多,那么就需要从不同的磁盘碎片去抽取数据,

Connect Out

n 偶尔出现该状态连接无碍。

复制从服务器正在连接主服务器。

n 回到上面影响结果集的问题,一般而言,如果sending
data连接过多,通常是某查询的影响结果集过大,也就是查询的索引项不够优化。

Copying to tmp table on disk

n 如果出现大量相似的SQL语句出现在show
proesslist列表中,并且都处于sending
data状态,优化查询索引,记住用影响结果集的思路去思考。

由于临时结果集大于
tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。

l Storing result to query cache

Creating tmp table

n 出现这种状态,如果频繁出现,使用set
profiling分析,如果存在资源开销在SQL整体开销的比例过大(即便是非常小的开销,看比例),则说明query
cache碎片较多

正在创建临时表以存放部分查询结果。

n 使用flush query cache可即时清理,也可以做成定时任务

deleting from main table

n Query cache参数可适当酌情设置。

服务器正在执行多表删除中的第一部分,刚删除第一个表。

l Freeing items

deleting from reference tables

n 理论上这玩意不会出现很多。偶尔出现无碍

服务器正在执行多表删除中的第二部分,正在删除其他表的记录。

n 如果大量出现,内存,硬盘可能已经出现问题。比如硬盘满或损坏。

Flushing tables

n i/o压力过大时,也可能出现Free items执行时间较长的情况。

正在执行 FLUSH TABLES,等待其他线程关闭数据表。

l Sorting for …

Killed

n 和Sending
data类似,结果集过大,排序条件没有索引化,需要在内存里排序,甚至需要创建临时结构排序。

发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。

l 其他

Locked

n
还有很多状态,遇到了,去查查资料。基本上我们遇到其他状态的阻塞较少,所以不关心

被其他查询锁住了。

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注