MySQL主从延迟Seconds_Behind_Master计算方式

1、Seconds_Behind_Master不准？

•大家之前了解到的这个计算方式可能是从库 I/O 线程读取的主库 binlog event 时间戳与 SQL 线程正在执行的 binlog event 的时间戳之间的时间差

这样计算的问题：

1、 首先这样的计算方法本身是不对的，如果本地时间有问题会导致event时间戳不准，从而出现误差，所以计算公式中会记录一下主从库的当前时间差

公式如下：

从库本地时间戳-主从本地的时间差-从库 SQL 线程正在执行的event的时间戳

注意：这个时间差只会在主从线程启动时计算一次，所以start slave后如果主从本地时间出现异常，Seconds_Behind_Master也是不准的，需要重启复制线程重新计算主从本地时间差异（如果最终计算结果是负数，会归零）

2、 如果IO线程出现延迟，此时这个值是有误差的，Seconds_Behind_Master可能显示为0，但实际和主库是有延迟的，容易出现误差

提示：所以只拿这个值来做复制延迟的监控是不准的，一般都会配合心跳表的时间戳来判断当前延迟

2、Seconds_Behind_Master的详细计算方式

每次进行show slave status时都会进行一次计算

源码如下：

if ((mi->get_master_log_pos() == mi->rli->get_group_master_log_pos()) &&
    (!strcmp(mi->get_master_log_name(), mi->rli->get_group_master_log_name())))
{
  if (mi->slave_running == MYSQL_SLAVE_RUN_CONNECT)
    protocol->store(0LL);
  else
    protocol->store_null();
}
else
{
  long time_diff= ((long)(time(0) - mi->rli->last_master_timestamp)
                   - mi->clock_diff_with_master);
  protocol->store((longlong)(mi->rli->last_master_timestamp ? max(0L, time_diff) : 0));
}

这里可以看到判断SQL线程是否应用所有的event

IO线程拉取主库的binlog位置=SQL线程应用到对应的主库binlog位置

注意：此时如果IO线程有延迟则即使Seconds_Behind_Master=0但是还是有延迟

关键词解释

•(long)(time(0))：获取当前从库服务器的运行时间•clock_diff_with_master：IO线程启动时会向主库发送sql语句”SELECT UNIX_TIMESTAMP()“ 获取主库当前时间，然后用从库当前时间-主库当前时间获取差值•last_master_timestamp：指最近一次事务的时间戳，不同场景下不同判断

单线程复制和并行复制对于last_master_timestamp这个的计算也是不同的

•单线程 sql线程从relay-log中读取event，每个event header部分的timestamp就是last_master_timestamp，会先更新这个值然后再执行事务

rli->last_master_timestamp= ev->when.tv_sec + (time_t) ev->exec_time;

ev->when.tv_sec表示事件的开始时间。exec_time指事件在主库的执行时间,GTID_EVENT和XID_EVENT才会统计exec_time

总结：事务执行中的event时间都是事务开始时begin的时间戳，GTID_EVENT和XID_EVENT则是事务提交的时间，所以如果存在事务长时间未提交就会出现延迟突然增大然后正常的情况

•举例：一个update，主库延迟5分钟提交，T1为主库执行时间，T1+5为主库提交时间，T2为从库系统时间-主从时间差

•并行复制

先了解一下并行复制的流程

1.协调线程会将binlog event读取到一个工作线程队列GAP，然后分发给worker线程执行2.并行复制时binlog是穿插并发执行的，有一个checkpoint叫lwm，lwm之前的是都执行的binlog，之后的有执行的，有未执行的 举例：有2个线程，9个事务，线程1执行完146，线程2执行完23，此时lwm就是4，因为线程5还未执行3.并行复制根据参数slave_checkpoint_group，默认512个事务和slave_checkpoint_period，默认300ms来推进checkpoint

•结论：

1.并行复制可以理解last_master_timestamp是lwm的timestamp，最老的未提交事务的前一个已经提交事务的结束时间，有时后面的事务都更新了，但是checkpoint没有推进，也会有误差2.单线程复制last_master_timestamp是最新执行事务的开始时间

这导致并行复制和单线程复制之间复制延迟会有差异，差异是slave_checkpoint_period + 事务在备库执行的时间，这就是有时单线程无延迟，换到并行复制反而有轻微延迟的原因

另外DDL的时间戳计算其实是事务执行时间+事务开始时间

3、Seconds_Behind_Master延迟原因总结

•大事务：延迟不会从0开始，会从事务在主库执行了多少s开始，然后逐步降为0•大表DDL 延迟从0开始，在执行完后延迟会骤降•表索引不合理 表没有主键和唯一键 索引过滤性不好等•从库应用不如主库快 1、参数：sync_relay_log，sync_master_info，sync_relay_log_info不合理导致频繁刷盘 2、硬件问题等•从库应用时被锁住 比如xtrabackup备份和并行复制的场景（大家如果后面有兴趣可以单独写一篇介绍）• MTS 中不合理的slave_checkpoint_period参数• 主从库服务器时间不对• 主库事务长时间未提交等

本文主要讲述了seconds_behind_master在不同模式下的计算方式，很多情况会导致这个参数不准确，所以也建议大家还是结合心跳表配合监控延迟比较准确,如有理解偏差欢迎随时指正

本文参考：

1.深入理解MySQL主从原理32讲

2.MySQL · 答疑解惑 · 备库Seconds_Behind_Master计算