How MySQL Parallel Replication Cuts Slave Lag: Group Commit, Lock‑Based & WRITESET Explained

Why Slave Lag Matters

Replication lag causes stale reads and can delay failover. The metric Seconds_Behind_Master grows when the slave replays a large backlog of binlog events.

Parallel Replication Schemes in MySQL

MySQL provides three main parallel replication mechanisms:

Database‑level parallelism (MySQL 5.6) – rarely used for single‑database, multi‑table workloads.

Group‑commit based parallelism (MySQL 5.7) – includes Commit‑Parent‑Based and Lock‑Based schemes.

WRITESET‑based parallelism (MySQL 8.0) – introduced for Group Replication certification.

Commit‑Parent‑Based Scheme

Each transaction is split into a Prepare phase and a Commit phase. A global counter is incremented before the storage‑engine commit; the value is stored as commit‑parent in the binlog. Transactions sharing the same commit‑parent can be replayed in parallel on the slave.

Example (seven transactions):

Trx1 ------------P----------C-------------------------------->
Trx2 ----------------P------+---C---------------------------->
Trx3 -------------------P---+---+-----C---------------------->
Trx4 -----------------------+-P-+-----+----C----------------->
Trx5 -----------------------+---+-P---+----+---C------------->
Trx6 -----------------------+---+---P-+----+---+---C---------->
Trx7 -----------------------+---+-----+----+---+-P-+--C------->

Trx1, Trx2, Trx3 execute in parallel.

Trx4 executes serially.

Trx5, Trx6 execute in parallel.

Trx7 executes serially.

Lock‑Based Scheme

The lock‑based approach defines a locking interval from the last DML lock acquisition in the Prepare phase to the lock release just before the storage‑engine commit. If two transactions’ intervals do not overlap, they have no lock conflict and can be replayed concurrently.

Key variables on the master: global.transaction_counter – global transaction counter. transaction.sequence_number – per‑transaction sequence number. global.max_committed_transaction – highest committed sequence number. transaction.last_committed – max committed sequence number seen before the transaction enters Prepare.

Both sequence_number and last_committed are written to the binlog (as GTID_LOG_EVENT for GTID replication, otherwise ANONYMOUS_GTID_LOG_EVENT).

WRITESET Scheme (MySQL 8.0)

The WRITESET method records a hash of each modified row (derived from primary‑key, unique‑index, or foreign‑key information) into a transaction‑wide write‑set. Two transactions are independent if their write‑sets do not intersect.

Relevant parameters: binlog_transaction_dependency_tracking – selects the dependency algorithm (COMMIT_ORDER, WRITESET, WRITESET_SESSION). transaction_write_set_extraction – hash algorithm (OFF, MURMUR32, XXHASH64; default XXHASH64). binlog_transaction_dependency_history_size – maximum entries in the in‑memory write‑set history (default 25000).

Implementation excerpt:

void Writeset_trx_dependency_tracker::get_dependency(THD *thd,
                                                     int64 &sequence_number,
                                                     int64 &commit_parent) {
  Rpl_transaction_write_set_ctx *write_set_ctx =
      thd->get_transaction()->get_transaction_write_set_ctx();
  std::vector<uint64> *writeset = write_set_ctx->get_write_set();
  // ... checks for emptiness, global settings, foreign‑key relations, size limits ...
  // Update m_writeset_history and compute last_parent / commit_parent.
}

Benchmark Results

Tests were run on a 16‑core SSD server with 8 million rows across 16 tables. Three scenarios were measured: COMMIT_ORDER, WRITESET, and WRITESET_SESSION, each with varying slave thread counts.

Key observations:

COMMIT_ORDER benefits from higher master concurrency – more threads yield faster slave replay.

WRITESET shows little sensitivity to master thread count; even a single thread outperforms COMMIT_ORDER with 256 threads.

WRITESET_SESSION behaves like COMMIT_ORDER but still achieves high throughput at low thread counts (4–8).

Performance charts:

Enabling Parallel Replication

On the replica, set the following parameters (requires a replication restart):

slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON

On the master (MySQL 5.7.22+ / 8.0), enable WRITESET‑based tracking:

binlog_transaction_dependency_tracking = WRITESET_SESSION
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_history_size = 25000
binlog_format = ROW

Note: WRITESET parallelism works only with ROW binlog format.

References

WL#6314: MTS: Prepared transactions slave parallel applier – https://dev.mysql.com/worklog/task/?id=6314

WL#6813: MTS: ordered commits (sequential consistency) – https://dev.mysql.com/worklog/task/?id=6813

WL#7165: Optimizing MTS scheduling – https://dev.mysql.com/worklog/task/?id=7165

WL#8440: Group Replication parallel applier support – https://dev.mysql.com/worklog/task/?id=8440

WL#9556: Writeset‑based MTS dependency tracking – https://dev.mysql.com/worklog/task/?id=9556

WriteSet parallel replication article – https://www.jianshu.com/p/616703533310

Improving the Parallel Applier with Writeset‑based Dependency Tracking – https://mysqlhighavailability.com/improving-the-parallel-applier-with-writeset-based-dependency-tracking/