skip to content

A MySQL 8.4 replica running the default four applier workers still falls behind at peak; how does it choose which transactions to apply in parallel, and what limits it?

level: seniorimportance: should knowfreq 50%

answer

  1. coordinator plus worker threads
  2. dependency marks written by the source
  3. rows each transaction wrote
  4. commit order makes fast workers wait
  5. per-worker timestamps, not one field

basics

~20 s

A MySQL 8.4 replica runs transactions in parallel when the source's binlog marks them independent, using writesets of the rows each changed. Shared rows, DDL, huge transactions, tables without primary keys and preserved commit order all serialize apply.

solid answer

~50 s

In MySQL 8.4 replicas are multithreaded by default: `replica_parallel_workers=4`, with a coordinator reading the relay log and handing transactions to workers under `replica_parallel_type=LOGICAL_CLOCK`. The source decides what may overlap: in 8.4 it always derives dependencies from writesets, the hashed keys of the rows each transaction wrote, and records them in the binlog; two transactions touching the same row or unique key must apply in order. Parallelism then shrinks when the workload hammers a few hot rows, when DDL forces serialization, when one transaction exceeds `replica_pending_jobs_size_max` (128M) and is held until every worker queue is empty, and when tables lack primary keys, which the manual warns hurt multithreaded replicas even more. `replica_preserve_commit_order=ON` makes finished workers wait for earlier ones to commit. Measure with `performance_schema.replication_applier_status_by_worker` timestamps; on a multithreaded replica `Seconds_Behind_Source` is based on `Exec_Source_Log_Pos` and can mislead.

code

ini · 5 lines
ini
[mysqld]
# replica side; these are the MySQL 8.4 defaults except the worker count
replica_parallel_workers      = 8
replica_preserve_commit_order = ON
replica_pending_jobs_size_max = 128M

go deeper

for a junior

Recall that a MySQL 8.4 replica applies changes with several worker threads by default and that replication lag means the replica is behind the source.

for a middle

Explain the coordinator and worker split, LOGICAL_CLOCK scheduling, and why two transactions touching the same row cannot apply in parallel.

for a senior

Diagnose a lagging replica from per-worker Performance Schema data, name the limit at work, from hot rows to oversized transactions, and fix the workload before adding workers.

for a principal

Set fleet rules that keep replicas parallel: mandatory primary keys, a batch size limit for bulk jobs, and lag alerts built on per-worker timestamps.

## How the multithreaded applier is built A MySQL replica has a **receiver** thread that copies the source's binary log into the local **relay log**, and an **applier** that executes it. In MySQL 8.4 the applier is multithreaded by default: - `replica_parallel_workers` defaults to **4**. With a value of 1 or more there is one **coordinator** thread that reads the relay log and schedules transactions, plus that many **worker** threads that execute them. Setting it to 0 is deprecated; 1 means one worker, so apply is sequential. - `replica_parallel_type` defaults to `LOGICAL_CLOCK`, scheduling from dependency information written by the source. The alternative, `DATABASE`, parallelizes only across schemas, and both the variable and that policy are deprecated. - The setting takes effect at the next `START REPLICA`. The general causes of replication lag are covered in the general lesson on lag; this answer is about what MySQL's applier itself can and cannot overlap. ## Where the dependency information comes from The **source** decides what may run in parallel, when it writes the binlog. In MySQL 8.4 it always uses **writesets**: the set of rows a transaction wrote, identified by hashes of the table and key values, with secondary unique key values added. Two transactions whose writesets intersect are dependent and must apply in order; transactions with disjoint writesets may overlap on the replica. 8.4 removed the old `binlog_transaction_dependency_tracking` variable because writeset tracking is now built in, and it is used only under row-based logging. Hash collisions can create false dependencies, and where dependencies cannot be worked out efficiently the server treats transactions as dependent to be safe. ## What limits parallelism 1. **Hot rows.** If most transactions update the same counter or the same few accounts, their writesets collide and the replica applies them one by one, however many workers it has. 2. **DDL.** A schema change forces a dependency, so the transactions around it are applied in order. 3. **Huge transactions.** `replica_pending_jobs_size_max` (default **128M**) caps the memory for worker queues. A transaction bigger than that is held until all workers' queues are empty, then runs alone, and later transactions wait behind it. 4. **Tables without primary keys.** Row events find their target row by key. The manual warns that tables without primary keys always harm performance and hurt even more when `replica_parallel_workers` is above 1, so add keys before raising the worker count. 5. **Preserved commit order.** `replica_preserve_commit_order` defaults to `ON`: workers may execute in parallel, but they **commit in relay-log order**, so a fast worker that finished early waits for the slow one ahead of it. Turning it `OFF` removes the wait but lets transactions commit out of order, leaving gaps in the executed sequence that affect recovery and what a reader sees. 6. **Chained replicas.** In a multi-level topology, `LOGICAL_CLOCK` may find less parallelism at each level further from the original source. 7. **Too many workers.** Beyond some point extra workers add contention and lower throughput; the right number is found by testing. On the source, `binlog_group_commit_sync_delay` (microseconds, default 0) can make more transactions commit together, which the manual notes can increase parallel execution on `LOGICAL_CLOCK` replicas at the cost of source commit latency. ## Measuring it honestly `SHOW REPLICA STATUS` gives `Seconds_Behind_Source`, but on a multithreaded replica that value is based on `Exec_Source_Log_Pos`, so it may not reflect the most recently committed transaction. Per-worker detail lives in the Performance Schema: ```sql SELECT WORKER_ID, LAST_APPLIED_TRANSACTION, TIMEDIFF(LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP, LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) AS lag_at_apply, APPLYING_TRANSACTION FROM performance_schema.replication_applier_status_by_worker; ``` | Symptom | Likely limit | |---|---| | one worker busy, others idle | hot rows or a forced dependency | | all workers idle, then a long stall | an oversized transaction | | steady lag after a bulk job | many row events on keyless tables | The same table explains a stopped applier. `APPLYING_TRANSACTION` names the GTID a worker is executing now, and `LAST_ERROR_NUMBER` and `LAST_ERROR_MESSAGE` record the error that stopped it, which also reaches the replica's error log. When the coordinator and many workers are idle while one worker shows a long-running `APPLYING_TRANSACTION`, the lag is that single transaction, not a shortage of threads. ## What to change, in order - Add primary keys to every replicated table. - Split bulk updates and deletes into smaller transactions. - Spread hot-row writes, for example by sharding a counter row. - Only then test a higher `replica_parallel_workers`, watching the per-worker table rather than one lag number.

  • What do you give up by setting replica_preserve_commit_order=OFF on a MySQL replica?
    Workers can then commit out of relay-log order, so at any moment the replica may have applied a later transaction while an earlier one is still pending. Checking the most recently executed transaction no longer proves everything before it is applied, which complicates recovery after a crash and means readers can see effects before their causes. It removes the commit wait, but consistency costs usually outweigh that.
  • Why can one 2 GB DELETE stall a MySQL multithreaded replica for far longer than it took on the source?
    Under row logging the delete becomes millions of row events, and a transaction larger than `replica_pending_jobs_size_max` is held until every worker queue drains, then applied alone while later transactions queue behind it. If the table has no primary key each row event is costlier to locate. Splitting the delete into small batches lets the replica keep overlapping work.
  • Why does Seconds_Behind_Source mislead on a multithreaded MySQL replica?
    It is computed from event timestamps and, on a multithreaded replica, is based on `Exec_Source_Log_Pos`, so with several workers it may not reflect the latest committed transaction. The per-worker rows in `performance_schema.replication_applier_status_by_worker` show each worker's last applied GTID with its original commit and apply timestamps, which is a better basis for measuring apply lag.

  • Doubling replica_parallel_workers always halves replica lag.
  • The replica works out which transactions conflict by itself while applying.
  • Commit order preservation only matters for statement-based replication.
  • Tables without primary keys replicate fine because row events carry every column.
  • Seconds_Behind_Source of zero proves every worker is fully caught up.
  • MySQL replicas apply single-threaded by default, so parallelism must be enabled first.

Assumes MySQL 8.4 LTS: replicas default to four workers with LOGICAL_CLOCK and preserved commit order, and 8.4 removed binlog_transaction_dependency_tracking in favour of built-in writeset tracking.