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

最新推荐文章于 2024-07-22 19:18:15 发布
最新推荐文章于 2024-07-22 19:18:15 发布
阅读量48 收藏
点赞数
文章标签： sql adb 数据库
原文链接：https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653941397&idx=1&sn=5f6534f88e8b864b213d4576d39259cf&chksm=bcef553a7690adfc9105c24b06d693810e738291ffdd2828d2bd001e2b1b19254216e047b727&scene=126&sessionid=0
版权

* GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。

1. 问题背景

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

版本：

  • MySQL 5.7.21

  • PXB 2.4.18

慢查询日志：

995710196649a0e2a8d18fa4c04228bd.png

备份脚本中的备份命令：

c534bac62eddb61be8909c25900e1246.png

mysql_kill.sh的主要逻辑内容：

0c43c2e38ea6b7836b62459ca590679d.png

备份参数：

5c56a7960dc29bd4b8ef8c69286d27c5.png

2. 问题复现及分析

2.1 问题分析

307ba782b566522ad4c1ead2ac187678.png

  • 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 :)


《深入浅出MGR》视频课程

戳此小程序即可直达B站

https://www.bilibili.com/medialist/play/1363850082?business=space_collection&business_id=343928&desc=0

文章推荐：

d4966305f23e2f8f1917ac7a8d5206ae.jpeg题图来自我的摄影作品

想看更多技术好文，点个“在看”吧

老叶茶馆_
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql 主从数据不一致,提示： Slave_SQL_Running: No 的解决方法
01-19
本文实例讲述了mysql 主从数据不一致,提示： Slave_SQL_Running No 的解决方法。分享给大家供大家参考，具体如下： 在slave服务器上通过如下命令 mysql> show slave status\G; 显示如下情况： Slave_IO_Running: ...
解决MySQL中的Slave延迟问题的基本教程
12-15
简单说，在master上是并发模式(以InnoDB引擎为主)完成事务提交的，而在slave上，复制线程只有一个sql thread用于binlog的apply，所以难怪slave在高并发时会远落后master。 ORACLE MySQL 5.6版本开始支持多线程复制，...
mysql同步问题之Slave延迟很大优化方法
01-19
简单说，在master上是并发模式(以InnoDB引擎为主)完成事务提交的，而在slave上，复制线程只有一个sql thread用于binlog的apply，所以难怪slave在高并发时会远落后master。  ORACLE MySQL 5.6版本开始支持多线程复制...
SPI.zip_Master/Slave_TSPI接口与SPI_megawin spi
09-24
1:\TSPI_MASTER SPI主机模式 SPICLK <-->P17 MOSI <...2:\TSPI_SLAVE SPI从机模式,中断方式接收 SPICLK <-->P17 MOSI <-->P15 MISO <-->P16 nSS <---> P14
MYSQL同步 Slave_IO_Running: No 或者Slave_SQL_Running: No的解决方法[已测]
09-11
为了服务器更好的安全特将mysql服务器同步一下，show slave statusG出现了Slave_IO_Running: No 错误，特整理了下，方便以后配置
PostgreSQL的Json数据类型如何使用
招风的黑耳CSDN
07-18 439
PostgreSQL中的JSON数据类型提供了一种灵活的方式来存储JSON（JavaScript Object Notation）数据。JSON是一种轻量级的数据交换格式，易于人阅读和编写，同时也易于机器解析和生成。在PostgreSQL中，你可以使用JSON和JSONB（JSON的二进制格式，支持索引和更快的查询）数据类型来存储JSON数据。
PostgreSQL异常：An I/O error occurred while sending to the backend
IT后浪的博客
07-19 361
在使用PostgreSQL数据库批量写入数据的时候，遇到了一个问题，异常内容如下：Cause: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
PostgreSQL的逻辑架构
weixin_41992498的博客
07-18 123
一、PostgreSql的逻辑架构：所有者：
【MySQL】根据binlog日志获取回滚sql的一个开发思路
weixin_45385457的博客
07-19 430
不同客户端之间会交替追加在 binlog 中，需要通过 👆的 binlog 匹配流程来控制匹配。行，需要再次寻找 thread_id 相同的行，匹配到后执行上一个流程。在 binlog 中的线程 ID 记录为 thread_id。一个线程执行多个 sql 回滚到同一个文件可能带来的问题。对应 binlog 日志中的 thread_id=指定 mysql 客户端 开始时间和结束时间。打开 mysql 客户端 开始时间。关闭 mysql 客户端 结束时间。先匹配 thread_id 相同的。
怎样在 PostgreSQL 中实现数据的异地备份？
技术笔记
07-19 1127
总而言之，在 PostgreSQL 中实现数据的异地备份不是一件难事，但也需要我们认真对待，精心规划。选择合适的备份方法和异地存储方式，制定合理的备份策略，定期进行测试和恢复演练，才能确保我们的数据在任何情况下都能“安然无恙”。🎉相关推荐🍅关注博主🎗️带你畅游技术世界，不错过每一次成长机会！📚领书：PostgreSQL 入门到精通.pdf📙PostgreSQL 中文手册📘PostgreSQL 技术专栏🍅CSDN社区-墨松科技。
sqlalchemy定期保持mysql连接活跃
ithongchou的博客
07-18 4684
数据库时，确保保持活跃连接是很重要的，特别是在长时间不使用数据库连接时。使用连接池管理数据库连接。通过配置合适的连接池参数可以有效地保持活跃连接。，以保持连接的活跃状态。这可以通过任务调度库如。（超出连接池大小时允许创建的额外连接数）和。（连接在被放回连接池前的最大生存时间）等。查询，保持数据库连接的活跃状态。可以定期执行简单的查询，例如。这个例子会每隔一段时间执行。
Sqlmap中文使用手册 - Detection模块参数使用
最新发布
Reset
07-22 473
sqlmap是一款自动化检测与利用sql注入漏洞的免费开源工具。
如何查询Oracle数据库一周内每天的SQL执行次数
weixin_60783132的博客
07-18 856
今天引入的问题是：oracle数据库怎么查询一周内，每天的查询次数？数据库的AWR报告会记录SQL的执行情况，可以从DBA_HIST_SNAPSHOT、在DBA_HIST_SQLSTAT这两个视图通过快照 id join一下得到记录了系统七天内Oracle数据库的SQL执行次数。如果有异常，再去分析问题。如果某一条SQL执行异常，需要做分析，怎么找出这条SQL文本。
在 PostgreSQL 里如何实现数据的分布式事务的事务传播和协调？
技术笔记
07-18 893
在分布式系统中，一个事务可能涉及到多个不同的节点或数据库。分布式事务就是要确保在这些多个操作中，要么所有的操作都成功提交，要么所有的操作都回滚，以保持数据的一致性。打个比方，这就好比一个团队合作完成一个项目，如果其中一个环节出了问题，整个项目都可能受到影响，所以需要确保每个环节都能顺利完成，或者在出现问题时能够及时回滚，避免出现部分完成、部分失败的情况。在本文中，我们深入探讨了在 PostgreSQL 中如何实现数据的分布式事务的事务传播和协调。
如何处理 PostgreSQL 中死锁的情况？
技术笔记
07-19 1045
处理 PostgreSQL 中的死锁情况需要我们综合运用预防、检测、解决和优化等多种手段。就像在一场复杂的交通拥堵中，需要交警的指挥、道路的规划、司机的配合以及交通规则的完善，才能保证道路的畅通无阻。对于数据库管理员和开发者来说，要时刻保持警惕，不断优化数据库的设计和操作，以确保系统的稳定和高效运行。只有这样，才能在遇到死锁这个“拦路虎”时，从容应对，顺利解决问题。🎉相关推荐🍅关注博主🎗️带你畅游技术世界，不错过每一次成长机会！📚领书：PostgreSQL 入门到精通.pdf。
Postgresql导入几何数据的几种方式
qq_40098572的博客
07-19 219
通过arcmapsde的方式导入，前提是arcmap要和pgsql版本匹配，首先装好arcmap，在arcmap的目录里面有一个数据支持，在里面查看sde支持的数据库版本，如果不匹配，则无法成功导入。首先，电脑要安装postgresql和对应版本的postgis。​这里注意一下：我目前使用的arcmap是10.2.2，无法打开postgresql12.9的数据库。第二步：启用数据库的postgis，如果不启用，就会导入不进来。最后一步：可以通过arcmap和qgis查看导入的效。
MY SQL
2301_77114936的博客
07-18 237
列2:order_id，整型，外键关联到orders表的id列。列4:total_amount:浮点型，要求数据大于0。列4:gender，字符串，最大长度10，不能为空，列2:name，字符串，最大长度100，不能为空。列5:category，字符串，最大长度50。列1:number，整型，主键自增长。列3:in date: 日期型。列4:quantity，整型。列5:salary，浮点型。，最大长度50，不能为空。列3:price，浮点型。列1:id，整型，主键。列1:id，整型，主键。
使用Spring Retry实现重试机制
weixin_53840353的博客
07-18 578
Spring Retry是Spring提供的一个轻量级库，旨在简化Java应用程序中复杂的重试逻辑。它允许开发者在调用失败时自动重试操作，并提供了丰富的配置选项，例如重试次数、间隔时间和回退策略等。Spring Retry提供了多种回退策略，例如固定延迟、指数回退等。在这个例子中，重试间隔时间将以指数方式增长，初始延迟为1000毫秒，每次重试延迟乘以2，最多延迟5000毫秒。
在 PostgreSQL 中如何实现数据的加密存储？
技术笔记
07-19 951
总的来说，在 PostgreSQL 中实现数据的加密存储是一项重要而又具有挑战性的任务。我们需要根据实际需求选择合适的加密方式，认真管理好加密密钥，充分考虑性能影响，并在实际应用中注意各种细节。这就像是建造一座坚固的城堡，每一块石头、每一道防线都要精心设计和建造。只有这样，我们才能真正保护好数据库中的宝贵数据，让它们在数字世界里安然无恙。🎉相关推荐🍅关注博主🎗️带你畅游技术世界，不错过每一次成长机会！📚领书：PostgreSQL 入门到精通.pdf📙PostgreSQL 中文手册。
如何重新启动slave的IO线程和SQL线程？
12-20
重新启动slave的IO线程和SQL线程的方法如下： 1. 首先，确保你已经登录到MySQL服务器上。 2. 使用以下命令检查当前的主从同步状态： ```sql SHOW SLAVE STATUS\G ``` 在结果中，你会看到类似以下的输出： ``` ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值