In a replication environment, we usually only judge the SLAVE delay based on the value of Seconds_Behind_Master.This is acceptable in most cases, but not accurate enough, and more factors should be considered.
First, let's take a look at the status of SLAVE:
You can see that the value of Seconds_Behind_Master is 3296, which means that SLAVE is delayed by at least 3296 seconds.
Let's take a look at the status of the two REPLICATION processes on SLAVE:
You can see that the SQL thread has been performing the UPDATE operation, and notice that the value of Time is 3293.It seems that this UPDATE operation has been executed for 3293 seconds.It is just an ordinary SQL, and it certainly does not take so long.
In fact, in the REPLICATION process, the value of the Time column may have several situations:
1.The difference between the timestamp in the binlog (actually the relay log) currently executed by the SQL thread and the latest timestamp of the IO thread, this is the Seconds_Behind_Master value that everyone usually thinks, not the actual execution time of a certain SQL;
2.If there is no active SQL currently executing in the SQL thread, the Time value is the idle time of the SQL thread;
The Time value of the IO thread is the total duration (how many seconds) of the thread since it was started.If the system time is modified after the IO thread is started, the Time value may be abnormal, such as a negative number, or very large.
Look at the following status:
Okay, finally, let's talk about how to correctly judge the delay of SLAVE:
1.First, see if there is a difference between Relay_Master_Log_File and Master_Log_File;
2.If Relay_Master_Log_File and Master_Log_File are the same, look at the difference between Exec_Master_Log_Pos and Read_Master_Log_Pos, and compare how many binlog events the SQL thread is slower than the IO thread;
3.If Relay_Master_Log_File and Master_Log_File are different, it means that the delay may be large, and it is necessary to obtain binlog status from MASTER to judge the gap between the current binlog and MASTER;
Therefore, a relatively more rigorous approach is:
On the third-party monitoring node, initiate SHOW BINARY LOGS and SHOW SLAVE STATUSG requests to MASTER and SLAVE at the same time, and finally judge the difference between the binlogs of the two, as well as the difference between Exec_Master_Log_Pos and Read_Master_Log_Pos.
The result of executing SHOW BINARY LOGS on MASTER is:
The result of executing SHOW SLAVE STATUSG on SLAVE is:
At this time, the actual delay of SLAVE should be:
mysql-bin.000009 The difference between the binlog position 1073742063 in this binlog and the binlog position read on SLAVE Differential delay, that is:
1073742063-654409041=419333022 binlog events
And also add mysql-bin.000010 this binlog has generated 107374193 binlog events, a total of 107374193 binlog events.
107374193 + 419333022=526707215 binlog events