2

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.

com
  • 616
  • 1
  • 7
  • 16

1 Answers1

1

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 !!!

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536