Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have few mysql servers in replication master-salves in cloud service.

The problem is even thought all configuration, mysql versions, server's resources are the same, still I have inconsistency in slave's behavior.

For example, I have a set of stable mysql slaves which work perfectly, and a set of unstable slaves which still have seconds behind master even in idle mode, without traffic, and the set of unstable is always the same servers.

I am looking for something that is different in stable and unstable slaves. So far I didn't find something like this. Please, if you know how to find the thing that different share this with us.


share|improve this question
How high is Seconds_Behind_Master on any given unstable server? – RolandoMySQLDBA Apr 4 '12 at 14:52
Seconds_Behind_Master is around 20-50 all the time – com Apr 4 '12 at 18:28
Are the OS clocks sync'd between master and slave ??? – RolandoMySQLDBA Apr 4 '12 at 18:40
There was a difference of 3 sec, now there are completely synced, still I have spikes on slave – com Apr 4 '12 at 18:51
Have you ever erased the relay logs on the Slave before ??? – RolandoMySQLDBA Apr 4 '12 at 18:52
up vote 1 down vote accepted

If all clocks are sync'd, I would suspect high write load on the Master. Why?

The Master is responsible for doing two things:

  1. Executing the SQL
  2. Recording the SQL and its timestamp

If there is any intermittency between steps 1 and 2, this could account for what seems to be replication lag.

Since there are stable servers, I suggest resetting the relay logs on the unstable servers by doing the following:

  • Get LOGFIL from Relay_Master_Log_File
  • Get LOGPOS from Exec_Master_Log_Pos
  • CHANGE MASTER TO master_log_file='LOGFIL',master_log_pos=LOGPOS;

The CHANGE MASTER TO command erases the relay logs it currently has and starts fresh.

Give it a Try, and see if this clears up the replication lag !!!

share|improve this answer
Thanks, I've done it, also I'll take a look at network. Thanks again – com Apr 4 '12 at 19:08

Your Answer


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.