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.
Technorati Tags:mysql, Paul Tuckfield, replication, sql
Xarpb,
What you (and others) do for the mysql community is outstanding and very much appreciated. Keep up the good work.
Sean
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.
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.
Sean, thanks.
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.
I saw that presentation. Not much meat there.
Anyway. what’s the right load ? Mostly write bound?
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, 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.