The Ultimate Guide to High Availability with MariaDB

Comments - Delayed Replication

8 months, 2 weeks ago Andrei Elkin
Re: 4. Seconds behind master for the parallel slave

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.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.