MySQL线程处于Waiting for table flush的剖析

 

如今遇见一个案例,很多查询被封堵没有回去结果,使用show
processlist查看,发现众多MySQL线程处于Waiting for table
flush状态,查询语句一贯被堵塞,只可以通过Kill进程来化解。那么我们先来看望Waiting
for table
flush的官方表达:https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

 

Waiting
for table flush

 

The
thread is executing FLUSH TABLES and is
waiting for all threads to close their tables, or 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_name, ALTER TABLE, RENAME
TABLE, REPAIR TABLE, ANALYZE
TABLE, or OPTIMIZE
TABLE.

 

 

这就是说我们接下去模拟一下线程处于Waiting
for table flush状态的情景,如所示:

 

在率先个会话连接(connection
id=13)中,我们拔取lock table 锁定表test。 

 

mysql> use MyDB;

Database changed

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|              13 |

+-----------------+

1 row in set (0.00 sec)

 

mysql> lock table test read;

Query OK, 0 rows affected (0.00 sec)

 

mysql> 

 

 

 

在其次个会话连接(connection
id=17)中,我们进行flush table 或 flush table test
皆可。此时您会发觉flush table处于阻塞状态。

 

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|              17 |

+-----------------+

1 row in set (0.00 sec)

 

mysql> flush table test;

 

 

MySQL 1

 

 

在第几个会话/连接中,当您切换来MyDB时,就会指示“You
can turn off this feature to get a quicker startup with -A”
,此时居于阻塞状态。此时你退出会话,使用参数-A登录数据库后,你一旦查询test表,就会处于阻塞状态(当然查询其他表不会被堵塞)。如下所示:

 

mysql>
use MyDB;

Reading
table information for completion of table and column names

You
can turn off this feature to get a quicker startup with -A

 

 

mysql>
use MyDB;

Database
changed

mysql>
select * from test;

 

MySQL 2

 

 

在第两个会话/连接,我们用show
processlist查看到眼前数据库所有连接线程状态,你会看到17、18都地处Waiting
for table flush的情况。如下截图所示:

 

mysql> show processlist;

+----+------+-----------+------+---------+------+-------------------------+--------------------+

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

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| 13 | root | localhost | MyDB | Sleep   |   90 |                         | NULL               |

| 14 | root | localhost | NULL | Query   |    0 | init                    | show processlist   |

| 17 | root | localhost | MyDB | Query   |   52 | Waiting for table flush | flush table test   |

| 18 | root | localhost | MyDB | Query   |    9 | Waiting for table flush | select * from test |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

4 rows in set (0.00 sec)

 

mysql> 

 

MySQL 3

 

mysql> show processlist;

+----+------+-----------+------+---------+------+-------------------------+--------------------+

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

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| 13 | root | localhost | MyDB | Sleep   |   90 |                         | NULL               |

| 14 | root | localhost | NULL | Query   |    0 | init                    | show processlist   |

| 17 | root | localhost | MyDB | Query   |   52 | Waiting for table flush | flush table test   |

| 18 | root | localhost | MyDB | Query   |    9 | Waiting for table flush | select * from test |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

4 rows in set (0.00 sec)

 

mysql> 

mysql> 

mysql> 

mysql> 

mysql> show open tables where in_use >=1;

+----------+-------+--------+-------------+

| Database | Table | In_use | Name_locked |

+----------+-------+--------+-------------+

| MyDB     | test  |      1 |           0 |

+----------+-------+--------+-------------+

1 row in set (0.00 sec)

 

mysql> kill 17;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show processlist;

+----+------+-----------+------+---------+------+-------------------------+--------------------+

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

+----+------+-----------+------+---------+------+-------------------------+--------------------+

| 13 | root | localhost | MyDB | Sleep   |  442 |                         | NULL               |

| 14 | root | localhost | NULL | Query   |    0 | init                    | show processlist   |

| 18 | root | localhost | MyDB | Query   |  361 | Waiting for table flush | select * from test |

+----+------+-----------+------+---------+------+-------------------------+--------------------+

3 rows in set (0.00 sec)

 

mysql> kill 13;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+

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

+----+------+-----------+------+---------+------+-------+------------------+

| 14 | root | localhost | NULL | Query   |    0 | init  | show processlist |

| 18 | root | localhost | MyDB | Sleep   |  427 |       | NULL             |

+----+------+-----------+------+---------+------+-------+------------------+

2 rows in set (0.00 sec)

 

mysql> 

 

|
MySQL 4

 

留神:大家需要Kill线程13,
Kill掉线程17是解决不了问题的。

 

 

 

生产条件中,很多时候恐怕不是lock
table read引起的不通,而是由于慢查询,导致flush
table一贯无法关闭该表而直接处于等候意况,例如上面测试案例中,我动用同一张大表做笛Carl积模拟一个慢查询,其余操作相同,如下所示,你会看出同一爆发了Waiting
for table flush

 

mysql>
SELECT T.* FROM TEST1 T, TEST1 L;

 

MySQL 5

 

 

除此以外,网上有个案例,mysqldump备份时,尽管没有运用参数—single-transaction
或由于同时利用了flush-logs与—single-transaction六个参数也恐怕滋生这么的等候场景,这多少个三个参数放在一块儿,会在始发dump数据从前先实施一个FLUSH
TABLES操作。

 

 

 

釜底抽薪方案:

** 

 

并发Waiting
for table flush时,我们一般需要找到这多少个表被lock住或这个慢查询导致flush
table一贯在等候而望洋兴叹关闭该表。然后Kill掉对应的线程即可,不过怎么精准定位是一个挑衅,尤其是生产条件,你利用show
processlist会看到大量的线程。让你眼花缭乱的,怎么一转眼定位问题呢?

 

对此慢查询引起的任何线程处于Waiting
for table flush状态的场所:

 

可以查看show
processlist中提姆e值很大的线程。然后甄别确认后Kill掉,如上截图所示,会话连接14就是挑起短路的源流SQL。有种规律就是以此线程的提姆(Tim)e列值必定比被堵塞的线程要高。这些就能过滤很多笔录。

 

对此lock
table read引起的别样线程处于Waiting for table flush状态的状态:

 

对此实验中利用lock
table read这种状况,这种对话可能处于Sleep状态,而且它也不会冒出在show
engine innodb status \G命令的出口信息中。 就算show open tables where
in_use
>=1;能找到是这张表被lock住了,但是不能稳定到具体的线程(连接),其实这多少个是一个讨厌的题目。不过inntop这款利器就可以一定到,如下所示,线程17锁住了表test,在innotop里面就能一定到是线程17。所谓工欲善其事必先利其器!

 

MySQL 6

 

MySQL 7

 

 

 

其它,在合法文档中ALTER
TABLE, RENAME
TABLE, REPAIR
TABLE, ANALYZE
TABLE, or OPTIMIZE
TABLE都能引起这类等待,下边也做了部分简约测试,如下所示:

 

 

 

Waiting
for table flush的另外一个场所

 

对话连接(connection
id=18)执行下边SQL语句,模拟一个慢查询SQL

 

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|              18 |

+-----------------+

1 row in set (0.00 sec)

 

mysql> select name, sleep(64) from test;

 

对话连接(connection
id=6)执行下边SQL语句,分析表test

 

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|               6 |

+-----------------+

1 row in set (0.00 sec)

mysql> analyze table test;

+-----------+---------+----------+----------+

| Table     | Op      | Msg_type | Msg_text |

+-----------+---------+----------+----------+

| MyDB.test | analyze | status   | OK       |

+-----------+---------+----------+----------+

1 row in set (0.04 sec)

 

mysql> 

 

对话连接(connection
id=8)执行下边SQL语句

 

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|               8 |

+-----------------+

1 row in set (0.00 sec)

 

mysql> select * from test;

 

翻看线程的场馆,你会意识被卡住的对话处于
Waiting for table flush状态。 因为当对表做了ANALYZE
TABLE后,后台针对该表的询问需要等待,因为MySQL已经检测到该表内部变化,需要选用FLUSH
TABLE关闭然后重新打开该表,所以当您询问该表时,就会处于 Waiting for
table flush

 

mysql> show processlist;

+----+------+-----------+------+---------+------+-------------------------+----------------------------------+

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

+----+------+-----------+------+---------+------+-------------------------+----------------------------------+

|  6 | root | localhost | MyDB | Sleep   |   22 |                         | NULL                             |

|  8 | root | localhost | MyDB | Query   |   14 | Waiting for table flush | select * from test               |

| 15 | root | localhost | NULL | Sleep   |    3 |                         | NULL                             |

| 16 | root | localhost | NULL | Query   |    0 | init                    | show processlist                 |

| 18 | root | localhost | MyDB | Query   |   46 | User sleep              | select name, sleep(64) from test |

+----+------+-----------+------+---------+------+-------------------------+----------------------------------+

5 rows in set (0.00 sec)

 

mysql> 

 

MySQL 8

 

 

 

Waiting
for table metadata lock

 

 

对话连接(connection
id=17)执行下边SQL语句,模拟一个慢查询SQL

 

 

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|              17 |

+-----------------+

1 row in set (0.00 sec)

 

mysql> select name, sleep(100) from test;

 

 

对话连接(connection
id=6)执行下面SQL语句, 修改表结构操作

 

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|               6 |

+-----------------+

1 row in set (0.00 sec)

 

mysql> alter table test add tname varchar(10); // rename table test to kkk 同样会引起Waiting for table metadata lock

 

 

MySQL,对话连接(connection
id=8)执行下面SQL语句,查询表test

 

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

|               8 |

+-----------------+

1 row in set (0.00 sec)

 

mysql> select * from test;

 

 

查看线程的场合,你会意识被封堵的对话处于
Waiting for table metadata lock状态。

 

 

mysql> show processlist;

+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+

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

+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+

|  6 | root | localhost | MyDB | Query   |   19 | Waiting for table metadata lock | alter table test add tname varchar(10) |

|  8 | root | localhost | MyDB | Query   |    6 | Waiting for table metadata lock | select * from test                     |

| 15 | root | localhost | NULL | Sleep   |    8 |                                 | NULL                                   |

| 16 | root | localhost | NULL | Query   |    0 | init                            | show processlist                       |

| 17 | root | localhost | MyDB | Query   |   55 | User sleep                      | select name, sleep(100) from test      |

+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+

5 rows in set (0.00 sec)

 

mysql> 

 

MySQL 9

 

 

 

 

参考资料:

 

https://www.percona.com/blog/2013/02/27/mysql-optimizer-analyze-table-and-waiting-for-table-flush/

http://www.cnblogs.com/jackhub/p/3841004.html

http://myrock.github.io/2014/11/20/mysql-waiting-for-table-flush/

http://mysql.taobao.org/monthly/2016/03/10/

网站地图xml地图