How to Solve Replication lag? quick solution

This topic is not for all situations but in my situation it helps. The problem is with Slave is lagging behind master in very large numbers and the relay logs are continously growing.

Slave is using: Relay_Log_File: mysql-relay-bin.000031.
But in folder where relay log resides, was created up to: mysql-relay-bin.000135 And it is continously growing.
So what i decide to do, again step-by-step i edited my.cnf file as follows:

1. I have 13 database so give to slave paralel workers 13: slave-parallel-workers = 13

2. Disabled sync_binlog: sync_binlog = 0

3. Changed at_trx_commit from 1 to 0: innodb_flush_log_at_trx_commit = 0

4. Gave log_at_timeout (only from 5.6.6>) to 10: innodb_flush_log_at_timeout = 10

5. Disabled slow query log( or commented out)

So Slave lag was somewhat: Seconds_Behind_Master: 250350 And it was continously increasing but now it is dicreasing: Seconds_Behind_Master: 178860

Again be carefull with this options. And After Slave catchs up Master undo these changes.

Author: Shahriyar Rzayev

Azerbaijan MySQL User Group leader.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s