Design Proposal for Multithreaded Replication in MySQL

With the current version of MySQL there are two threads used by a slave to implement replication. One reads from the binary log of the master and the other one executes the SQL.

If you have a master which is being pounded with individual inserts that are happening on parallel threads these will be serialized by the slave and executed one by one.

The master would in theory fully saturate the disks since it would be bottlenecked. Since the INSERTs are happening in parallel and on multiple threads the disk subsystem would get more effective write performance since it can take advantage of tagged command queueing and so forth.

Since the slave doesn’t have the ability to execute the INSERTs in parallel and can only use one thread it will quickly fall behind replication.

In practice the only way to fix this is to make sure the that the master isn’t fully loaded or that the slaves are more powerful than the master.

Making slaves faster is usually easy to do because you can always implement striping or partitioning.

While I sit here in bed with a bad case of insomnia it dawns on me that it might be possible to implement this functionality by monitoring the ordering of the queries and replaying them in multiple threads on the slave with the same order.

There would need to be a small window that the master would use to note a batch operation that would need to be executed on the slave in a batch. The batch would be an ordered set of queries to execute across threads.

Since the batch would be built on the master there’d be a overall query latency hit but an effective throughput improvement. The batch would be factored into the total write time so this wouldn’t be counted against the slave in replication lag.

This might only be possible with MyISAM though due to the lock ordering. I’ll have to think about this some more with INNODB. The order of addition in the binary log is also important. I’m going to have to think of this as well.

  1. Kevin,

    In fact this was discussed inside MySQL not once or twice. There are numerous problems with this approach in regards to the state of database – even if you reorder queries so they can be executed in parallel, lets say queries A and B update different tables. Now on MASTER query A completed before query B but due to parallel execution on the slave query B completed before query A. This means Query executed on slave could see database stage which never existed on the master.

    This might not be critical for your applications by it is different from semantics of current replication.

  2. Hey Peter….

    This would only apply to INNODB tables… not MyISAM…..


%d bloggers like this: