3

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?

5

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:

mk-slave-prefetch

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.