The deal is this: For certain kind of Slave operations, UPDATE, DELETE and INSERT ... SELECT, the operation is both a read operation, as well as a write. To be frank (but I am not, I'm Anders), this is the case with just about any MySQL operation, but for the ones I list, there is a distinct set of rows, defined by the statement itself, that will have to be read.
The above is something we can take advantage on. As there is just one thread applying the changes from the master in the slave, as the slave operations are serialized, this thread is easily the bottleneck. But there is one aspect that doesn't necessarily need to be serialized, which is the read of the data to be modified. We can, to put it simple, pre-warm the MySQL cache with the data that is used by the statements above.
What is needed to make this work is to read from the MySQL relay log, ahead of the SQL Slave Replication thread, and convert the UPDATE, INSERT or DELETE statement to a SELECT, and then execute that SELECT statements. As I said, I didn't invent this, but I wanted to try it out. Hence you can now download slavereadahead 1.0 from Sourceforge. This is a really simple piece of software that just what I describe above. But before you get started, let me tell you some caveats:
- I am NOT reading directly from the MySQL relay log files, rather, I use the output from the SHOW RELAYLOG EVENTS command, which means you have to be running MySQL 5.5 or up.
- Because of the above, not all statements will be available to cache, as row-based events are not shown properly in this case.
- Also, it might be the case, but I have not tested this, that the overhead of doing this through the server cause quite a bit of overhead. I do not know.
The algorithm I use for which statements to pick up and execute is pretty simple, and the conversion from INSERT, UPDATE and DELETE into SELECT is also not very fancy. For us here at Recorded Future, it seems to work though. Any errors in the conversion to / execution of SQL statements are silently ignored.
I'd be really glad if someone would try out the usefulness or not of this program. It comes with the standard GNU autobuild setup, i.e. configure / make, and should be easy to get going with. There is also a basic documentation PDF included.
So, download from Sourceforge and try it out!