How to avoid lagging when enabling log-slave-updates?

Question

Master:

• 48GB RAM
• 16-core
• my.cnf: https://clbin.com/hlkUo

Slave:

• 64GB RAM
• 24-core
• my.cnf: https://clbin.com/viLm0

Both are running 5.5.28.

For the incremental backup purpose, I need to enable the log-slave-updates option. But Seconds_Behind_Master keeps increasing immediately after restarting MySQL. A simple statement get... 2 minutes to updated on the slave at spare time. At the busy time, it seems that the Slave cannot catch up with the Master, so I have to comment it out and restart MySQL.

Is there any way to improve this?

Answer 1

As your main concern is to have an incremental backup solution, you can change the follow variables:

Changes:

innodb_max_dirty_pages_pct - set it to 75, then innoDB will cache some changes and flush it to disk at once.

innodb_doublewrite - Disable the innodb double write

sync_binlog - Disable syn binlog

long_query_time - increase the long query time or disable it (at the moment you are logging every single query)

Why slave delays?

Other think is, it looks to be a busy master, on master you are able to handle 768 connections at the same time, it means that 768 thread can be writing data to MySQL, and the server (if not having looking issues) will execute all these queries at same time. In the other end(slave), you have a single replication thread to execute all queries received from master, and if a query take 10 seconds to execute on slave, the only thread available to execute changes from master will be busy, in this case, some delay are expected. In your case you also use the slave as a read only copy of the master, then the replication thread may also need to wait some other thread release lock on some rows to apply the changes.

Answer 2

You can try to comment out these two variables on your slave to use default values - they may cause high disk I/O:

max_binlog_size
sync_binlog