Waiting for table flush故障处理

一 原理总结

官方手册中关于Flush tables的介绍：

Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement.

flush tables会关闭所有已打开的表对象，同时将查询缓存中的结果清空。

 

Flush tables会先等待所有正在运行的SQL请求结束。然而，正在使用的表对象是不能关闭的（如SQL请求仍在运行），因此，Flush Tables操作会被正在运行的SQL请求阻塞。

试验发现：如果flush tables被阻塞，那么导致flush tables阻塞的sql语句所查询的表的相关查询都会被阻塞，如有个查询t1表的sql执行比较慢，此时flush tables，则flush tables会被阻塞，且后续查询t1的sql都会被阻塞。

MySQL会隐式（后台表对象管理线程）或显式（flush tables等）来关闭已打开但并没有使用的表对象。所以遇到慢的select sql，也要及时处理掉，否则很可能会引发故障。

二 小示例

2.1 故障模拟

在一个会话执行SELECT SLEEP(300) FROM dba.d：

在另一个会话执行flush tables，该语句会被阻塞：

在第三个会话查询d表(select * from dba.d)，也被阻塞:

查询当前进程状态：

2.2 故障排查

2.2.1 找到正处于open状态的表

show open tables where in_use >=1;

2.2.2 找到引发故障的慢查询

① 找到阻塞flush tables的慢查询

SELECT * FROM information_schema.`PROCESSLIST`

WHERE info IS NOT NULL

AND state NOT LIKE 'Waiting for table flush'

AND info NOT LIKE 'SELECT * FROM information_schema.`PROCESSLIST`%'

AND INFO LIKE '%处于打开状态的表名%'

ORDER BY TIME DESC

 

② 找到flush tables慢查询

SELECT * FROM information_schema.`PROCESSLIST`

WHERE info LIKE '%flush tables%'

AND info NOT LIKE 'SELECT * FROM information_schema.`PROCESSLIST`%'

2.3 故障处理

杀掉2.2.2 步骤中找到的两个慢查询

kill id;

三 生产故障案例

昨天下午收到慢查询告警：

看到是select语句，觉得select不会被阻塞，也不会阻塞其他查询，因此就没管它。

结果第二天早晨醒来发现有好多慢查询，且状态都处于Waiting for table flush，如：

通过看告警邮件，了解到昨晚23:58分的时候还是只有那一个慢查询，没有其他慢查询，所以猜测和0点的自动备份有关系，而且mysqldump默认会短暂调用flush tables with read lock。

我把备份进程杀掉后，再杀掉了这个慢查询。其他状态都处于Waiting for table flush的慢查询立即消失了。

 

总结：

安全意识有待提升。

对任何慢查询都需要警惕，对select慢查询也不能掉以轻心。

 

--本篇文章参考了http://blog.chinaunix.net/uid-31401119-id-5781305.html