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
- 减少请求,增加缓存层,减少读请求落库
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对的支持。如果你想了解更多相关内容请查看下面相关链接
下一篇: Docker镜像的导入导出的实现方法