MySQL執行緒處於Waiting for table flush的分析
- 大家讲道理原創
- 2017-08-19 10:47:472895瀏覽
#最近遇到一個案例，很多查詢被阻斷沒有回傳結果，使用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 reads have cad the table, it must wait until all other reads have in thread. ################This notification takes place if another thread has used FLUSH TABLES# 或 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)
<br>
mysql> lock table test read;
Query OK, 0 rows affected (0.00 sec)
<br>
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
<br>
Database changed
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 17 |
+-----------------+
1 row in set (0.00 sec)
<br>
mysql> flush table test;
在第三個會話/連線中，當你切換到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;
#
############################# ############## #########在第四個會話/連接，我們用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)
<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>
| 
##
#注意：我們需要Kill線程13， Kill掉線程17是解決不了問題的。
#
##生產在環境中，很多時候可能不是lock table read引起的阻塞，而是由於慢查詢，導致flush table一直無法關閉該表而一直處於等待狀態，例如下面測試案例中，我使用同一張大表做笛卡爾積模擬一個慢查詢，其它操作相同，如下所示，你會看到同樣產生了Waiting for table flush# #mysql>從TEST1 T、TEST1 L 中選擇T.*;
## ########## 另外，網路上有個案例，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住了，但是無法定位到具體的線程（連接），其實這個是一個頭痛的問題。但是inntop這款利器就可以定位到，如下圖所示，線程17鎖住了表test，在innotop裡面就能定位到是線程17。所謂工欲善其事必先利其器！ #
另外，在官方文件中ALTER TABLE, RENAME TABLE, #REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE都能引起這類等待，以下也做了一些簡單測試，如下： # ###################################################### ###Waiting for table flush的另一個場景################ 会话连接（connection id=18)执行下面SQL语句，模拟一个慢查询SQL 会话连接（connection id=6)执行下面SQL语句，分析表test 会话连接（connection id=8)执行下面SQL语句 查看线程的状态，你会发现被阻塞的会话处于 Waiting for table flush状态。 因为当对表做了ANALYZE TABLE后，后台针对该表的查询需要等待，因为MySQL已经检测到该表内部变化，需要使用FLUSH TABLE关闭然后重新打开该表，所以当你查询该表时，就会处于 Waiting for table flush Waiting for table metadata lock 会话连接（connection id=17)执行下面SQL语句，模拟一个慢查询SQL 会话连接（connection id=6）执行下面SQL语句， 修改表结构操作 会话连接（connection id=8）执行下面SQL语句，查询表test 查看线程的状态，你会发现被阻塞的会话处于 Waiting for table metadata lock状态。
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 18 |
+-----------------+
1 row in set (0.00 sec)
<br>
mysql> select name, sleep(64) from 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>
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 8 |
+-----------------+
1 row in set (0.00 sec)
<br>
mysql> select * from test;
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>
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 17 |
+-----------------+
1 row in set (0.00 sec)
<br>
mysql> select name, sleep(100) from test;
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
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 8 |
+-----------------+
1 row in set (0.00 sec)
<br>
mysql> select * from test;
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>
以上是MySQL執行緒處於Waiting for table flush的分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！