MySQL slave State of Seconds_behind_master

In MySQL's master-slave environment, we were able to view some of slave's state information by running show slave status on Slave, which has a more significant number of seconds_behind_master. So do you know 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, assuming that the value of 0 indicates that the master and slave are already in the same (in the non-synchronous mode, the official is now only added in the 5.5 only in the half-synchronous copy). But in the near future I finally realized the wrong understanding. First we need to be clear: 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 is not run out. The definition 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 assuming that slave pulled to the local relaylog (in fact Binlog, just slave on the habit of calling Relaylog) are all running, at this time through show slave status to see is 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, async 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 still not certain that the master/slave is consistent. This is the reason I emphasized the non-synchronous replication before (now there are companies doing synchronous replication, for example, NetEase itself implemented the vsr,virtualSynchronized Replication, due to the poor synchronous replication performance, So netease to achieve synchronous replication at the same time also hit the group Commit patch). So suppose we're going to use this number to predict how long the master-slave delay will be at least in a better network environment, so that the ability to ensure that almost master Binlog has 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, every Binlog events in the Binlog is attached to the runtime timestamp, so in determining the value of Seconds_behind_master, MySQL is passing the current system timestamp with the current SQL Thread is running a time stamp on the Binlog event, and this difference is the value of Seconds_behind_master. Maybe you have a question. What if the clock is inconsistent between the two servers? There is indeed such a situation, then this value is not reliable at this time, the manual on this also explained:

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 such things as the following: SQL thread does not perform/io thread does not perform/slave is not connected to master.

Next, let's take a look at the differences between asynchronous replication/semi-synchronous replication.

Asynchronous replication, the operation on Master records Binlog at the same time does not care whether Binlog has been received by slave.

Semi-synchronous replication, the operation record on Master Binlog at the same time will care whether Binlog is slave accepted. However, because of its handling logic, it may throw a transaction, for example, as seen in the following:

There is a problem with this processing process, and when the storage engine commits (storage commit), it is assumed that master is not consistent at this point, and for this problem Orczhou it seems that it has 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.