Two years ago wrote an article explaining the meaning of Seconds_behind_master representative and why it is not accurate, today colleague Gao teacher raised an interesting question: Seconds_behind_master in the end how to calculate it? High teacher also specially to turn over a bit of source to explain, I found that I understand before or discrepancies, so I have to turn over the source code, the following is a more comprehensive explanation of how it is calculated, why not completely credible.
I usually read MySQL source less, in general through the source code is also check some basic issues, for me if the key code location is not familiar, the more efficient way is the CD to the root of the source directory, and then grep "Seconds_behind_master". -r-n. The results are as follows:
./sql/rpl_rli.cc:1209:seconds_behind_master-not critical).
./sql/slave.cc:1345: "Do not trust column seconds_behind_master of SHOW"
./sql/slave.cc:1874:field_list.push_back (New Item_return_int ("Seconds_behind_master", 10,
./sql/slave.cc:1963:seconds_behind_master:if SQL thread is running and I/O thread is
./sql/slave.cc:3254:alive and connected, this was going to make Seconds_behind_master be 0
./sql/slave.cc:3258:seconds_behind_master grows. No big deal.
./sql/slave.cc:4666:we say in Seconds_behind_master, We have the "caught up". Note that
./sql/slave.cc:4679:seconds_behind_master would be zero if Master has no
From the search results, the main design to sql/slave.cc and sql/rpl_rli.cc, and just these two files can already solve our doubts.
First question, how exactly does seconds_behind_master calculate?
Let's first look at the explanations in the manual:
This field is a indication of how ' late ' the Slave is:
When the slave are actively processing updates, this field shows the difference between the current timestamp on the slave and the original timestamp logged on the master for the event currently being processed on the slave.
When no event was currently being processed on the slave, this value is 0.
The main idea is that if slave is processing the update, then SBM computes the current timestamp-the timestamp that is included with the updated Binlog event. If slave does not process the update, then sbm=0
So that's the answer, right? The answer is basically right, but it doesn't explain the details. Then we find this real formula in slave.cc:
1962 /*1963 seconds_behind_master:if SQL thread is running and I/O thread is1964 connected, we can compute it Otherwise show NULL (i.e. unknown). 1965 */1966 if (mi->slave_running = = mysql_slave_run_connect) & &1967 mi->rli.slave_running) 1968 {1969 long time_diff= ((Long) (Time (0)-Mi->rli.last_master _timestamp) 1970 -mi->clock_diff_with_master); Many comments are omitted here 1991 Protocol->store ((longlong) (Mi->rli.last_master_timestamp? 1992 Max (0, Time_diff): 0) ); 1993 }1994 else1995 {1996 protocol->store_null (); 1997 }
The key code is as follows:
1. Long time_diff= ((Long) (Time (0)-Mi->rli.last_master_timestamp) -Mi->clock_diff_with_master); 2. Protocol->store (Longlong) (Mi->rli.last_master_timestamp?
Here we finally see the legendary SBM calculation, which explains the meanings of each variable:
Time (0)//from library current system timestamp, Linux system function
Mi->rli.last_master_timestamp//Current from library executing statement Binlog event timestamp
Mi->clock_diff_with_master//master-slave system timestamp difference, Slave-master
Here we know the manual is actually inaccurate, but also a part of the Clock_diff_with_master, because the time stamp of the Binlog event is recorded on the main library and the local timestamp from the library time (0) is called the system's temporal function, At this point, if the master-slave time settings are inconsistent, then this value is not completely meaningless? Therefore, in order to avoid this situation, each time the connection from the library and the main library will get the master-slave timestamp, and then calculate a difference as a constant to save, each time you calculate SBM will subtract this value. But there is a problem, MySQL only in the establishment of the master-slave replication connection when the value, and will not be updated, so if the master-slave connection is established to change the master-slave time settings, such as NTPD, such as set timestamp=xxx operations, Then the value of SBM will be more unreliable at this point.
Under normal circumstances the first sentence is already our show slave status see the SBM value, then the second sentence is to solve what problem?
1. Clock_diff_with_master is the difference between master and slave select Unix_timestamp (), it is possible that the main library executes at 1 and 2 (because it is not guaranteed to execute at the same time), then clock_diff_ With_master=1, then assume that the value of time (0)-last_master_timestamp is equal to 0, then 0-1=-1, and 1 will give the user ambiguity, so the official in this case will force the negative value into 0
2. The above said that the value of SBM in two cases, from the library has SQL thread in the processing of statements when the calculation method just explained in detail, from the library SQL thread does not handle the statement is to set this value to 0, then the second sentence code to achieve this function, the second sentence of the ternary expression tells us, when the Mi->rli.last When the _master_timestamp value is 0, sbm=0. So what is the update logic for the Mi->rli.last_master_timestamp value?
There are two main places:
Firstly, Rpl_rli.cc/relay_log_info::stmt_done
Each time from relay log parse out a Binlog event execution, last_master_timestamp= Event_creation_time;
Secondly, slave.cc/static log_event* next_event (relay_log_info* rli)
the prerequisite for entering the logic below is that the relay log has been executed and the SQL thread is waiting for relay log to be updated
4686 time_t save_timestamp= rli->last_master_timestamp;//First save the last Binlog event timestamp. And so in the future the main library and push Binlog come over the first time to calculate SBM can be used, which is why sometimes start slave after the first show slave status see SBM value is very large one reason 4687 rli->last _master_timestamp= 0; Set the value to 0, then the first ternary expression calculation of SBM is 0 ... Omit part of code 4779 Rli->relay_log.wait_for_update_relay_log (RLI->SQL_THD);//wait for relay log to have update 4780 // Re-acquire Data Lock Since we released it earlier4781 Mysql_mutex_lock (&rli->data_lock); 4782 rli-> Last_master_timestamp= Save_timestamp; Reset back to original value 4783 continue;
and then the second big question: is seconds_behind_master reliable? Does the value of 0 indicate that the master-slave data is fully consistent?
The answer must be negative.
1. SBM is the delay of the event in relay log, and MySQL default replication is asynchronous, so it may be done from the library relay log, but Master and slave because of the network and this reason the Binlog on the main library is not pushed over, And we actually encountered on the line because of network reasons sbm=0, but the main library of Binlog is not pushed over, generally at this time through the Stop Slave/start slave can find this illusion. So usually we go to monitor the master-slave delay will not be directly with SBM to judge the standard, but the master-slave to establish a heartbeat table, to the main library to insert a data to determine the delay situation.
2. If the master-slave time is not synchronized, it may cause the SBM delay value to be inaccurate
3.5.6 If multi-threaded replication is turned on, this value is even more inaccurate. I'm curious to know if you're on line 5.6. How does MTS monitor the master-slave delay?
--eof--
Another discussion on Seconds_behind_master
