Mysql从库大量select堵塞处于Waiting for table flush 状态该怎么办

本篇文章给大家分享的是有关Mysql从库大量select堵塞处于Waiting for table flush 状态该怎么办，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

背景：

1. mycat读写分离，应用大量select超时

1.检查

1. 通过检查发现大量select处于Waiting for table flush 状态，仔细看了一下processlist以及时间段，可以断定是备份加select慢查询引起的！

2.重现环境

1. session1
   session2
   查看此时的processlist状态
   
   

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. 终端二执行xtracebackup备份

10. 。。。。。

11. 。。。。。

12. >> log scanned up to (768745274)
    

13. 。。。。。备份堵塞

14. 终端一执行一个慢查询

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. ...堵塞状态

5. 
   

3. 此时的processlist状态
   
   

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阻塞了步骤二,步骤二导致步骤三需要等待步骤一。
    

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 | 开发工程师 | 开发工程师开发工程师 | 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 | 系统管理 | 1 | 2 |
    

17. | 600 | 环境管理 | 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成功获得锁之前，必须等待所有语句执行完成（包括SELECT）。所以如果有个慢查询在执行，或者一个打开的事务，或者其他进程拿着表锁，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 状态该怎么办，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。