Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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