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 one master and four slaves. Sometimes all my slaves lag behind the master. I have implemented the heartbeat for monitoring replication lag. Now I am trying to find why the slaves are lagging behind the master.

I saw the slow queries (for today) on the master and I found that the slowest query (DML) was taking 138 seconds. But the slaves were lagging about 1400 seconds and there were also no slow queries on the slaves for DML (update, delete, insert, etc.).

Points to be taken into consideration:

  1. All tables are InnoDB.
  2. 68 GB of RAM (Master as well as slaves).
  3. Data size about 1 TB.
  4. Master and slave are running from a long.

What may be the reason for lag?

share|improve this question
    
any application changes recently? check with your development team. –  Mannoj Mar 18 '13 at 10:22
    
There are no changes. –  Abdul Manaf Mar 18 '13 at 10:22
    
Does your DML on master takes such a long time usually? If not is your Master Server health is good. Check load, IO importantly are they normal do you notice any spike in your master server. Any other operation being performed. Post the slowest query that is taking 138 secs with its table definition. –  Mannoj Mar 18 '13 at 10:26
add comment

1 Answer

The Problem

This problem normally happens because on master, you allow a poll of connections(threads) which run commands at same time (max_connections), but on the slave (on versions before 5.6) you have just 1 thread executing commands from binlog, lets say you have a big table, and you run an optimize or alter table and this command took 3 minutes on master, after execute it on master, it will be logged on binary log and relay to slave, when the slave pick this command from his log, it will start to run this command and any other query from master will need to wait until this command.

Solution 1

when you start to see the seconds behind master increase, try to look in the SHOW PROCESSLIST on the slave serve to see which query is been executed at that time

Solution 2

when you start to see the seconds behind master increase, try to look in the SHOW SLAVE STATUS which position of the binary log is been executed, and use the mysqlbinlog to find the command.

Solution 3

Update to MySQL 5.6 and start to make use of multi-threaded slave a good start is to read this article: http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html

share|improve this answer
add comment

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.