Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Aug 3 '11 at 18:31

This question came from our site for professional and enthusiast programmers.

    
Is this slave located on your LAN or in a galaxy, far, far away? –  Janne Pikkarainen Aug 3 '11 at 18:42
    
it's located in my lan –  Mike Sherov Aug 4 '11 at 12:22

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer

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).

share|improve this answer
    
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 4 '11 at 12:23

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.

share|improve this answer
    
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 4 '11 at 12:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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