Seconds_Behind_Master in MySQL slave status

In the MySQL master-slave environment, we can run show slave status on slave to view some slave status information. Among them, there is an important parameter Seconds_Behind_Master. Do you understand the true meaning of it and how it is calculated?
Previously, I mistakenly thought that Seconds_Behind_Master indicates the number of slave lags behind the master node. If this value is 0, the master and slave nodes are already in the same State (in non-synchronous mode, currently, the official website only adds semi-synchronous replication in 5.5 at most ). But recently I finally realized my previous misunderstanding. First, we need to understand that Seconds_Behind_Master indicates the delay between SQL thread and IO thread on slave. We all know that in the MySQL replication environment, slave first pulls the binlog from the master to the Local Machine (through IO thread), and then replays the binlog through SQL thread. Seconds_Behind_Master indicates the difference between the unexecuted part of the local relaylog.
Www.2cto.com
Definition In the manual: in essence, this field measures the time difference In seconds between the slave SQL thread and the slave I/O thread. therefore, if slave pulls the local relaylog (in fact, it is the binlog, but it is used to calling relaylog on slave), it will be executed. At this time, it will be 0 through show slave status, so is the value of Seconds_Behind_Master 0 indicating that the master and slave are already consistent? The answer is almost no! Why is it almost no? In most cases, replication is asynchronous. asynchronous means that the binlog on the master node is not sent to the slave in real time. Even if the value of Seconds_Behind_Master is 0, the master and slave nodes are not necessarily consistent, this is also the reason why I have previously stressed non-synchronous Replication (some companies are already doing synchronous Replication. For example, Netease has implemented VSR and VirtualSynchronized Replication. Due to the poor synchronization Replication performance, therefore, Netease implements synchronous replication and patches group commit ). Therefore, if we want to use this parameter to estimate the delay between the master and slave nodes, we must at least in a good network environment to ensure that almost all the binlogs on the master node have been sent to the slave.
Www.2cto.com explained the true meaning of the value Seconds_Behind_Master. How is the value calculated? In fact, in binlog, each binlog events will include the timestamp at the time of execution, therefore, when determining the value of Seconds_Behind_Master, MySQL compares the timestamp of the current system with the timestamp of the binlog event being executed by the current SQL thread. The difference is the value of Seconds_Behind_Master. Maybe you have doubts. What if the clock between the two servers is inconsistent? This situation does exist, so the reliability of this value is not big at this time. This is also described in the Manual: this time difference computation works even if the master and slave do not have identical clock times, provided that the difference,
Computed when the slave I/O thread starts, remains constant from then on. Any changes-including NTP updates-can lead to clock
Skews that can make calculation of Seconds_Behind_Master less reliable Seconds_Behind_Master may be NULL in addition to non-negative values. It is caused by the following situations: SQL thread is not running/IO thread is not running/slave is not connected to master. Next, I will briefly introduce the differences between asynchronous replication and semi-synchronous replication. Asynchronous replication: The binlog Operation Records on the master node do not care whether the binlog has been received by slave. Semi-synchronous replication: The binlog of the Operation Records on the master node is also concerned about whether the binlog is accepted by slave. However, because of its processing logic problems, a transaction may be lost, as shown in: This processing process has a problem. After the storage engine commits (storage commit, at this time, if the master node fails, the master node will be inconsistent. For this problem, orczhou seems to have modified the source code and changed the storage commit order to achieve an enhanced semi-synchronous replication. This is an explanation of Seconds_Behind_Master. I hope it will be helpful to you too.