Analysis of MySQL thread in Waiting for table flush

##I recently encountered a case and many inquiries No results are returned due to blocking. Use show processlist to check and find that many MySQL threads are in the Waiting for table flush state. The query statement has been blocked and can only be solved by killing the process. So let’s first take a look at the official explanation of 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.

# Then let’s simulate that the thread is in Waiting for table The flush status is as shown:

##In the first session connection (connection id=13), we use lock table to lock the table test.

#

mysql> use MyDB;

Database changed

mysql> select connection_id();

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

| connection_id() |

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

|              13 |

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

1 row in set (0.00 sec)

 <br>

mysql> lock table test read;

Query OK, 0 rows affected (0.00 sec)

 <br>

mysql>

#In the second session connection (connection id=17), we You can execute flush table or flush table test. At this point you will find that the flush table is blocked.

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

 <br>

Database changed

mysql> select connection_id();

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

| connection_id() |

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

|              17 |

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

1 row in set (0.00 sec)

 <br>

mysql> flush table test;

 

 

 

 

In the third session/connection, when you switch to MyDB, you will be prompted“##You can turn off this feature to get a quicker startup with -A” , is in blocking state at this time. At this time, you exit the session and use parameter -A to log in to the database. If you query the test table, it will be blocked (Of course, querying other tables will not be blocked). As follows:

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;

In the fourth session/connection, we use show processlist to view the status of all connection threads in the current database. You will see that 17 and 18 are in the Waiting for table flush state. As shown in the screenshot below:

 

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)

 <br>

mysql>

 

 

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)

 <br>

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)

 <br>

mysql> kill 17;

Query OK, 0 rows affected (0.00 sec)

 <br>

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)

 <br>

mysql> kill 13;

Query OK, 0 rows affected (0.00 sec)

 <br>

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)

 <br>

mysql>

##|

## Note: We You need to kill thread 13. Killing thread 17 will not solve the problem.

#Production In many environments, it may not be the blocking caused by the lock table read, but the slow query, which causes the flush table to be unable to close the table and remains in a waiting state. For example, in the test case below, I use the same large table to simulate Cartesian product A slow query, other operations are the same, as shown below, you will see that Waiting for table flush is also generated

 

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

 

 

 

In addition, there is a case on the Internet. When mysqldump is backed up, if the parameters are not used ##—single-transaction or due to the simultaneous use of flush-logs and —The two parameters of single-transaction may also cause such a waiting scenario. When these two parameters are put together, a FLUSH TABLES operation will be performed before starting to dump data.

##solution:

#

When Waiting for table flush appears, we generally need to find those tables that are locked or those slow queries that cause the flush table to be waiting and unable to close the table. Then just kill the corresponding thread, but how to accurately locate it is a challenge, especially in a production environment. If you use show processlist, you will see a large number of threads. It makes you dizzy, how to locate the problem at once?

For slow Situation when other threads are in Waiting for table flush state caused by query:

##You can view threads with large Time values ​​in show processlist. Then kill after screening and confirmation. As shown in the screenshot above, session connection 14 is the source SQL that causes the blocking. There is a rule that the Time column value of this thread must be higher than that of the blocked thread. This can filter many records.

for lock Situation when other threads are in Waiting for table flush state caused by table read:

# #

For the case where lock table read is used in the experiment, this session may be in Sleep state, and it will not appear in the output information of the show engine innodb status \G command. Even if show open tables where in_use >=1; can find out which table is locked, it cannot locate the specific thread (connection). In fact, this is a headache. But inntop can be used to locate it. As shown below, thread 17 has locked the table test, and thread 17 can be located in innotop. As the saying goes, if you want to do your job well, you must first sharpen your tools!

#

In addition, in the official documentation ALTER TABLE, RENAME TABLE,##REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE can cause this kind of wait , some simple tests were also done below, as shown below:

#Another scenario of Waiting for table flush

 

会话连接（connection id=18)执行下面SQL语句，模拟一个慢查询SQL

 

mysql> select connection_id();

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

| connection_id() |

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

|              18 |

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

1 row in set (0.00 sec)

 <br>

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)

 <br>

mysql>

 

会话连接（connection id=8)执行下面SQL语句

 

mysql> select connection_id();

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

| connection_id() |

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

|               8 |

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

1 row in set (0.00 sec)

 <br>

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)

 <br>

mysql>

 

 

 

 

Waiting for table metadata lock

 

 

会话连接（connection id=17)执行下面SQL语句，模拟一个慢查询SQL

 

 

mysql> select connection_id();

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

| connection_id() |

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

|              17 |

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

1 row in set (0.00 sec)

 <br>

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)

 <br>

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)

 <br>

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)

 <br>

mysql>

 

 

The above is the detailed content of Analysis of MySQL thread in Waiting for table flush. For more information, please follow other related articles on the PHP Chinese website!