1

I have a MySQL slave that when it is 0 seconds behind the master, it correctly reports 0 Seconds_Behind_Master. However, if it gets even 1 seconds behind, it reports 14401 Seconds_Behind_Master (which is 4 hours and 1 second behind).

The unix date command produces the same time on both the master and the slave, and both machines are synced with ntp.

Issuing a SELECT NOW() on both machines produces the same time. Also, the time zones are the same on both:

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CDT    | 
| time_zone        | SYSTEM | 
+------------------+--------+
2 rows in set (0.00 sec)

This isn't a huge problem for me, but it makes my nagios graphs look weird, and prevents me from having sane monitoring on that machine to monitor slave lag. Anyone know why the slave would think it's 4 more hours behind than it really is?

Mike Sherov
  • 113
  • 1
  • 7

3 Answers3

2

The time difference between master and slave is computed when I/O thread starts and it assumed to never change while it runs. It you had a different timezone on one of the servers and it changed without restarting MySQL, then slave is adding the incorrect difference to the report. Restart servers.

Andrei
  • 36
  • 2
1

Seconds_Behind_Master is considered an unreliable measure of Slave lag. mk-heartbeat is the suggested solution. Check out this SF post (by yours truly).

Community
  • 1
HTTP500
  • 4,827
  • 4
  • 22
  • 31
  • Jason, I appreciate the alternative suggestion, and will definitely look into it. However, I'd really like a solution rather a workaround. Thanks again. – Mike Sherov Aug 04 '11 at 12:23
0

There might be a better answer, but in my experience, any lag time > 0 is pretty much just a wild estimate, and should be treated with suspicion. I've had lag times of 11 to 15 years. Also, afaik, slave lag has nothing to do with timezone information; it's a function of the cursor position in the binary log.

MrTuttle
  • 1,166
  • 5
  • 5
  • It is not usually a wild estimate for me. We run servers that are really heavy on writes and so it's normal for us sometimes to see slave lag. However, in this case, it seems like there is simply something misconfigured with timing. – Mike Sherov Aug 04 '11 at 12:22