Parallel Replication, Commit Ordering, Worker Tuning, and Replica Throughput

Learning outcomes

ServiceHub's replica receives binlogs quickly, yet the SQL side falls behind during bursts. The bottleneck is no longer transport; it is apply throughput. MariaDB can dispatch eligible transactions to a pool of replication worker threads. Parallel apply can reduce backlog, but only when the transaction dependency graph and replica resources permit useful concurrency.

In-order parallel replication allows execution overlap while preserving primary commit order. In modern MariaDB, slave_parallel_mode=optimistic is the default mode: transactional DML is attempted concurrently and conflicting work can be rolled back/retried. DDL and non-transactional work have tighter serialization constraints. Out-of-order behavior uses GTID domains and is an explicit application/topology choice, not a transparent tuning switch.

1. Parallelism is constrained by dependencies, not CPU count

Imagine 100 transactions touching 100 unrelated ticket rows. Several can execute concurrently on the replica. Now imagine 100 transactions updating the same account balance row: they serialize on the same lock even if 64 workers exist. More workers can add scheduling, lock-wait, rollback/retry, buffer, and CPU overhead without increasing completed transactions per second.

2. Reproducible parallel-apply lab

services:  primary:    image: mariadb:12.3.2    container_name: mdb14-primary    environment:      MARIADB_ROOT_PASSWORD: labroot    command:      - --server-id=141      - --log-bin=mariadb-bin      - --binlog-format=ROW      - --gtid-domain-id=14      - --gtid-strict-mode=ON    ports:      - "33141:3306"  replica:    image: mariadb:12.3.2    container_name: mdb14-replica    environment:      MARIADB_ROOT_PASSWORD: labroot    command:      - --server-id=142      - --log-bin=replica-bin      - --log-slave-updates=ON      - --relay-log=relay-bin      - --gtid-domain-id=14      - --gtid-strict-mode=ON      - --read-only=ON    ports:      - "33142:3306"

docker compose down -vdocker compose up -d

CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'lab-repl';GRANT REPLICATION REPLICA ON *.* TO 'repl'@'%';-- Disposable lab only: discard initialization-era binlogs before application data.RESET MASTER;SHOW MASTER STATUS;SELECT @@server_id, @@gtid_domain_id, @@global.gtid_binlog_pos;

STOP REPLICA;RESET REPLICA ALL;SET GLOBAL gtid_slave_pos='';CHANGE MASTER TO  MASTER_HOST='primary',  MASTER_PORT=3306,  MASTER_USER='repl',  MASTER_PASSWORD='lab-repl',  MASTER_USE_GTID=slave_pos;START REPLICA;SHOW REPLICA STATUS\G

CREATE DATABASE servicehub_parallel_lab;CREATE TABLE servicehub_parallel_lab.counters(  id INT PRIMARY KEY, value BIGINT NOT NULL) ENGINE=InnoDB;WITH RECURSIVE n AS (  SELECT 1 AS i UNION ALL SELECT i+1 FROM n WHERE i<200)INSERT INTO servicehub_parallel_lab.countersSELECT i,0 FROM n;

Verify the 200-row seed exists on both sides before measuring. The benchmark measures catch-up time after intentionally accumulating a relay backlog; it is not an application benchmark and should not be compared across machines without recording hardware, container limits, storage, cache state, and server settings.

3. Build a deterministic backlog

Stop only the replica SQL thread so I/O keeps receiving. Generate 600 committed transactions spread over 200 keys.

STOP REPLICA SQL_THREAD;SHOW REPLICA STATUS\G

from pathlib import PathN = 600rows = 200out = ["USE servicehub_parallel_lab;"]for i in range(N):    key = (i % rows) + 1    out += ["START TRANSACTION;",            f"UPDATE counters SET value=value+1 WHERE id={key};",            "COMMIT;"]Path("workload.sql").write_text("\n".join(out)+"\n", encoding="utf-8")print(f"wrote {N} committed transactions across {rows} keys")

python generate_workload.pydocker cp workload.sql mdb14-primary:/tmp/workload.sqldocker exec mdb14-primary sh -lc "mariadb -uroot -plabroot < /tmp/workload.sql"

SELECT @@global.gtid_binlog_pos AS target_gtid;

Copy the returned target GTID. On the replica, Gtid_IO_Pos should reach or approach that target while gtid_slave_pos remains behind because SQL apply is stopped.

4. Compare worker counts without fake numbers

To change the worker-pool size, stop replication first. MariaDB documents slave_parallel_threads as dynamic but requires all replica connections stopped while changing it.

STOP REPLICA;SET GLOBAL slave_parallel_threads=4;SET GLOBAL slave_parallel_mode='optimistic';START REPLICA;SHOW VARIABLES LIKE 'slave_parallel%';

Use the following portable Python wrapper to measure how long MASTER_GTID_WAIT() takes to observe the target position. Repeat from a freshly recreated backlog for worker counts 0, 2, 4, and 8. Rebuild/reset the data between runs so each trial starts from the same state; do not merely rerun after the replica is already caught up.

import subprocess, sys, timethreads = sys.argv[1]target = sys.argv[2]cmd = ["docker","exec","mdb14-replica","mariadb","-uroot","-plabroot","-Nse",       f"SELECT MASTER_GTID_WAIT('{target}',120)"]start = time.perf_counter()r = subprocess.run(cmd, text=True, capture_output=True)elapsed = time.perf_counter() - startprint(f"threads={threads} rc={r.returncode} wait_result={r.stdout.strip()} seconds={elapsed:.3f}")if r.stderr.strip(): print(r.stderr.strip())

python measure_catchup.py 4 14-141-601

5. Why optimistic mode can help—and can stop helping

Optimistic mode assumes transactional DML conflicts are uncommon. If two workers conflict, MariaDB can roll back and retry the later transaction. That is acceptable for transactional work because InnoDB can roll back the attempt. It is not a free strategy for DDL or non-transactional engines.

Create a second workload that repeatedly updates the same key. You do not need to quote a speedup number; observe that worker availability no longer creates independent work.

from pathlib import PathN = 600out = ['USE servicehub_parallel_lab;']out += ['UPDATE counters SET value=value+1 WHERE id=1;' for _ in range(N)]Path('hot_workload.sql').write_text('\n'.join(out)+'\n', encoding='utf-8')print(f'wrote {N} autocommit hot-key transactions')

python generate_hot_workload.pydocker cp hot_workload.sql mdb14-primary:/tmp/hot_workload.sqldocker exec mdb14-primary sh -lc "mariadb -uroot -plabroot < /tmp/hot_workload.sql"

Each statement is its own autocommit transaction, so the backlog contains many transactions but they all contend for the same row. This isolates the dependency effect from the separate question of transaction size.

6. Conservative, optimistic, and aggressive are policies, not rankings

Always verify exact target-version options before persisting them. Multi-source connections share the worker pool and can add their own fairness/capacity constraints.

7. Observable symptoms when worker count is not the answer

• I/O caught up, SQL behind: apply is the candidate bottleneck; inspect worker/lock/resource behavior.
• Both I/O and SQL behind: network/source/binlog read may also be limiting; worker tuning alone is incomplete.
• CPU saturated: more workers can steal resources from read queries and InnoDB background work.
• Disk latency climbs: parallel commit/apply can amplify write pressure.
• Hot-row lock waits: dependency graph, not thread count, dominates.
• DDL backlog: serialization/metadata locking dominates.

8. Production judgment

Start from a measured backlog and workload shape. Increase worker count in controlled steps, keep the primary's commit pattern and replica hardware in view, and stop when marginal benefit disappears or resource contention harms the replica's other responsibilities. A reporting replica that catches up faster but saturates CPU and misses read SLOs is not an improvement.

Next, use deliberate delay and multiple replication sources to build recovery/reporting patterns—and confront the application consistency contract that comes with stale replicas.

docker compose down -v