Understanding MySQL Parallel Replication: Schema‑based MTS, Logical Clock, Write‑Set and Fairness Metrics

MySQL parallel replay has progressed from schema‑based MTS in 5.6, to group‑commit based logical‑clock in 5.7, and finally to write‑set tracking in 8.0, each improving the ability of a slave to replay transactions concurrently.

MTS based on schema works when DML statements affect different schemas; the slave can replay per‑schema in parallel, but the benefit is limited if the number of schemas is small. The parallelism is controlled by slave_parallel_workers , which should not exceed the number of schemas on the master.

Logical clock (LOGICAL_CLOCK) adds sequence_number and last_committed tags to each transaction in the binlog. Transactions with the same last_committed value were committed together and can be replayed in parallel. Example binlog entries:

#180105 20:08:33 ... last_committed=7201 sequence_number=7203
#180105 20:08:33 ... last_committed=7203 sequence_number=7204
#180105 20:08:33 ... last_committed=7203 sequence_number=7205
#180105 20:08:33 ... last_committed=7203 sequence_number=7206
#180105 20:08:33 ... last_committed=7205 sequence_number=7207

From these tags we can infer dependencies such as:

Transaction 7203 depends on 7201.

Transactions 7204, 7205, 7206 depend on 7203 and can be committed in parallel.

Transaction 7207 depends on 7205, so after 7205 finishes it can run together with 7206.

Optimising logical‑clock parallelism involves tuning binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count . Larger delays increase group size and parallelism but may reduce master throughput.

Write‑set (WRITESET) tracks row‑level changes, allowing parallel replay of transactions that modify different rows. It requires:

binlog_format=row

transaction_write_set_extraction=XXHASH64

All updated tables must have a primary key (otherwise it falls back to COMMIT_ORDER).

binlog_transaction_dependency_tracking set to WRITESET (or WRITESET_SESSION).

slave_parallel_workers enabled on the slave.

Three dependency‑tracking modes are described:

COMMIT_ORDER : based on commit timestamps; different sessions can replay concurrently.

WRITESET : transactions that touch different rows can run in parallel; tables without primary keys revert to COMMIT_ORDER.

WRITESET_SESSION : transactions from the same session keep order, while different sessions may run in parallel.

To evaluate the effectiveness of parallel replay, the article proposes using Jain's fairness index, originally from the paper “A Quantitative Measure Of Fairness And Discrimination For Resource Allocation In Shared Computer Systems”. The index is computed from performance_schema transaction statistics:

select ROUND(POWER(SUM(trx_summary.COUNT_STAR), 2) /
(@@GLOBAL.slave_parallel_workers * SUM(POWER(trx_summary.COUNT_STAR, 2))), 2) as replica_jain_index
from performance_schema.events_transactions_summary_by_thread_by_event_name as trx_summary
join performance_schema.replication_applier_status_by_worker as applier
on trx_summary.THREAD_ID = applier.THREAD_ID;

A value close to 0 indicates many idle applier threads, while a value near 1 shows high thread utilization, helping to choose an appropriate slave_parallel_workers setting.

Collecting the necessary transaction metrics requires enabling performance_schema instruments:

call sys.ps_setup_enable_consumer('events_transactions%');
call sys.ps_setup_enable_instrument('transaction');

Finally, the article lists several reference links for deeper reading on MySQL parallel replication and related metrics.