Mysql – happening when Seconds_Behind_Master oscillates between two very different sets ov values

MySQLperconareplication

I have a Percona 5.6 slave, newly set up, that is replicating from a server that is itself a slave to a third. The middle server (dbS2, the master for dbS3) is currently ~260000 seconds behind the transaction server, and dbS3 was configured from an innobackupex snapshot of dbS2, which put it about 45 minutes behind dbS2 when dbS3 started replicating.

This is what a loop showing some parameters from show slave status\G looks like:

Fri Jul 31 23:46:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 285744

Fri Jul 31 23:46:31 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 285763

Fri Jul 31 23:47:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 2223

Fri Jul 31 23:47:31 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 2226

Fri Jul 31 23:48:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 2227

Fri Jul 31 23:48:31 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 284885

Fri Jul 31 23:49:01 ART 2015: dbS3
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 284568

So it seems that Seconds_Behind_Master on dbS3 is jumping back & forth between the number of seconds dbS2 is lagging behind dbS1 and the number of seconds dbS3 is lagging behind dbS2.

Shouldn't show slave status\G on dbS3 always show the lag between dbS2 and dbS3 without regard for how far dbS2 is behind dbS1?

Best Answer

I have seen this since about 2002. Ignore it; it will go away. I have failed to find out what causes it, and it so elusive that I could not get anyone to look at as a "bug".

I do not know if this will have any impact on innobackupex.

Related Question