MySQL Slaves lag behind master

Question

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?

Answer 1

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

Answer 2

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.