Seconds_behind_master: confusions and explanations

1. I used to blur this parameter all the time. Today I am free to look at the source code file.SQL/rpl_slave.cc (MySQL 5.6.16)

If (mi-> RLi-> slave_running)

{
/* Check if SQL thread is at the end of relay log
Checking shoshould be done using two conditions
Condition1: Compare the log positions and
Condition2: 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) # Io SQL statements are all in the runing state and time_diff = 0
Protocol-> store (0ll );
Else
Protocol-> store_null ();
}
Else
{
Long time_diff = (long) (time (0)-mi-> RLi-> last_master_timestamp) # Formula
-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
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
Special marker to say "consider we have caught up ".
*/
Protocol-> store (Longlong) (mi-> RLi-> last_master_timestamp?
Max (0l, time_diff): 0); # diff_tiime <0 is treated as 0.
}
}
Else
{
Protocol-> store_null ();
}

Summary: From the source code, we can see that seconds_behind_master = Current slave system time minus the timestamp of the BINLOG recently received by SQL _thread, and then the system time difference between the master and slave. The value is <0, and the value is 0.

We can also see from the above that (long) (time (0)-mi-> RLi-> last_master_timestamp) # Calculation Formula

-Mi-> clock_diff_with_master );

It can be analyzed that the latency has nothing to do with the time zone of the master and slave.. M + 7 S + 8 m execute diff_time = (11-10)-(11-10) = 0 at 10, as long as S has no latency.

I'm curious, even if a negative number can occur, but all of them have been processed0.

* Negative values:

1. the system time of the man-made set master is less than the slave

2. A ---> B --> C: calculate the time of the C-delay BINLOG, which is the time of A. the time value of a node other than the master node is added to the calculation, and a negative value may occur.

This article is from my DBA life blog, please be sure to keep this source http://huanghualiang.blog.51cto.com/6782683/1557135

Seconds_behind_master: confusions and explanations