Mysql – How to avoid lagging when enabling log-slave-updates

linuxMySQLmysql-5.5replication

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?

Best Answer

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.

Related Solutions

MySQL – Master/Slave Replication with different server specs

Yes you can upgrade the Master's my.cnf file to handle the new environment.

I have an additional suggestion.

InnoDB prior to MySQL 5.1.38 cannot use multiple CPUs.

You can do one of two things that will not affect the Slave

Upgrade to Percona Server 5.1
MySQL 5.1.38 has InnoDB Plugin (dsiabled by default) which acceses mulitple CPUs
- If you have MySQL 5.1.37 or prior, upgrade to the latest MySQL 5.1 and install InnoDB Plugin
- If you have MySQL 5.1.38+, install InnoDB Plugin

Once you do, one of the above, you can configure the necessary options for accessing multiple CPUs

MySQL – Installing InnoDB Plugin

Goto to the following folder

cd /usr/lib64/mysql/plugin
ls -l

If you see something like this

-rw-r--r-- 1 root root 11827298 Sep 13  2010 ha_innodb_plugin.a
-rwxr-xr-x 1 root root      962 Sep 13  2010 ha_innodb_plugin.la
lrwxrwxrwx 1 root root       25 Jun 23  2011 ha_innodb_plugin.so -> ha_innodb_plugin.so.0.0.0
lrwxrwxrwx 1 root root       25 Jun 23  2011 ha_innodb_plugin.so.0 -> ha_innodb_plugin.so.0.0.0
-rwxr-xr-x 1 root root  1204496 Sep 13  2010 ha_innodb_plugin.so.0.0.0

You have located the InnoDB Plugin files

Getting back to your question :

1.Is it possible to install the InnoDB plugin just on the master and not on the slave?

Yes

2.Are there any other implications to installing the plugin?

Once the plugin is installed and you configure my.cnf for the Master, do not use the options for InnoDB Plugin on another server where the plugin was not installed.

3.What parameters do I need to change in my.cnf to better utilize the plugin and the multi core options ?

Please see my past posts on this

4.Any recommended tutorial for installing the plugin?

Please follow the instructions from the MySQL Documentation on InnoDB Plugin Installation