MySQL线程处于Waiting

作者: 银河网站登录  发布:2019-11-20

 

近期超越一个案例,非常多询问被打断没有回来结果,使用show processlist查看,开采大多MySQL线程处于Waiting for table flush状态,查询语句从来被卡住,只可以通过Kill进度来解决。那么大家先来拜访Waiting for table flush的官方表达:

 

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卡塔 尔(英语:State of Qatar)中,大家选拔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;

 

 

银河网站登录 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;

 

银河网站登录 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> 

 

银河网站登录 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> 

 

| 银河网站登录 4

 

留心:大家要求Kill线程13, Kill掉线程17是湮灭不了难点的。

 

 

 

传延宗族景况中,非常多时候只怕不是lock table read引起的隔绝,而是由于慢查询,招致flush table一直不可能关闭该表而直白处在等候状态,比方上面测量试验案例中,笔者使用相近张大表做笛Carl积模拟多个慢查询,别的操作相近,如下所示,你会见到相近发出了Waiting for table flush

 

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

 

银河网站登录 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中Time值非常的大的线程。然后甄别确认后Kill掉,如上截图所示,会话连接14便是挑起短路的源流SQL。有种规律就是其一线程的Time列值必定比被封堵的线程要高。那么些就会过滤超多记下。

 

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

 

对于实验中采纳lock table read这种景象,这种对话大概处于Sleep状态,何况它也不会现出在show engine innodb status G命令的出口音讯中。 即使show open tables where in_use >=1;能找到是那张表被lock住了,不过力所不及稳固到实际的线程(连接卡塔 尔(英语:State of Qatar),其实这一个是五个不喜欢的标题。然而inntop那款利器就足以稳定到,如下所示,线程17锁住了表test,在innotop里面就能够一定到是线程17。所谓工欲善其事必先利其器!

 

银河网站登录 6

 

银河网站登录 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> 

 

银河网站登录 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

 

 

对话连接(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> 

 

银河网站登录 9

 

 

 

 

参照他事他说加以考察资料:

 

本文由银河网站登录发布于银河网站登录,转载请注明出处:MySQL线程处于Waiting

关键词: