Mysql slave State of Seconds_behind_master

In MySQL's master-slave environment, we can view some of slave's state information by executing the show slave status on Slave, which has a more important parameter seconds_behind_master. So do you understand what it really means and how it's calculated?

In the past I have been mistaken for seconds_behind_master to indicate how much slave is behind the master, if the value of 0 indicates that the master and slave are already in the same (in the non-synchronous mode, now the official up to only 5.5 in the semi-synchronous replication). But recently I finally realized the wrong understanding. The first thing we need to understand is that seconds_behind_master represents the delay between SQL thread and IO thread on slave, and we all know that in MySQL's replication environment, Slave first pulls Binlog from master to local (via IO thread) and then replays the Binlog through SQL thread, while Seconds_behind_master represents the difference in the portion of the local relaylog that has not been executed. Definitions on the manual:

In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

So if slave pulled to the local relaylog (in fact, binlog, just slave used to call Relaylog) are executed, at this time through show slave status will be 0, then Seconds_behind_ Does the value of master 0 indicate that the master and slave are already in the same order? The answer is almost negative! Why is it almost negative? Because replication is asynchronous in most cases, asynchronous means that the Binlog on master is not sent to slave on the fly, so even if the value of Seconds_behind_master is 0, it is not certain that the master-slave is consistent. This is also my previous emphasis on the reasons for non-synchronous replication (now there are companies doing synchronous replication, such as NetEase own implementation of the Vsr,virtualsynchronized Replication, due to the poor synchronous replication performance, so netease to achieve synchronous replication also hit the group Commit patch). So if we're going to use this parameter to estimate how long the master-slave delay will be at least in a better network environment, this will ensure that almost the binlog on the master have been sent to slave.

The above explains the true meaning of the value of Seconds_behind_master, so how is its value calculated? In fact, each binlog events in Binlog is attached to the timestamp at execution time, so when determining the value of Seconds_behind_master, MySQL is comparing the current system's timestamp with the current SQL Thread is performing a comparison of the timestamp on the Binlog event, which is the value of Seconds_behind_master. Maybe you have questions. What if the clock is inconsistent between the two servers? This is true, and this value is less reliable at this point, which is also illustrated in the manual:

This time difference computation works even if the master and slave does not has identical clock times, provided that the D Ifference,
Computed when the slave I/O thread starts, remains constant from and on. Any changes-including NTP Updates-can leads to clock
Skews that can make calculation of seconds_behind_master less reliable

The value of Seconds_behind_master, in addition to being non-negative, may also be null, which is caused by the following: SQL thread is not running/io thread is not running/slave is not connected to master.

Let's take a brief look at the differences between asynchronous replication/semi-synchronous replication.

Asynchronous replication, the operation on master records Binlog while not caring whether Binlog has been received by slave.

Semi-synchronous replication, the operation on master records Binlog while caring whether Binlog is slave accepted. However, because of its handling logic, it is possible to throw a transaction, as shown in:

There is a problem with such a process, when the storage engine commits (storage commit), at this point if master is hung up then there will be a master never consistent, for this problem Orczhou as if they have made changes to the source code storage Commit in order to achieve an enhanced semi-synchronous replication.

To Seconds_behind_master here, I hope you can help a little.

Mysql slave State of Seconds_behind_master