MySQL主从延迟问题排查

最新推荐文章于 2023-01-05 09:01:38 发布

三思呐三思

最新推荐文章于 2023-01-05 09:01:38 发布

阅读量1.5k

点赞数 2

分类专栏： MySQL 文章标签： MySQL复制延迟

本文链接：https://blog.csdn.net/weixin_37692493/article/details/118903025

版权

22 篇文章 2 订阅

订阅专栏

通常情况下我们的业务都是读多写少型的业务模式，数据库层面针对该模式下的优化，一般建议使用MySQL一主多从的读写分离架构。所有的变更操作路由到master实例，所有的非强一致性读可路由到多台slave实例上，当业务量上涨我们可以通过扩展slave实例的个数来横向的扩展业务读能力。

使用读写分离架构，从业务层面来看，我们往往比较关注从slave实例读取数据的一致性，业务是否可以接受读取到落后的数据，可接受落后数据的最大时延是多少？从数据库的角度来看，未避免主从之间复制出现较大延迟，我们也需要从架构的部署、数据库表结构的设置、数据库的规范使用上来尽量的避免这个问题。

一、主从复制概念

对于MySQL的主从复制的搭建，必须保证以下参数的设置：

主从复制流程如下：

	a）优化数据库性能来降低负载
	b）提升数据库配置。生产环境中建议只读实例配置至少要与主实例配置相同，避免因为配置相差较大导致的复制延迟。
	c）新增slave实例来横向扩展业务读请求，降低只读实例的负载压力
	d）优化业务慢查询

2）查看slave复制是否开启并行复制，可通过并行复制一定程度上来降低复制延迟

现象
1）master、slave数据库实例上资源均未出现较大的瓶颈
2）slave数据库实例全局状态参数 Innodb_rows_xxx 参数不断上涨或 show engine innodb status 中的 [ROW OPERATIONS] 中存在大量的 DML操作数
3） show slave status 中 Exec_Master_Log_Pos 位点一直不变化
处理思路
业务层考虑切分大事务，少量多次分批执行

现象
1）无主键表的批量更新传输到slave进行应用，由于参数 slave_rows_search_algorithms 设置为 INDEX_SCAN,TABLE_SCAN ，slave对于无主键表的每条记录检索都是一次全表扫描操作，因此若为无主键表的批量，应用期间一定程度上会导致slave负载上涨明显
2）通过 show slave status 发现 Exec_Master_Log_Pos 参数长时间未出现变化，show engine innodb status 中或者全局状态参数中的 Innodb_rows_xxx ，insert/delete/update相对较小，但是read却很大
3）show slave status 中 Exec_Master_Log_Pos 位点一直不变化
处理思路
1）分析主从复制延迟期间的Binlog日志，判断延迟期间slave应用的对应变更操作涉及到表是否为无主键表
2）为无主键表添加自增长主键

现象
1）slave数据库实例负载未出现明显上涨
2）通过 show slave status 发现 Exec_Master_Log_Pos 参数长时间未出现变化，show engine innodb status 中或者全局状态参数中的 Innodb_rows_xxx ，insert/delete/update相对较小，且read也基本正常
3）show slave status 中 Exec_Master_Log_Pos 位点一直不变化
4）查看数据库会话，存在长时间未提交的会话或者大查询一直未完成
处理思路
1）询问业务层面在复制延迟时间段左右是否有做相关的DDL操作
2）若数据库开启performance_schema相关的MDL锁监控参数，可通过相关表排查持有以及阻塞会话信息，将长时间持有且未释放锁资源的会话提交或者kill掉
3）若无法从数据库层面定位阻塞源，建议建长时间未结束的会话kill掉后继续观察slave的复制应用情况

在这里插入图片描述

关注