The Ultimate Guide to High Availability with MariaDB

Parallel Replication

You are viewing an old version of this article. View the current version here.

Contents

  1. Parallel replication overview
  2. How to enable parallel slave
  3. What can be run in parallel
  4. Expected performance gain
  5. Configuration variable --slave-parallel-max-queued
  6. Implementation details
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.

Parallel replication overview

MariaDB replication in general takes place in three parts:

  • Replication events are read from the master by the IO thread and queued in the relay log
  • Replication events are fetched one at a time by the SQL thread from the relay log
  • Each event is applied on the slave to replicate all changes done on the master.

Before MariaDB 10, the third step was also performed by the SQL thread; this meant that only one event could be executing at a time, and replication was essentially single-threaded. In MariaDB 10, the third step can optionally be performed by a pool of separate replication worker threads, and thereby potentially increase replication performance by applying multiple events in parallel.

How to enable parallel slave

To enable, specify slave-parallel-threads=# in your my.cnf file as an argument to mysql.

The value (#) specifies how many threads will be created in a pool of worker threads used to apply events in parallel for *all* your slaves (this includes multi-source replication). If the value is zero, then no worker threads are created, and old-style replication is used where events are applied inside the SQL thread. Usually the value, if non-zero, should be at least two times the number of multi-source master connections used. It makes little sense to use only a single worker thread for one connection; this will incur some overhead in inter-thread communication between the SQL thread and the worker thread, but with just a single worker thread events can not be applied in parallel anyway.

slave-parallel-threads=# is a dynamic variable that can be changed without restarting mysqld. All slaves connections must however be stopped 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

The variable slave_parallel_max_queued is only meaningful when parallel replication is used (slave_parallel_threads>0).

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.

Implementation details

The implementation is described in MDEV-4506.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.