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

創(chuàng)新互聯建站專注于松滋網站建設服務及定制，我們擁有豐富的企業(yè)做網站經驗。 熱誠為您提供松滋營銷型網站建設，松滋網站制作、松滋網頁設計、松滋網站官網定制、重慶小程序開發(fā)公司服務，打造松滋網絡公司原創(chuàng)品牌,更為您提供松滋網站排名全網營銷落地服務。

背景：

1. mycat讀寫分離，應用大量select超時

1.檢查

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

2.重現環(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 | Waitingfor 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_loglimit 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)

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 | Waitingfor table flush| FLUSH NO_WRITE_TO_BINLOG TABLES|

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

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

11. 步驟1阻塞了步驟二,步驟二導致步驟三需要等待步驟一。

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 | 超級管理員 | 所有權限 | 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可加參數 --lock-wait-query-type=all

8. xtrace版本2.4可加參數 --ftwrl-wait-query-type

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

原因：
在flush tables with read lock成功獲得鎖之前，必須等待所有語句執(zhí)行完成（包括SELECT）。所以如果有個慢查詢在執(zhí)行，或者一個打開的事務，或者其他進程拿著表鎖，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)該怎么辦，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注創(chuàng)新互聯行業(yè)資訊頻道。