前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Slave SQL线程与PXB FTWRL死锁问题分析

Slave SQL线程与PXB FTWRL死锁问题分析

作者头像
GreatSQL社区
发布2024-04-30 17:23:15
发布2024-04-30 17:23:15
1110
举报
文章被收录于专栏:GreatSQL出品技术文章

1. 问题背景 2.27号凌晨生产环境MySQL备库在执行备份期间出现因FLUSH TABLES WITH READ LOCK未释放导致备库复制延时拉大,慢日志内看持锁接近25分钟未释放。 版本:

  • MySQL 5.7.21
  • PXB 2.4.18

慢查询日志:

备份脚本中的备份命令:

mysql_kill.sh的主要逻辑内容:

备份参数:

2. 问题复现及分析 2.1 问题分析

  • 144是SQL线程,并行复制中的Coordinator线程;
  • 145/146是并行复制的worker线程,145/146worker线程队列中的事务可以并行执行。
  • 162线程是执行innobackup执行的flush tables with read lock;

144 Coordinator线程分发relay log中事务时发现这个事务不能执行,要等待前面的事务完成提交,所以处于waiting for dependent transaction to commit的状态。145/146线程和备份线程162形成死锁,145线程等待162线程 global read lock 释放,162线程占有MDL::global read lock 全局读锁,申请全局commit lock的时候阻塞等待146线程,146线程占有MDL:: commit lock,因为从库设置slave_preserve_commit_order=1,保证从库binlog提交顺序,而146线程执行事务对应的binlog靠后面,所以等待145的事务提交。最终形成了145->162->146->145的死循环,形成死锁。 三个线程相互形成死锁,还是很少见的。 2.2 相关参数为何未生效 --ftwrl-wait-timeout=60 指的是执行FTWRL之前,如果检测到存在长SQL,先等待指定时间(秒),如果超时后还存在长SQL,则备份报错退出。默认为0则表示立即执行。 --ftwrl-wait-threshold=5 指的是执行FTWRL之前,检测长SQL的方法,如果在执行flush前存在已经运行了超过指定时间(秒)的SQL,则将该SQL定义为长SQL,默认60s。 --kill-long-queries_timeout=0 在执行FTWRL后,如果flush操作被阻塞了N秒,则kill掉阻塞它的线程,默认0的情况就是不kill任何阻塞flush的SQL,直到该SQL执行完成。 从上面各个参数的解释,不难看出,--ftwrl-wait-*参数是针对执行FTWRL之前的长SQL检测机制,对于已执行FTWRL时无济于事,--kill-long-*参数则是设置默认值0,不起任何作用。 3. 结论与建议

  • PXB备份中执行FTWRL加全局读锁与SQL线程形成死锁是导致本次从库延迟过高的原因。
  • 启用--kill-long-queries_type--kill-long-queries_timeout参数,在检测到flush被阻塞后执行kill掉相关线程的操作。比较暴力,存在较大的风险,若备库无业务访问则可考虑。
  • 启用--safe-slave-backup参数,执行备份时该参数会停掉SQL线程,从而避免死锁的产生。仅建议在无业务访问的备库上执行。
  • 设置MySQL参数slave_preserve_commit_order=0,关闭从库binlog的顺序提交,关闭该参数只是影响并行复制的事务在从库的提交顺序,对最终的数据一致性并无影响,所以如果无特别要求从库的binlog顺序必须与主库保持一致,可以考虑设置slave_preserve_commit_order=0避免死锁的产生。

Enjoy GreatSQL :)

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2024-04-28,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 GreatSQL社区 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1. 问题背景 2.27号凌晨生产环境MySQL备库在执行备份期间出现因FLUSH TABLES WITH READ LOCK未释放导致备库复制延时拉大,慢日志内看持锁接近25分钟未释放。 版本:
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档