# MySQL-Seconds_behind_master的精度误差

05/13 15:20

## 前言

Seconds_behind_master是我们观察主从延迟的一个重要指标。但任何指标所能表示的精度都是有限的。例如用精度只能到秒的指标去衡量毫秒级的表现就会产生非常大的误差。如果再以此误差去分析问题，就会让思维走上弯路。例如用Seconds_behind_master去评估1s内的主从延迟就是一个典型的例子。

## Seconds_behind_master什么时候计算出来为1

long time_diff= ((long)(time(0) - mi->rli->last_master_timestamp)
- mi->clock_diff_with_master);


### 什么时候计算clock_diff_with_master

handle_slave_io
/* 建立主从连接 */
|->safe_connect(thd, mysql, mi))
/* connected: 主从连接成功后，计算一下主从clock_diff_with_master */
|->get_master_version_and_clock


### 实际主从延迟为0的情况下clock_diff_with_master计算为0，Seconds_behind_master计算为-1并被校正为0

MySQL中的源码注释和强行校正逻辑如下所示:

      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 possible
reasons 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, and
that 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) when
the slave's timestamp is read it is at the very beginning of second
2. Then the recorded value for master is 1 and the recorded value for
slave is 2. At SHOW SLAVE STATUS time, assume that the difference
between 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 a
special marker to say "consider we have caught up".
*/
protocol->store((longlong)(mi->rli->last_master_timestamp ?
max(0L, time_diff) : 0));


0
1 收藏

0 评论
1 收藏
0