Speed up your MySQL replication slaves
Paul Tuckfield of YouTube has spoken about how he sped up his slaves by pre-fetching the slave’s relay logs. I wrote an implementation of this, tried it on my workload, and it didn’t speed them up. (I didn’t expect it to; I don’t have the right workload). I had a few email exchanges with Paul and some other experts on the topic and we agreed my workload isn’t going to benefit from the pre-fetching.
In the meantime, I’ve got a pretty sophisticated implementation of Paul’s idea just sitting around, unused. I haven’t released it for the same reasons Paul didn’t release his: I’m afraid it might do more harm than good.
However, if you’d like the code, send me an email at [baron at this domain] and I’ll share the code with you. In return, I would like you to tell me about your hardware and your workload, and to do at least some rudimentary benchmarks to show whether it works or not on your workload. If I find that this is beneficial for some people, I may go ahead and release the code as part of Maatkit.
Update: it’s part of Maatkit now.
Xarpb,
What you (and others) do for the mysql community is outstanding and very much appreciated. Keep up the good work.
Sean
Sean
13 Jan 08 at 1:06 pm
I would not expect prefetch to speed up a slave if the slave and master are on the same highspeed network. The problem we see is the single thread execution of the SQL statements. Once mysql addresses this bottleneck, slave replication will fly.
Dale
13 Jan 08 at 1:12 pm
What if MySQL had a built-in prefetch thread on the slaves? I think would be more efficient and robust than a python (or whatever) script ripping through the binlog and rewriting update queries into selects.
Chris Barber
13 Jan 08 at 3:35 pm
Sean, thanks.
Xaprb
13 Jan 08 at 8:19 pm
Dale, it’s actually a bit of a misnomer. It’s not pre-fetching the relay log — MySQL already does that extremely fast, as you know. It’s pre-executing the write queries as SELECT queries to address precisely the issue you mentioned: single-threaded execution on the slave.
In a bit more detail: pre-executing these queries can, if the conditions are right, cause MySQL to fetch at least some of the necessary data from the disk, so it’s already in memory when the slave SQL thread wants to update it.
I should really explain this in more detail. I’ll write another post on the topic and give proper references this time :-)
Chris, you are absolutely correct.
Xaprb
13 Jan 08 at 8:22 pm
I saw that presentation. Not much meat there.
Anyway. what’s the right load ? Mostly write bound?
Kevin Burton
14 Jan 08 at 6:22 am
Another note. This will NOT fix performance on DBs that are all in memory. This is how we see most of our performance benefits.
Kevin Burton
14 Jan 08 at 6:24 am
Kevin, I wrote more about the workload in the next post. You’re right about in-memory — it works only if data is lots bigger than memory.
Xaprb
14 Jan 08 at 10:10 am