一文揭秘MySQL复制之Seconds_Behind_Master的计算
作者介绍
冷正磊，2018年2月加入去哪儿网 DBA 团队，主要负责公司 Redis和机票业务 MySQL 的运维管理工作，同时参与数据库自动化运维平台开发工作，对数据库技术具有浓厚兴趣，具有多年 MySQL 和 Redis 运维管理和性能优化经验。
一、前言
记得在 2018 年我刚刚加入去哪儿网 DBA 团队的时候，生产环境运行的 MySQL 高可用架构有好几种，如比较古老的 MMM 架构、公司自研的 QMHA（类似MHA）架构、以及大名鼎鼎的 Percona 公司出品但国内用的比较少 PXC（Percona XtraDB Cluster）架构，当时公司使用这三种 MySQL 的高可用架构的集群数量不分伯仲，有各自适用的业务和场景。
然而现在公司 80% 以上的 MySQL 集群都是 PXC 架构，通过最近两年左右的时间，我们逐渐淘汰了比较古老且维护繁琐的 MMM 架构，全部改造为 PXC 架构，同时将部分对数据一致性要求较高的业务但架构为 QMHA 的集群，也改造成了 PXC 架构。可能很多人会问，为什么会选择 PXC ？目前这种架构使用的公司并不多，没有足够的使用案例支撑会不会遇到一些无法解决的“疑难杂症”？而且 PXC 本身有一些使用的局限性，怎么能够避免不同技术水平的开发在使用的过程中不出现问题？其实这些问题我们团队内部在确定改造方案之前也讨论过，也是在对比了不同架构的利弊之后做出的决定，去哪儿网 DBA 团队有多年 PXC 集群运维经验且内部有比较完善的文档支持，包括运维和开发规范，这些都极大地增强了 DBA 团队在公司全面推广并落地 PXC 架构的信心。
二、MySQL复制原理简述
在 MySQL 主从复制架构中，一般至少需要有两台数据库服务器，一台服务器的角色是 Master ，另外一台或多台服务器的角色是 Slave ，数据在主从服务器之间的复制流程大致如下：
Master服务器会把数据变更产生的二进制日志通过Dump线程发送给Slave服务器。
Slave服务器中的I/O线程负责接收二进制日志，并保存为中继日志。
Slave服务器中的SQL线程负责执行中继日志，即在Slave服务器上回放Master产生的二进制日志。
通过上面MySQL的复制流程，可以看出这种异步复制可能产生的最大问题就是复制延迟，从而造成主从节点数据不一致，进而影响数据库高可用的容灾切换。造成主从复制延迟的因素有很多，比如：
主从服务器之间的网络问题。
主从服务器的硬件配置差异。
主从库 MySQL Server 的参数配置。
这几个方面的问题都可能影响主从库之间的复制，如果业务层架构设计中有读写分离，就可能会出现客户端迟迟无法读取到主库已经写入数据的情况。所以在使用这种 MySQL 异步复制架构时，为了避免出现以上问题，有一项必不可少的工作就是监控主从节点之间的复制延迟时间，这也是判断集群各节点数据是否一致的重要指标，由此来判断集群发生故障转移时应该选择哪一个从库提升为新的主库。
在运维过程中，一般是通过定时采集 SHOW SLAVE STATUS 语句输出信息中的 Seconds_Behind_Master 字段的值作为监控复制延迟的标准，那么由此也会产生一些常见的问题，比如 Seconds_Behind_Master 的值是否可靠？如果 Seconds_Behind_Master 的值为 0 ，是否一定表示主从复制没有延迟？在 MySQL 内部是怎么计算 Seconds_Behind_Master 的？现在我们来看看 Seconds_Behind_Master 的计算方法，逐步揭开它的神秘面纱。
三、可能计算的方法
方法一
从库 IO 线程读取主库 binlog event 时间戳与 SQL 线程正在执行的 binlog event 的时间戳之间的时间差，单位为秒。
该方法其实就是在计算从库的两个线程处理日志的时间差，基于这个算法，如果主从之间的网络存在很大延迟的时候，主库中就可能存在着大量的 binlog 还没来得及推送到从库，那么此时使用该方法计算出来的延迟时间，跟主从之间数据真正的延迟就没有太大关系了。
方法二
从库所在服务器系统时间与 IO 线程读取的主库 binlog event 时间戳之间的时间差，单位为秒。
这种计算方法比较少见，基于这个算法，如果从库或者主库的操作系统时间被更改了，即主从库的主机时间差本身就比较大的时候，那么计算出来的结果也毫无参考意义。
通过分析，以上两种计算方法貌似都不太靠谱，都存在计算结果与实际延迟时间不一致的问题，那么 MySQL 自身是如何计算这个参数呢？
四、探究MySQL如何计算Seconds_Behind_Master
我们分别从MySQL官方文档中的描述和MySQL源码中的实现这两方面来看看Seconds_Behind_Master的值是如何计算的：
MySQL官方文档关于Seconds_Behind_Master 的论述
在 Oracle MySQL 官方文档中搜索 Seconds_Behind_Master ，即可查到关于如何计算 Seconds_Behind_Master字段值的相关内容。
Seconds_Behind_Master This field is an indication of how “late” the replica is:
When the replica is actively processing updates, this field shows the difference between the current timestamp on the replica and the original timestamp logged on the source for the event currently being processed on the replica.
When no event is currently being processed on the replica, this value is 0.
In essence, this field measures the time difference in seconds between the replica SQL thread and the replica I/O thread. If the network connection between source and replica is fast, the replica I/O thread is very close to the source, so this field is a good approximation of how late the replica SQL thread is compared to the source. If the network is slow, this is not a good approximation; the replica SQL thread may quite often be caught up with the slow-reading replica I/O thread, so Seconds_Behind_Master often shows a value of 0, even if the I/O thread is late compared to the source. In other words, this column is useful only for fast networks.
This time difference computation works even if the source and replica do not have identical clock times, provided that the difference, computed when the replica I/O thread starts, remains constant from then on. Any changes—including NTP updates—can lead to clock skews that can make calculation of Seconds_Behind_Master less reliable.
In MySQL 5.7, this field is NULL (undefined or unknown) if the replica SQL thread is not running, or if the SQL thread has consumed all of the relay log and the replica I/O thread is not running. (In older versions of MySQL, this field was NULL if the replica SQL thread or the replica I/O thread was not running or was not connected to the source.) If the I/O thread is running but the relay log is exhausted, Seconds_Behind_Master is set to 0.
The value of Seconds_Behind_Master is based on the timestamps stored in events, which are preserved through replication. This means that if a source M1 is itself a replica of M0, any event from M1's binary log that originates from M0's binary log has M0's timestamp for that event. This enables MySQL to replicate TIMESTAMP successfully. However, the problem for Seconds_Behind_Master is that if M1 also receives direct updates from clients, the Seconds_Behind_Master value randomly fluctuates because sometimes the last event from M1 originates from M0 and sometimes is the result of a direct update on M1.
When using a multithreaded replica, you should keep in mind that this value is based on Exec_Master_Log_Pos, and so may not reflect the position of the most recently committed transaction.
通过分析文档内容，Seconds_Behind_Master值的计算主要有以下几种情况：
从库正在处理更新。即从库上持续不断有事件被 SQL 线程或者 I/O 线程处理，此字段显示从库主机当前时间戳和主库（原始）的二进制日志中记录的时间戳之间的差。
从库没有处理更新。即从库没有任何需要处理的更新，如果I/O和SQL线程状态都为 Yes ，则此字段显示为 0 ，如果有任意一个线程状态不为 Yes ，则此字段显示为 NULL 。
主从库之间网络的影响。如果主从库之间的网络非常快，那么从库的I/O线程读取的主库 binlog 会与主库中最新的 binlog 非常接近，这样计算得来得值就可以作为主从库之间数据延迟时间。但是如果主从库之间的网络非常慢，可能导致从库 SQL 线程正在重放的主库 binlog 非常接近从库 I/O 线程读取的主库 binlog ，而 I/O 线程因为网络慢的原因可能读取的主库binlog远远落后于主库最新的binlog，此时计算出来的值是不可靠的。尽管这个时候有可能该字段显示为 0 ，但实际上可能从库已经落后于主库非常多了。所以对于网络比较慢的情况，该值并不可靠。
主从库服务器系统时间。如果主库与从库的 server 自身的时间不一致，只要从库复制线程启动之后，没有做过任何时间变更，也可以正常计算这个字段的值，但是如果修改过 server 的时间，则可能导致时钟偏移，从而导致计算出来的这个值不可靠。
从库 SQL 和 I/O 线程状态。如果从库的 SQL 线程没运行，或者 SQL 线程正在运行且已经消费完所有的r elay log ，但是 I/O 线程没有运行，则该字段显示为 NULL（如果I/O线程已经停止，但还存在着 relay log 未重放完成，该字段仍然会显示为复制延迟时间，直到所有 relay log 被重放完成之后，显示为 NULL）。如果 SQL 线程和 I/O 线程都运行着，但是处于空闲状态（SQL线程已经重放完I/O线程产生的relay log），则该字段显示为 0 。
单/多线程的影响。Seconds_Behind_Master 字段的值是通过存储在主库 binlog event 中的时间戳与从库当前时间戳之差计算出来的（保留这个时间戳会通过复制拓扑同步到从库，如果主从库的操作系统时间戳存在差异，则还要减去此差值）。这意味着正常的复制情况下（排除人为在从库写入数据的情况）主库与从库上的 binlog event 的时间戳都来自主库。这种计算 Seconds_Behind_Master 字段值的算法存在一个问题：在单线程复制场景下，如果从库上通过客户端连接进入，并直接更新数据，可能导致该字段的值随机波动，因为有时候 event 来源于主库，有时候由从库直接更新产生的，而这个字段的值会受到后者的影响。但如果是多线程复制，则此值是基于 Exec_Master_Log_Pos 的 event 时间戳来计算的，因此可能不会反映从库最近提交的事务的位置。
MySQL源码中关于 Seconds_Behind_Master 的判断
以下是源码中关于延迟时间计算方法的注释说明：
/*The difference in seconds between the clock of the master and the clock ofthe slave (second - first). It must be signed as it may be <0 or >0.clock_diff_with_master is computed when the I/O thread starts; for this theI/O thread does a SELECT UNIX_TIMESTAMP() on the master."how late the slave is compared to the master" is computed like this:clock_of_slave - last_timestamp_executed_by_SQL_thread - clock_diff_with_master*/long clock_diff_with_master;mi->clock_diff_with_master=(long) (time((time_t*) 0) - strtoul(master_row[0], 0, 10));start_slave_thread->handle_slave_io->get_master_version_and_clock
以下是源码中关于 Seconds_Behind_Master 计算结果的一些判定值：
/*The pseudo code to compute Seconds_Behind_Master: # 阐明这是一段注释关于如何计算Seconds_Behind_Master的伪代码if (SQL thread is running) # 如果SQL线程正在运行，则进入这个if判断内，假设这里标记为if one{if (SQL thread processed all the available relay log) # 如果SQL线程应用完成了所有可用的relay log，则进入这个if判断内，假设这里标记为if two{if (IO thread is running) # 如果I/O线程正在运行，则进入这个if判断内，假设这里标记为if threeprint 0; # 如果if one/two/three三个条件都为真，则延迟值判定为0elseprint NULL; # 如果if one/two为真，if three为假，则延迟值判定为NULL}elsecompute Seconds_Behind_Master; # 如果if one为真，if two为假，则执行公式计算延迟值}elseprint NULL; # 如果if one为假，则延迟值判定为NULL*/if (mi->rli->slave_running){/*Check if SQL thread is at the end of relay logChecking should be done using two conditionscondition1: compare the log positions andcondition2: compare the file names (to handle rotation case)*/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);elseprotocol->store_null();}else{long time_diff= ((long)(time(0) - mi->rli->last_master_timestamp)- mi->clock_diff_with_master);/*Apparently on some systems time_diff can be <0. Here are possiblereasons related to MySQL:- the master is itself a slave of another master whose time is ahead.- somebody used an explicit SET TIMESTAMP on the master.Possible reason related to granularity-to-second of time functions(nothing to do with MySQL), which can explain a value of -1:assume the master's and slave's time are perfectly synchronized, andthat at slave's connection time, when the master's timestamp is read,it is at the very end of second 1, and (a very short time later) whenthe slave's timestamp is read it is at the very beginning of second2. Then the recorded value for master is 1 and the recorded value forslave is 2. At SHOW SLAVE STATUS time, assume that the differencebetween timestamp of slave and rli->last_master_timestamp is 0(i.e. they are in the same second), then we get 0-(2-1)=-1 as a result.This confuses users, so we don't go below 0: hence the max().last_master_timestamp == 0 (an "impossible" timestamp 1970) is aspecial marker to say "consider we have caught up".*/protocol->store((longlong)(mi->rli->last_master_timestamp ?max(0L, time_diff) : 0)); # 这里time_diff其实就是最终计算的Seconds_Behind_Master 值，如果为负数，则直接归零}}
从上面的源码中，我们可以分析出几个问题：
1. 为什么 Seconds_Behind_Master 为 0 不代表没有延迟
我们先看看如何判断 SQL 线程应用完所有了的 event ？
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())))
如果主从库之间的网络有延迟，从库的 SQL 线程应用的 event 的速度可能比 IO 线程读取主库的 event 的速度更快，所以这时候虽然从库的 SQL 线程应用完了所有的 event ，并且 Seconds_Behind_Master 的值也显示为 0 ，但是并不代表从库没有延迟，因为主库由于网络原因有部分 event 没有被从库的 IO 线程读取，从而造成延迟。因此 Seconds_Behind_Maste r的值为 0 不代表主从库之间没有延迟。
2. 影响Seconds_Behind_Master计算的因素
从上面的源码部分，我们可以看到计算 Seconds_Behind_Master 的代码：
long time_diff= ((long)(time(0)- mi->rli->last_master_timestamp)- mi->clock_diff_with_master);
(long)(time(0)
当前从库服务器的系统时间。
mi->clock_diff_with_master
从库服务器与主库服务器系统时间的差值。
这个值只在IO线程启动时进行一次计算，所以在启动IO线程之后人为修改从库服务器的系统时间，会导致 Seconds_Behind_Master 的计算值出现问题，甚至出现负数的情况。
protocol->store((longlong)(mi->rli->last_master_timestamp ?max(0L, time_diff) : 0));
mi->rli->last_master_timestamp
这个值的取值比较复杂，需要分 DML （单线程复制和多线程复制）和 DDL 两种不同的情况进行讨论：
当执行 DML 语句时，且为单线程复制
SQL 线程每次执行 binlog event 时获取主库该 event 的执行时间。
rli->last_master_timestamp= ev->common_header->when.tv_sec + (time_t)ev->exec_time;当执行DML语句时，且为多线程（MTS）复制
在 MTS 情况下，last_master_timestamp 的取值为检查点位置事务 XID_EVENT的timestamp ，大致流程如下：
一、工作线程执行到事务XID_EVENT时，会设置如下值：ptr_group->ts= common_header->when.tv_sec + (time_t)exec_time; // Seconds_behind_master related二、当进行检查点时设置变量ts，调用函数mts_checkpoint_routine()/*Update the rli->last_master_timestamp for reporting correct Seconds_behind_master.If GAQ is empty, set it to zero.Else, update it with the timestamp of the first job of the Slave_job_queuewhich was assigned in the Log_event::get_slave_worker() function.*/ts= rli->gaq->empty()? 0: reinterpret_cast<Slave_job_group*>(rli->gaq->header_queue())->ts;rli->reset_notified_checkpoint(cnt, ts, need_data_lock, true);三、更改last_master_timestamp值为ts变量，函数Relay_log_info::reset_notified_checkpointlast_master_timestamp= new_ts;
当执行DDL语句时
last_master_timestamp 的计算同样是这个公式：
rli->last_master_timestamp = ev->common_header->when.tv_sec + (time_t)ev->exec_time;所以MySQL在执行DML和DDL语句时，由于需要判断exec_time是否参与计算，计算Seconds_Behind_Master的方式是有区别的。
五、总结
通过以上分析，对 MySQL 复制状态中 Seconds_Behind_Master 字段的值可以简单得出如下一些结论：
对于主从库服务器系统时间不一致的情况。在 I/O 线程第一次启动时，会计算主从库之间的系统时间差，在后面计算复制延迟时，减去这个时间差，就可以保证获取的是正确的复制延迟时间。但是只有 I/O 线程启动时才会计算该时间差，所以当 I/O 线程启动之后修改了主从库的主机时间，会导致根据这个公式计算的复制延迟时间不可靠，但是当 I/O 线程重启之后就可以恢复正常（因为 I/O 线程重启时，会重新计算主从库主机之间的时间差）。
在计算复制延迟时（执行 SHOW SLAVE STATUS 语句时会进行计算），对 Seconds_Behind_Master 计算结果做一些判定：
如果从库的 I/O 和 SQL 线程同时为 Yes （表示两个线程处于运行状态），且 SQL 线程没有做任何事情（没有执行任何 event ），此时直接判定复制延迟为 0 ，不会用公式计算延迟时间，否则会用公式进行计算（所以在该前置条件下不会出现当主库没有写任何 binlog event 时，从库延迟不断加大的情况）。
如果 SQL 线程为 Yes ，而且 I/O 线程已经读取的 relay log 未应用完，则不管 I/O 线程是否正在运行，都会用公式计算延迟时间。但当 SQL 线程重放完成了所有 relay log 时，如果 I/O 线程不为 Yes ，则直接判定复制延迟结果为 NULL 。
任何时候，如果 SQL 线程不为 Yes ，直接判定复制延迟结果为 NULL。
当计算出的复制延迟为负数时，将该值直接归零。
提示：
当 SQL 线程重放大事务时，SQL 线程的时间戳更新相当于被暂停了（因为一个大事务的 event 在重放时需要很长时间才能完成，虽然这个大事务也可能会有很多 event ，但是这些 event 的时间戳可能全都相同），此时根据计算公式可以得出，无论主库是否有新的数据写入，从库复制延迟仍然会持续增大（即此时的复制延迟值是不可靠的），所以就会出现主库停止写入之后，从库复制延迟逐渐增大到某个最大值之后突然变为 0 的情况。
以上就是对 MySQL 复制状态中 Seconds_Behind_Master 字段值的简单分析，由此可以看出，在架构设计时，仅仅通过监控 Seconds_Behind_Master 的值来判断主从复制延迟并不合适，因为这个值并不能准确反映真实的延迟情况，还需要额外采用一些其他方法，比如配置一张心跳表等。也可以采取一些主动措施，比如将 MySQL 的大事务拆分成小事务处理、配置基于 WRITESET的MTS 并行复制机制等、或者在 GTID AUTO_POSITION MODE 模式下通过比较主从库的 Executed_Gtid_Set 进行判断，可以进一步有效地避免主从复制延迟。
去哪儿网 DBA 团队从不同架构的特点上考虑，结合具体的业务场景需求，最终选择了以同步复制的 PXC 为主，其它异步复制架构为辅并逐渐替换为 PXC 的整体方案，在内部总结了一系列开发和运维规范，同时也有不定期的小组分享和培训，来提升整个团队对 PXC 的运维和故障处理能力。
当然 PXC 除了同步复制，还有多点写入、自动扩缩容、自动故障切换等特点，如果你对 PXC 架构感兴趣，可以随时与我们交流，当然也欢迎志同道合之士可以加入去哪儿网 DBA 团队，共同成长进步。
参考资料：
《MySQL运维内参》 作者：周彦伟、王竹峰、强昌金
《深入理解主从原理32讲》（简书专栏） 作者：重庆八怪
《数据生态：MySQL复制技术与生产实践》 作者：罗小波 沈刚
https://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html MySQL官方文档
招募贴：