Comments - Delayed Replication

One more field of interest for monitoring is Seconds_Behind_Master. When initially the slave is in sync the four fields would say the following:

MariaDB [test]> show slave status\G
         Seconds_Behind_Master: 0
                     SQL_Delay: 30
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
 

Here 30 seconds is my preference for MASTER_DELAY.

Once the master has updated some data

MariaDB-master [test]> insert into t set a=1;
Query OK, 1 row affected (0.01 sec)

and it is replicated the fields show is progressed maintaining

Seconds_Behind_Master + SQL_Remaining_Delay == SQL_Delay

property:

MariaDB-slave [test]> show slave status\G
         Seconds_Behind_Master: 1
                     SQL_Delay: 30
           SQL_Remaining_Delay: 29
       Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
...
MariaDB-slave [test]> show slave status\G
         Seconds_Behind_Master: 28
                     SQL_Delay: 30
           SQL_Remaining_Delay: 2
       Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
1 row in set (0.01 sec)

MariaDB-slave [test]> show slave status\G
         Seconds_Behind_Master: 0
                     SQL_Delay: 30
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Note the above formula is correct only to the serial "legacy" slave.

In the parallel case the relation between the above parameters can be seen from the following illustration.
Let the binlog contains two adjacent events

  #240329 7:00:00 server id 2 end_log_pos 6657072 CRC32 0xf0da1817   Xid = 23868012
  COMMIT/*!*/;
  #240329 8:00:00 server id 2 end_log_pos 6657114 CRC32 0x1e4b110b   GTID 1-2-47672 trans

Let the parallel slave is set up with

                     SQL_Delay: 60

Suppose they arrive at slave about the time, the 1st is executed to commit its transaction and by that update Seconds_Behind_Master's "base". Since this point

   Seconds_Behind_Master = clock_time_on_slave - clock_when_ending_execution_of_the_event_on_master

when the timezone difference is implied to be zero. Now the 2nd transaction starts, and say at 8:00:20 it would be still hanging in waiting, Show-Slave-Status reports

         Seconds_Behind_Master: 3600+20
           SQL_Remaining_Delay: 40

The first line value may surprise yet it says the last committed transaction was that many seconds ago. The 2nd transaction will start in 40 seconds and only when it ends Seconds_Behind_Master's "base" will update again. That means all execution time of the second transaction Seconds_Behind_Master would be growing over 3600 + 60.