Parallel replication

MariaDB 10.0 can execute some queries in parallel on the slave. This article will explain how it works and how you can tune it.

Note that parallel replication only exist in a development tree in launchpad. It will be merged with 10.0-base and 10.0 shortly.

How to enable parallel slave

To enable specify slave-parallel-threads=# in your my.cnf file as an argument to mysql where # is bigger than 1.

The value (#) specifics how many threads will be used in to run queries in parallel for *all* your slaves (this includes multi-source replication).

If slave-parallel-threads is 0, then each slave setup will have one thread each to execute queries.

The slave-parallel-threads=# is a dynamic variable that can be changed without restarting mysqld. All slaves connections must however be down when changing the value.

What can be run in parallel

Currently the following things can be run in parallel on the slave:

• Queries that are run on the master in one group commit.
• Queries that are from different domains.
• Queries from different masters (when using multi-source replication).

Expected performance gain

Assuming that the slave has as many threads to execute things as the master, slave should be almost as fast master. We have measured up to 4 times speedups when testing parallel slave.

Open issues/TODO

Parallel replication is still in alpha/testing phase. It's planned to be stable when MariaDB 10.0 is stable.

When the code is in the 10.0 main tree, the expectation is that parallel slave should work, except if you get a failure when reading an event or executing an event or if you run out of memory for the cached events. We are working on fixing this in good time before the 10.0 gamma release

Here follows the open issues that we are working on and should be done before MariaDB 10.0 is declared stable:

• Error handling. If we fail in one of multiple parallel executions, we need to make a best effort to complete prior transactions and roll back following transactions, so slave binlog position will be correct. And all the retry logic for temporary errors like deadlock.
• Stopping the slave needs to handle stopping all parallel executions. And the logic in sql_slave_killed() that waits for current event group to complete needs to be extended appropriately...
• Audit the use of Relay_log_info::data_lock. Make sure it is held correctly in all needed places also when using parallel replication.
• We need some user-configurable limit on how far ahead the SQL thread will fetch and queue events for parallel execution (otherwise if slave gets behind we will fill up memory with pending malloc()'ed events).
• Fix update of relay-log.info and master.info. In non-GTID replication, they must be serialised to preserve correctness. In GTID replication, we should not update them at all except at slave thread stop.
• All the waits (eg. in struct wait_for_commit and in rpl_parallel_thread_pool::get_thread()) need to be killable. And on kill, everything needs to be correctly rolled back and stopped in all threads, to ensure a consistent slave replication state.
• Handle the case of a partial event group. This occurs when the master crashes in the middle of writing the event group to the binlog. The slave rolls back the transaction; parallel execution needs to be able to deal with this wrt. commit_orderer and such.
• We should notice if the master doesn't support GTID, and then run in single threaded mode against that master. This is needed to be able to support multi-master-replication with old and new masters.
• Retry of failed transactions is not yet implemented for the parallel case.

Where can one find details about the implementation

The implementation is described in MDEV-4506.