seconds_behind_master.txt (848B)
In a heavily-loaded MariaDB replication setup, you notice that Seconds_Behind_Master is fluctuating wildly even though your pt-heartbeat shows consistent lag. What could cause this discrepancy, and which tool is more trustworthy?
Seconds_Behind_Master -> shows how "old" the event currently being executed by the SQL thread is, compared to the master's event timestamp.
long time_diff= ((long)(time(0) - mi->rli->last_master_timestamp) - mi->clock_diff_with_master);
If the SQL thread stalls or catches up in bursts (due to I/O lag, locking, or massive transactions), Seconds_Behind_Master can jump around dramatically.
pt-heartbeat can give a more accurate measurement, because it inserts current timestamps into a small table, therefore it is measuring "how far back in real time the replica is", and not "how old the binlog event is."