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

共计 4124 个字符，预计需要花费 11 分钟才能阅读完成。

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

背景：

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

1. 检查

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

2. 重现环境

session1
session2
查看此时的 processlist 状态

mysql show full processlist;

+——+——+———————+——–+————-+———+———————————————————————–+—————————————————+

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

+——+——+———————+——–+————-+———+———————————————————————–+—————————————————+

| 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 |

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

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

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

终端二执行 xtracebackup 备份

。。。。。

。。。。。

log scanned up to (768745274)

。。。。。备份堵塞

终端一执行一个慢查询

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

session3

终端 3 对慢查询涉及到的表进行查询操作

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

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

… 堵塞状态

此时的 processlist 状态

mysql show full processlist;

+——+——+———————+——–+————-+———+———————————————————————–+—————————————————+

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

+——+——+———————+——–+————-+———+———————————————————————–+—————————————————+

| 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 |

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

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

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

| 9150 | root | localhost | devops | Query | 454 | Waiting for table flush | select * from operation_log limit 10 | – 查询被堵塞

+——+——+———————+——–+————-+———+———————————————————————–+—————————————————+

步骤 1 阻塞了步骤二, 步骤二导致步骤三需要等待步骤一。

session4

终端四对其它非慢查询中的表进行查询（不堵塞）

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

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

+—-+—————–+——————————–+——–+

| id | role_name | description | status |

+—-+—————–+——————————–+——–+

| 1 | 超级管理员 | 所有权限 | 1 |

| 3 | 开发工程师 | 开发工程师开发工程师 | 1 |

| 4 | 运维工程师 | 运帷工程师运帷工程师 | 1 |

+—-+—————–+——————————–+——–+

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

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

+—–+————–+——–+————+

| id | module_name | status | list_order |

+—–+————–+——–+————+

| 100 | 系统管理 | 1 | 2 |

| 600 | 环境管理 | 1 | 3 |

+—–+————–+——–+————+

解决办法：

杀掉原始慢查询 sql 即可！

xtrace 版本 2.2 可加参数  –lock-wait-query-type=all

xtrace 版本 2.4 可加参数  –ftwrl-wait-query-type

该选项表示获得全局锁之前允许那种查询完成，默认是 ALL，可选 update。

原因：
在 flush tables with read lock 成功获得锁之前，必须等待所有语句执行完成（包括 SELECT）。所以如果有个慢查询在执行，或者一个打开的事务，或者其他进程拿着表锁，flush tables

with read lock 就会被阻塞，直到所有的锁被释放。

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, orOPTIMIZE TABLE.

以上就是 Mysql 从库大量 select 堵塞处于 Waiting for table flush 状态该怎么办，丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。