Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.
Sign up to join this community
I have a slave that got an error about a months or so ago and got way behind the master. I fixed the error and now playing catchup with the master but its going very slowly. Its going at 1.3x real time. I was using less that 10% of the db resources when these writes were first happening so the speed of the server shouldn't be an issue.
Is there any settings I can switch to help the slave catch up with the master?
MySQL uses only one thread on the slave to apply the replicated statements coming from the master.
In the master server, SQL statements are executed in a multi-threaded concurrent way, but they get serialized when appplied in the slave. That's why your slave server may seem to be underused while, painfully, tries to catch up.
If your server is lagging way too far behind the master one option is restoring the most recent backup of the master database on the slave server and reconfigure replication from that point on.
I recommend you to take a look a this site for more detailed info on replication lag. http://www.mysqlperformanceblog.com/2007/10/12/managing-slave-lag-with-mysql-replication/
There is also a tool in Maatkit toolset that you may consider:
This tool implements Paul Tuckfield’s famous “oracle” algorithm to read ahead of the slave SQL thread in the relay logs, rewriting queries as SELECT and executing them to warm the slave’s caches. This can help an I/O-bound slave SQL thread run faster under some conditions, because it doesn’t have to wait for as much I/O to complete.
Just my 2c.
Good luck.
Fran.
