0

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?

1

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.

  • Thanks, Rick. I just wanted to understand how in the world seconds_behind_master is computed, since this jump indicates it's (sometimes) not based on the difference between the last executed transaction on each server. The fact that it's been like this for some time implies that it's not a function of multi-threaded replication & GTIDs, which makes it even more mysterious. In any case - good to know it's not a potential problem. Thank you. – Eljuan Aug 2 '15 at 10:48
1

Seconds_Behind_Master is the difference between the timestamps a statement was most recently downloaded by the IO_THREAD and when it was most recently executed.

I'd highly recommend pt-heartbeat.

pt-heartbeat is a two-part MySQL and PostgreSQL replication delay monitoring system that measures delay by looking at actual replicated data. This avoids reliance on the replication mechanism itself, which is unreliable. (For example, SHOW SLAVE STATUS on MySQL).

Source: https://www.percona.com/doc/percona-toolkit/2.2/pt-heartbeat.html

1

What is happening is the relay_log contains statements from it's master - some of those statements were created on that master however some were originally run on your masters master. None of this is a problem but it's disconcerting to see the jumping - why?

You must understand what "Seconds_Behind_Master" really means. It means that the statement that is running now but the slave SQL_THREAD completed on the master that many seconds ago. So if you have a chain of master: master A --> master B --> slave and you allow writes to master B slave will see statements originally run on master A and on master B but the timestamp of when that statement was originally run is never rewritten.

So if there is some noticeable lag on master B (lets say 100 seconds) and the slave is another 100 seconds behind master B on the slave you will see Seconds_behind_master jump between 100 and 200 seconds depending on where this statement was first run.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.