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

2 Answers 2

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

What binary log format are you using ? Are you using ROW or STATEMENT ?
"SHOW GLOBAL VARIABLES LIKE 'binlog_format';"

If you are using ROW as a binlog format make sure that all your tables has Primary or Unique Key:
SELECT t.table_schema,t.table_name,engine FROM information_schema.tables t INNER JOIN information_schema .columns c on t.table_schema=c.table_schema and t.table_name=c.table_name and t.table_schema not in ('performance_schema','information_schema','mysql') GROUP BY t.table_schema,t.table_name HAVING sum(if(column_key in ('PRI','UNI'), 1,0)) =0;

If you execute e.g. one delete statement on the master to delete 1 million records on a table without a PK or unique key then only one full table scan will take place on the master's side, which is not the case on the slave.

When ROW binlog_format is being used, MySQL writes the rows changes to the binary logs (not as a statement like STATEMENT binlog_format) and that change will be applied on the slave's side row by row, which means a 1 million full table scan will take place on the slave's to reflect only one delete statement on the master and that is causing slave lagging problem.

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.