Home > mysql > mysql中的Waiting for tables

mysql中的Waiting for tables

October 21st, 2011 Leave a comment Go to comments

接着上篇中遇到的mysql子查询,在问题的诊断中,丹臣注意到一个较为严重的问题,就是我们生产库中全部的数据库访问请求都处于Waiting for tables的状态,在将大查询kill掉后,所有的请求恢复正常;简单的理解为大查询阻塞了其他访问请求,但是这个理论是不可信,如果阻塞该表的DML还可以理解,但是把该数据库上的所有请求都阻塞了,这还是说不通的。那么我们就来看看所有的请求处于Waiting for tables这个状态是什么原因导致的:

The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_nameALTER TABLERENAME TABLEREPAIR TABLEANALYZE TABLE, orOPTIMIZE TABLE.

从文档上的解释来看,是主库做了一个flush tables的操作,导致所有的表都需要打开,但是由于在10-07号放假,应该不会有人在主库上执行flush tables,而且从日志中可以看到:

1044 system user Connect 27406 Flushing tables FLUSH TABLES

所以可以判断是系统自己执行了这个操作,那么既然不是主库上执行而来,那么这个flush tables操作是从slave上复制过来的(M-M结构),

那么备库的什么操作会有flush tables,真的百思不其解,我们备库在6点之前做的是什么,后端应用的dump?还是数据库的备份?是不是xtrabackup,很有可能是xtrabackup在备份的时候做的fulsh tables,查看备份脚本,应该轮到mysqldump做逻辑备份操作了,并不是xtrabackup,检查了mysqldump的备份脚本,脚本里:

-uroot -P$port –protocol=tcp –single-transaction –master-data=2是这样的

Single-transaction这个选项是加上了的,希望再一次被打破 >_<

最后想还是想到到官网上去看看,mysqldump+flush tables是否有bug,

唉,搜索了一下果然发现了蹊跷:

http://bugs.mysql.com/bug.php?id=35157

When using the –master-data option with mysqldump, mysqldump uses a FLUSH TABLES command. However, this statement got replicated to the slave(s), which caused the slave(s) to block unnecessarily while the FLUSH tables command completed.

在5.0存在的bug很好的解释了这个问题,在mysqldump加入了–master-data就会将flush tables记录到binglog中,然后在被同步到主库,主库执行binglog后,由于有一个大查询正在

执行,这个子查询由于执行了很长时间,阻塞了flush tables的操作,最后导致了雪崩,所有的请求都被阻塞:

The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table,

it must wait until all other threads have closed the table in question.

综合上篇的所写,Mysqldump的在5.0的bug加上数据库的低效子查询构成这次故障的原因。

峰回路转,山穷水尽,哈哈

^_^

Categories: mysql Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.