In a recent case, many queries were blocked and no results were returned. Using show process list, we found that many MySQL threads were in the state of Waiting for table flush. Query statements were blocked all the time and could only be solved by Kill process. Let's take a look at Waiting for table flush's official explanation: 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 the situation where the thread is in the Waiting for table flush state, as shown below:
In the first session connection (connection id=13), we lock the table test with lock table.
mysql> use MyDB;Database changedmysql> 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>
In the second session connection (connection id=17), we can execute either 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select connection_id();+-----------------+| connection_id() |+-----------------+| 17 |+-----------------+1 row in set (0.00 sec)mysql> flush table test;
In the third session/connection, when you switch to MyDB, you are prompted "You can turn off this feature to get a quicker startup with -A", which is blocked. When you exit the session and log in to the database with parameter - A, you will be blocked if you query the test table (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 process list to see all connection threads in the current database, and you will see that 17 and 18 are in the state of Waiting for table flush. As shown in the following screenshot:
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> 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>
| 
Note: We need Kill thread 13. Kill thread 17 can't solve the problem.
In production environments, many times it may not be the blocking caused by lock table read s, but because of slow queries, flush table has been unable to close the table and has been in a waiting state. For example, in the following test case, I use the same large table to simulate a slow query by Cartesian product. The other operations are the same. As shown below, you will see that Waiting for table for flush is also generated.
mysql> SELECT T.* FROM TEST1 T, TEST1 L;
In addition, there is a case on the Internet that when mysqldump is backed up, a FLUSH TABLES operation will be performed before dump data is started if the parameters - single-transaction or flush-logs and - single-transaction are not used at the same time.
Solution:
When Waiting for table flush occurs, we usually need to find tables that are lock ed or that slow queries cause flush table s to wait and cannot close. Then Kill drops the corresponding threads, but how to locate them accurately is a challenge, especially in the production environment, where you can see a lot of threads using the show process list. Let you dazzled, how to locate the problem at once?
For cases where other threads caused by slow queries are in the Waiting for table flush state:
You can see threads with large Time values in the show process list. Then Kill is removed after the screening confirmation. As shown in the screenshot above, session connection 14 is the source SQL causing the blockage. There is a rule that the Time column value of this thread must be higher than that of the blocked thread. This filters many records.
For cases where other threads caused by lock table read are in the Waiting for table flush state:
In the case of lock table read 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 that the table is locked, but can not locate the specific thread (connection), in fact, this is a headache. But inntop can be positioned. As shown below, thread 17 locks the table test and can be positioned as thread 17 in innotop. The so-called "good work must first use its tools!"
In addition, in official documents, ALTER TABLE, RENAME TABLE, REPAIR TABLE, AnalyYZE TABLE, or OPTIMIZE TABLE can cause such waiting. Here are some simple tests, as follows:
Another scenario for Waiting for table flush
Session connection (connection id=18) executes the following SQL statement to simulate a slow query SQL
mysql> select connection_id();+-----------------+| connection_id() |+-----------------+| 18 |+-----------------+1 row in set (0.00 sec)mysql> select name, sleep(64) from test;
Session connection (connection id=6) executes the following SQL statement and parses the table 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>
Session connection (connection id=8) executes the following SQL statement
mysql> select connection_id();+-----------------+| connection_id() |+-----------------+| 8 |+-----------------+1 row in set (0.00 sec)mysql> select * from test;
Looking at the state of the thread, you will find that the blocked session is in the state of Waiting for table flush. Because when the table is analyzed by TABLE, the background needs to wait for the table query, because MySQL has detected the internal changes in the table and needs to close and reopen the table using FLUSH TABLE, so when you query the table, you will be in 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>
Waiting for table metadata lock
Session connection (connection id=17) executes the following SQL statement to simulate a slow query SQL
mysql> select connection_id();+-----------------+| connection_id() |+-----------------+| 17 |+-----------------+1 row in set (0.00 sec)mysql> select name, sleep(100) from test;
Session connection (connection id=6) executes the following SQL statement to modify the table structure operation
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 It can also cause Waiting for table metadata lock
The session connection (connection id=8) executes the following SQL statement to query the table test
mysql> select connection_id();+-----------------+| connection_id() |+-----------------+| 8 |+-----------------+1 row in set (0.00 sec)mysql> select * from test;
Looking at the state of the thread, you will find that the blocked session is in the Waiting for table metadata lock state.
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>
Reference material:
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/