【Mysql】从库大量select堵塞，Waiting for table flush 状态

最新推荐文章于 2024-06-25 02:43:56 发布

转载最新推荐文章于 2024-06-25 02:43:56 发布 · 945 阅读

文章标签：

#数据库 #devops #运维

背景：

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

1.检查

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

2.重现环境

session1
1. 终端一执行一个慢查询
2. mysql> select *,sleep(1000) from operation_log limit 100
session2
1. 终端二执行xtracebackup备份
2. 。。。。。
3. 。。。。。
4. >> log scanned up to (768745274)
5. 。。。。。备份堵塞
查看此时的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 |
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. ...堵塞状态
此时的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阻塞了步骤二,步骤二导致步骤三需要等待步骤一。
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. +-----+--------------+--------+------------+
解决办法：
杀掉原始慢查询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.