Take the 2-minute tour ×
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.

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.

Thanks.

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

2 Answers 2

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:

  • STOP SLAVE;
  • SHOW SLAVE STATUS\G
  • 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;
  • START SLAVE;

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

hmm .. You can try out MONyog which has 'Replication tab' which will show details about all slaves in one place along with Master detail. You can sort on 'seconds_behind_master' column as well. Also it has 'Server config' feature using it you can compare diffrence between 'stable' and 'unstable' set of slaves.

share|improve this answer

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.