Configure MySQL Multi-Threaded Replication to Reduce Lag

This article provides a step-by-step guide on how to configure multi-threaded replica workers in MySQL to mitigate replication lag. You will learn how multi-threaded replication works, the key configuration parameters required to enable it, and how to verify that your replica is successfully processing transactions in parallel.

Understanding Multi-Threaded Replication (MTS)

By default, a MySQL replica uses a single SQL thread to apply transactions received from the source. When the write workload on the source is high, this single-threaded model often causes the replica to fall behind, resulting in replication lag. Enabling Multi-Threaded Replication (MTS) allows the replica to distribute transaction execution across multiple worker threads, parallelizing the write workload and keeping the replica synchronized.

Key Configuration Parameters

To configure multi-threaded replication, you need to adjust three primary system variables:

replica_parallel_workers (or slave_parallel_workers in MySQL versions older than 8.0.26): Defines the number of worker threads for executing transactions. Setting this to a value greater than 0 enables MTS. A common starting recommendation is to set this to the number of CPU cores available on the replica server.
replica_parallel_type (or slave_parallel_type): Determines the policy used to decide which transactions can execute in parallel. Set this to LOGICAL_CLOCK (recommended), which allows transactions committed in the same binary log group on the source to be applied concurrently on the replica.
replica_preserve_commit_order (or slave_preserve_commit_order): Ensures that transactions are committed on the replica in the exact same order they were committed on the source. This prevents gaps in the transaction sequence and is highly recommended to maintain data consistency.

Step-by-Step Configuration

Follow these steps to enable multi-threaded replication on your MySQL replica.

Step 1: Pause the Replica SQL Thread

Before modifying replication parameters, you must temporarily stop the replica’s execution thread:

STOP REPLICA SQL_THREAD;
-- Use STOP SLAVE SQL_THREAD; for MySQL versions older than 8.0.22

Step 2: Apply the Parameters Dynamically

Configure the parameters on the running MySQL instance using the following global commands:

SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL replica_parallel_workers = 4; -- Adjust based on your CPU cores
SET GLOBAL replica_preserve_commit_order = ON;

(Note: If using MySQL versions older than 8.0.26, replace the prefix replica_ with slave_ in the variable names above).

Step 3: Make the Configuration Persistent

To ensure the settings persist across MySQL restarts, add the following lines to your MySQL configuration file (my.cnf or my.ini) under the [mysqld] block:

[mysqld]
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 4
replica_preserve_commit_order = ON

Step 4: Restart the Replica SQL Thread

Start the replica SQL thread again to begin parallel transaction processing:

START REPLICA SQL_THREAD;
-- Use START SLAVE SQL_THREAD; for MySQL versions older than 8.0.22

Verifying Multi-Threaded Replication

To confirm that the replica is successfully utilizing the multiple worker threads, execute the following command:

SHOW PROCESSLIST;

In the output, you should see one system user running the Replica coordinator (or Slave coordinator) process, alongside multiple worker threads listed as system user with the state Waiting for an event from Coordinator.