1

Got the following nagios alert this morning for our MySQL replica:

14:49 PROBLEM: mysql2/MySQL replication check is CRITICAL, 10.1.0.133 is 72870 seconds behind, Aug 09, 21:49 UTC

When I look at SHOW SLAVE STATUS on the replica, the places I normally look at are fine:

Slave_IO_State: Waiting for master to send event
Seconds_Behind_Master: 73698
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_SQL_Errno: 0

Where else should I be looking to diagnose and fix replication?

  • As long as Exec_Master_Log_Pos is increasing, you are processing and just behind. Is it increasing? – winmutt Aug 10 '15 at 13:12
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 for monitoring your replication status.

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

Has anything been written to the Master in the last 72K seconds (about a day)?

Sometimes this helps:

STOP SLAVE;
START SLAVE;

I have seen the need for this on and off over the last decade; never figured out why it might be needed.

Another mystery is that the Seconds_behind_master sometimes mysteriously decides to flap between zero and some large value. Eventually that problem clears itself up.

  • I'm certain something has been written. There's about 200 databases total across a variety of projects. Restarting the slave seems... unreliable. – jldugger Aug 10 '15 at 1:26
  • As far as i know the Seconds_Behind_Master are the difference between the slave current time and the event time stamp of binary log. The event time stamp could be different of commit time. So Seconds_Behind_Master is not accurate measurement of delay between master and slave. – Giovanni Aug 10 '15 at 7:47
  • In the case of a long-running query (eg, ALTER of big table), the Master's "time" is when the ALTER started on the Master. – Rick James Aug 10 '15 at 12:18

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.