Friday, November 9, 2012

Sudden large increases in MySQL slave lag caused by clock drift

Just in case this ever helps anyone else, I had a machine where slave lag (as reported by Seconds_Behind_Master in SHOW SLAVE STATUS) would sometimes suddenly jump to 7 hours and then come back, and jump again, and come back.


Turns out, the machine's clock was off by 7 hours and no one had noticed!  After fixing NTP synchronization, the issue remained, I suspect that MySQL keeps a base timestamp in memory that was still off by 7 hours.

The fix was to STOP SLAVE; START SLAVE;