MariaDB starting with 10.0.5
Parallel Replication was introduced in MariaDB 10.0.5.
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.
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 (#) specifies how many threads will be used to run queries in parallel for *all* your slaves (this includes multi-source replication).
slave-parallel-threads is 0, then each slave setup will have one thread each to execute queries.
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, the slave should be almost as fast as the master. We have measured up to fourfold increases in speed when testing parallel replication.
Configuration variable --slave-parallel-max-queued
When parallel replication is used, the SQL threads will read ahead in the relay logs, queueing events in memory while looking for opportunities for executing events in parallel. The @@slave_parallel_max_queued variable sets a limit for how much memory the SQL threads will use for read-ahead in the relay logs looking for such opportunities.
Note that @@slave_parallel_max_queued is not a hard limit, since the binlog events that are currently executing always need to be held in-memory.
@@slave_parallel_max_queued is mainly needed when using GTID with different replication domain ids. If the binary log contains first transactions in domain 1 followed by some transactions in domain 2, then parallel replication can execute the domain 2 transactions in parallel with the domain 1 transactions. However, this requires that the SQL thread is able to read ahead of the domain 1 transactions while they are executing so that it can queue the domain 2 transactions for parallel execution. Thus, the total size of the domain 1 transactions must be less than @@slave_parallel_max_queued, or parallel execution will not be possible. On the other hand, a too large @@slave_parallel_max_queued value on a slave that is much behind the master could cause the SQL thread to queue up an excessive amount of events in-memory vainly looking for opportunities for parallelism, which could lead to too high memory consumption.
For parallel replication of transactions that group-committed together on the master, only one (or two when moving to the next group commit) transactions can be queued for one worker at a time. In this case, it is sufficient that @@slave_parallel_max_queued is larger than the event size of two normal transactions.
The implementation is described in MDEV-4506.