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

发布时间:2021-11-16 09:17:52 作者:柒染
来源:亿速云 阅读:206

本篇文章给大家分享的是有关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. ...堵塞状态


  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 状态该怎么办,小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。

推荐阅读:
  1. MYSQL 主从添加新从库
  2. Mysql数据库理论基础二

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql select

上一篇:MYSQL外键的坏处有哪些

下一篇:MySQL5.6 Online DDL在线状态是怎么样的

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》