MySQL主从延迟现象及原理分析详解
一、现象
凌晨对线上一张表添加索引,表数据量太大(1亿+数据,数据量50G以上),造成主从延迟几个小时,各个依赖从库的系统无法查询数据,最终影响业务。
现在就梳理下主从延迟的原理。
二、原理
根据 MySQL 官方文档 MySQL Replication Implementation Details 中的描述,MySQL 主从复制依赖于三个线程:master
一个线程(Binlog dump thread
),slave
两个线程(I/O thread
和SQL thread
)。主从复制流程如下图:
master 服务器和 slave 服务器连接时,创建Binlog dump thread
以发送bin log
数据:
- 一个
Binlog dump thread
对应一个 slave 服务器; Binlog dump thread
从bin log
获取数据时会加锁,获取到数据后,立即释放锁。
当 slave 服务器收到 START_SLAVE 命令时,会创建I/O thread
和SQL thread
:
I/O thread
以拉的方式,从 master 读取事件,并存储到 slave 服务器的relay log
中;SQL thread
从relay log
中读取事件并执行;slave
可以按照自己的节奏读取和更新数据,也可以随意操作复制进程(启动和停止)。
注: START_SLAVE
命令成功启动线程后,如果后面I/O thread
或SQL thread
因为某些原因停止,则不会有任何的警告,业务方无法感知。可以通过查看 slave 的 error 日志,或者通过 SHOW SLAVE STATUS 查看 slave 上的线程状态。
通过 SHOW PROCESSLIST 可查看线程状态:
Binlog dump thread:
mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 2 User: root Host: localhost:32931 db: NULL Command: Binlog Dump Time: 94 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL
I/O thread 和 SQL thread:
mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** Id: 10 User: system user Host: db: NULL Command: Connect Time: 11 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 11 User: system user Host: db: NULL Command: Connect Time: 11 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL
三、分析
根据上面的原理,由于slave
是单线程(I/O thread
)读取数据,单线程(SQL thread
)更新数据,而master
是多线程写入,那么只要master
写入的频率大于slave
读取更新的频率,就有可能出现主从延迟的情况,如:
master
写入tps
较高,大于slave
更新速度;slave
执行某些语句耗时较长,如持有锁等;master
执行某些DDL
语句时,执行的时间较长,在slave
也执行相同的时间;
此处创建了索引,咨询 DBA,产生的bin log
文件有100多G,数据量太大,导致从库I/O thread
一直读取DDL
操作产生的bin log
事件,而影响到正常的业务DML
事件的更新,从而表现为主从同步延迟。
四、解决方案
从主从延迟的原因来看,解决方案可以从以下几个方向入手:
- 业务选型,对于无法忍受从库延迟的架构,可选择分布式架构等,避开从库延迟问题
- 执行时间,对大表进行线上
DDL
操作尽量选择凌晨等业务量较小的时候 - 硬件配置,升级从库硬件配置,如SSD
- 减少请求,增加缓存层,减少读请求落库
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对我们的支持。如果你想了解更多相关内容请查看下面相关链接