[MySQL] 主从延迟现象及原理分析

「爱情、让人受尽委屈。」 2022-05-10 23:46 182阅读 0赞

# 一、现象 #

凌晨对线上一张表添加索引，表数据量太大(1亿+数据，数据量50G以上)，造成主从延迟几个小时，各个依赖从库的系统无法查询数据，最终影响业务。

现在就梳理下主从延迟的原理。

# 二、原理 #

根据 MySQL 官方文档 [MySQL Replication Implementation Details][] 中的描述，MySQL 主从复制依赖于三个线程：`master` 一个线程(`Binlog dump thread`)，`slave` 两个线程(`I/O thread` 和 `SQL thread`)。主从复制流程如下图：

![主从复制原理][20190128100514547.png]

master 服务器和 slave 服务器连接时，创建 `Binlog dump thread` 以发送 `bin log` 数据：

1.  一个 `Binlog dump thread` 对应一个 slave 服务器；
2.  `Binlog dump thread` 从 `bin log` 获取数据时会加锁，获取到数据后，立即释放锁。

当 slave 服务器收到 [START\_SLAVE][START_SLAVE] 命令时，会创建 `I/O thread` 和 `SQL thread`：

1.  `I/O thread` 以**拉**的方式，从 master 读取事件，并存储到 slave 服务器的 `relay log` 中；
2.  `SQL thread` 从 `relay log` 中读取事件并执行；
3.  `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` 读取更新的频率，就有可能出现主从延迟的情况，如:

1.  `master` 写入 `tps` 较高，大于 `slave` 更新速度；
2.  `slave` 执行某些语句耗时较长，如持有锁等；
3.  `master` 执行某些 `DDL` 语句时，执行的时间较长，在 `slave` 也执行相同的时间；

此处创建了索引，咨询 DBA，产生的 `bin log` 文件有100多G，数据量太大，导致从库 `I/O thread` 一直读取 `DDL` 操作产生的 `bin log` 事件，而影响到正常的业务 `DML` 事件的更新，从而表现为主从同步延迟。

# 四、解决方案 #

从主从延迟的原因来看，解决方案可以从以下几个方向入手：

1.  **业务选型**，对于无法忍受从库延迟的架构，可选择分布式架构等，避开从库延迟问题
2.  **执行时间**，对大表进行线上 `DDL` 操作尽量选择凌晨等业务量较小的时候
3.  **硬件配置**，升级从库硬件配置，如SSD
4.  **减少请求**，增加缓存层，减少读请求落库
5.  **并行复制**，MySQL 5.7支持基于组的并行复制：相同 GTID 的事务可以并行回放

[MySQL Replication Implementation Details]: https://dev.mysql.com/doc/refman/5.6/en/replication-implementation-details.html
[20190128100514547.png]: /images/20220511/d4227b41e07443d8a8c60115c6dbfd96.png
[START_SLAVE]: https://dev.mysql.com/doc/refman/5.6/en/start-slave.html
[SHOW SLAVE STATUS]: https://dev.mysql.com/doc/refman/5.6/en/show-slave-status.html
[SHOW PROCESSLIST]: https://dev.mysql.com/doc/refman/5.6/en/show-processlist.html