Mysql Percona 5.6 slave lags gradually

Question

I have got into slave lag gradually say every minute it lags around 5secs on slave.

So it started morning 10AM the Seconds_behind_master was 13. Now at 4:30 PM it has reached to 560.

No variable changed. Not much selects are running in there, its all regular ones. In fact we have stopped few applications thinking those could cause slowness due to selects.

This seems to be strange for me. This had never happened before.

I have below variables already set to minimize IO load on disk.

> innodb_flush_log_at_trx_commit=0   
> innodb_flush_method = O_DIRECT
> sync_binlog=0

I also have 2 more slave nodes. They are performing real good. And they are used heavily by selects on app end.

Any thoughts or any one have run into similar issue before?

New Update

Something found in network.

Transferring 1 GB file to slave0
mannoj@master:~$ scp mysql-bin.001539 [email protected]:/home/mannoj/
 100% 1024MB  68.3MB/s   00:15

Transferring 1 GB file to slave1                                                                                                                               mannoj@master:~$ scp mysql-bin.001539 [email protected]:/home/mannoj/
 100% 1024MB 170.7MB/s   00:06

Transferring 1 GB file to newslave000
mannoj@master:~$ scp mysql-bin.001539 [email protected]:/home/mannoj/
 100% 1024MB 204.8MB/s   00:05

The slave lag persist on slave0.

Final Update (I guess)>

I disabled Network consuming mysqlbinlog puller runs via cron. Seems to be draining seconds_behind_master from 700 to 490.

Answer 1

I have a few suggestions

SUGGESTION #1

You should use innodb_flush_log_at_trx_commit=2 instead. Why ?

• With innodb_flush_log_at_trx_commit set to 0, a mysqld crash can lose up to 1 second of data.
• With innodb_flush_log_at_trx_commit set to 2, an OS crash can lose up to 1 second of data. A mysqld crash can still keep that last second lingering in the OS until the tables and other moving parts of InnoDB are flushed to disk PROVIDED THE OS IS STILL UP.

See my post Is it safe to use innodb_flush_log_at_trx_commit = 2

SUGGESTION #2

You should use sync_relay_log=1 instead. Why ?

MySQL Documentation on sync_relay_log says:

Setting sync_relay_log to 0 causes no synchronization to be done to disk; in this case, the server relies on the operating system to flush the relay log's contents from time to time as for any other file.

Prior to MySQL 5.6.6, 0 was the default for this variable. In MySQL 5.6. and later, the default is 10000.

A value of 1 is the safest choice because in the event of a crash you lose at most one event from the relay log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

Since you did not set sync_relay_log, relay logs would flush to disk every 10000 binary log events (the default setting). In that case, you may as well set sync_relay_log_info to 100.

If setting sync_relay_log and sync_relay_log_info produces too much disk I/O you can adjust them to your liking. It seems inappropriate to me to use 10000 as the setting.

SUGGESTION #3

On the Master, make sure sync_binlog is set to 1 if the application is low-to-medium write. If it is heavy write, use sync_binlog is set to 100. Same thing with setting sync_master_info

SUGGESTION #4

With more than one slave, it would do you some good to setup of Semisynchronous Replication. That will give you added assurance that at least one Slave is sync'd and can be promoted to Master.

Please read my Aug 05, 2011 post Is MySQL Replication Affected by a High-Latency Interconnect?. It includes instructions on how to set it up as well as how it differs from regular MySQL Replication (asynchronous).

SUGGESTION #5

If any Slave has log-bin and log-slave-updates enabled, disable them if the Slave is not a Master. Don't be surprised by this suggestion. I have a client who would do midday bulk loads and Seconds_Behind_Master increase 1 second every 2 seconds for 6 hours. Disabling the binary log made all that replication lag go away quickly.

SUGGESTION #6

For slave0, check the following

• battery on the disk controller
• network traceroute
• switches

GIVE IT A TRY !!!