Mysql從庫大量select堵塞處于Waiting for table flush 狀態(tài)該怎么辦

本篇文章給大家分享的是有關(guān)Mysql從庫大量select堵塞處于Waiting for table flush 狀態(tài)該怎么辦，小編覺得挺實用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

背景：

1. mycat讀寫分離，應(yīng)用大量select超時

1.檢查

1. 通過檢查發(fā)現(xiàn)大量select處于Waiting for table flush 狀態(tài)，仔細看了一下processlist以及時間段，可以斷定是備份加select慢查詢引起的！

2.重現(xiàn)環(huán)境

1. session1
   session2
   查看此時的processlist狀態(tài)
   
   

1. mysql> show full processlist;
   

2. +------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
   

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

4. +------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
   

5. | 2 | repl | 47.93.243.162:43700 | NULL | Binlog Dump | 1527333 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
   

6. | 9140 | root | localhost | devops | Query | 564 | User sleep | select *,sleep(1000) from operation_log limit 100 |
   

7. | 9141 | root | localhost | NULL | Query | 0 | init | show full processlist |
   

8. | 9143 | root | localhost:56880 | NULL | Query | 509 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES |

9. 終端二執(zhí)行xtracebackup備份

10. 。。。。。

11. 。。。。。

12. >> log scanned up to (768745274)
    

13. 。。。。。備份堵塞

14. 終端一執(zhí)行一個慢查詢

15. mysql> select *,sleep(1000) from operation_log limit 100

2. session3
   
   

1. 終端3對慢查詢涉及到的表進行查詢操作
   

2. [root@iZ2ze66bhrbxkc31nljgjnZ ~]# mysql -uroot -p***** -e "select * from operation_log limit 10" devops
   

3. Warning: Using a password on the command line interface can be insecure.
   

4. ...堵塞狀態(tài)

5. 
   

3. 此時的processlist狀態(tài)
   
   

1. mysql> show full processlist;
   

2. +------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
   

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

4. +------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+
   

5. | 2 | repl | 47.93.243.162:43700 | NULL | Binlog Dump | 1527460 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
   

6. | 9140 | root | localhost | devops | Query | 691 | User sleep | select *,sleep(1000) from operation_log limit 100 |
   

7. | 9141 | root | localhost | NULL | Query | 0 | init | show full processlist |
   

8. | 9143 | root | localhost:56880 | NULL | Query | 636 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES |

9. | 9150 | root | localhost | devops | Query | 454 | Waiting for table flush | select * from operation_log limit 10 | --查詢被堵塞
   

10. +------+------+---------------------+--------+-------------+---------+-----------------------------------------------------------------------+---------------------------------------------------+

11. 步驟1阻塞了步驟二,步驟二導(dǎo)致步驟三需要等待步驟一。
    

4. session4
   
   

1. 終端四對其它非慢查詢中的表進行查詢（不堵塞）
   

2. [root@iZ2ze66bhrbxkc31nljgjnZ ~]# mysql -uroot -pESBecs00 -e "select * from role limit 10" devops
   

3. Warning: Using a password on the command line interface can be insecure.
   

4. +----+-----------------+--------------------------------+--------+
   

5. | id | role_name | description | status |
   

6. +----+-----------------+--------------------------------+--------+
   

7. | 1 | 超級管理員 | 所有權(quán)限 | 1 |
   

8. | 3 | 開發(fā)工程師 | 開發(fā)工程師開發(fā)工程師 | 1 |
   

9. | 4 | 運維工程師 | 運帷工程師運帷工程師 | 1 |
   

10. +----+-----------------+--------------------------------+--------+
    

11. [root@iZ2ze66bhrbxkc31nljgjnZ ~]# mysql -uroot -pESBecs00 -e "select * from module limit 10" devops
    

12. Warning: Using a password on the command line interface can be insecure.
    

13. +-----+--------------+--------+------------+
    

14. | id | module_name | status | list_order |
    

15. +-----+--------------+--------+------------+
    

16. | 100 | 系統(tǒng)管理 | 1 | 2 |
    

17. | 600 | 環(huán)境管理 | 1 | 3 |
    

18. +-----+--------------+--------+------------+

5. 解決辦法：

6. 殺掉原始慢查詢sql即可！

7. xtrace版本2.2可加參數(shù) --lock-wait-query-type=all

8. xtrace版本2.4可加參數(shù) --ftwrl-wait-query-type

9. 該選項表示獲得全局鎖之前允許那種查詢完成，默認是ALL，可選update。

原因：
在flush tables with read lock成功獲得鎖之前，必須等待所有語句執(zhí)行完成（包括SELECT）。所以如果有個慢查詢在執(zhí)行，或者一個打開的事務(wù)，或者其他進程拿著表鎖，flush tables

with read lock就會被阻塞，直到所有的鎖被釋放。

1. The thread got a notification that the underlying structure for a table has changed
   

2. and it needs to reopen the table to get the new structure.
   

3. However, to reopen the table,
   

4. it must wait until all other threads have closed the table in question.
   

5. This notification takes place if another thread has used FLUSH TABLES
   

6. or one of the following statements on the table in question:
   

7. FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, orOPTIMIZE TABLE.

以上就是Mysql從庫大量select堵塞處于Waiting for table flush 狀態(tài)該怎么辦，小編相信有部分知識點可能是我們?nèi)粘９ぷ鲿姷交蛴玫降?。希望你能通過這篇文章學(xué)到更多知識。更多詳情敬請關(guān)注億速云行業(yè)資訊頻道。