MySQL 5.7 Parallel Replication: Architecture, Config & Performance Gains

MySQL 5.7 Parallel Replication Era

MySQL replication lag has long been a pain point, but MySQL 5.7 introduces true parallel replication called enhanced multi‑threaded slave (MTS), which dramatically reduces lag and can even eliminate hour‑long delays in production environments.

MySQL 5.6 Parallel Replication Architecture

MySQL 5.6 supports parallel replication based on schemas (databases). When multiple schemas exist, replication speed can improve.

The red box in the diagram shows the key part for parallel replication. Before MySQL 5.6, a slave has an I/O thread and an SQL thread. Enabling parallel replication turns the SQL thread into a coordinator thread, which assigns work to worker threads.

If a transaction can be executed in parallel, the coordinator selects a worker thread to apply the binary log.

If it cannot (e.g., DDL or cross‑schema transaction), the coordinator waits for all workers to finish before proceeding. The coordinator also replays logs itself, following a producer‑consumer model.

This schema‑based parallel replication has two drawbacks: crash‑safety is hard because transactions may finish out of order, and performance gains are limited when only one schema is present, sometimes even worse than single‑threaded replication.

MySQL 5.7 Parallel Replication Principles

Group‑Commit Based Parallel Replication

MySQL 5.7 achieves true parallel replication by ensuring the slave replays transactions in the same order as the master. There is no longer a schema‑based limitation, and no special binary‑log format is required.

Officially, MySQL planned both table‑level and row‑level parallel replication. The row‑level approach parses ROW‑format binary logs (WL#4648). The final feature is called MTS: Prepared transactions slave parallel applier (WL#6314), originally proposed by MariaDB’s Kristain and already present in MariaDB 10.

The core idea: a group‑committed transaction set can be replayed in parallel because all transactions have reached the PREPARE phase, indicating no conflicts.

To stay compatible with MySQL 5.6’s schema‑based parallel replication, 5.7 adds the variable slave-parallel-type with two possible values:

DATABASE – default, schema‑based parallel replication.

LOGICAL_CLOCK – group‑commit based parallel replication.

GTID Support for Parallel Replication

Identifying which transactions belong to the same group is done via GTID. When GTID is disabled, MySQL 5.7 introduces an Anonymous_Gtid event type that still carries group‑commit information.

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000006';
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                          |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000006| 4   | Format_desc    | 88        | 123         | Server ver: 5.7.7-rc-debug-log, Binlog ver: 4 |
| mysql-bin.000006| 123 | Previous_gtids | 88        | 194         | f11232f7-ff07-11e4-8fbb-00ff55e152c6:1-2      |
| mysql-bin.000006| 194 | Anonymous_Gtid | 88        | 259         | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'          |
| mysql-bin.000006| 259 | Query          | 88        | 330         | BEGIN                                         |
| mysql-bin.000006| 330 | Table_map      | 88        | 373         | table_id: 108 (aaa.t)                         |
| mysql-bin.000006| 373 | Write_rows     | 88        | 413         | table_id: 108 flags: STMT_END_F               |
| ...

Even without GTID, each transaction begins with an Anonymous_Gtid that contains group‑commit data.

LOGICAL_CLOCK

Using mysqlbinlog, one can see additional fields last_committed and sequence_number in the binary log, representing the logical clock.

#150520 14:23:11 server id 88 end_log_pos 259 CRC32 0x4ead9ad6 GTID last_committed=0 sequence_number=1
#150520 14:23:11 server id 88 end_log_pos 1483 CRC32 0xdf94bc85 GTID last_committed=0 sequence_number=2
#150520 14:23:11 server id 88 end_log_pos 2708 CRC32 0x0914697b GTID last_committed=0 sequence_number=3
#150520 14:23:11 server id 88 end_log_pos 3934 CRC32 0xd9cb4a43 GTID last_committed=0 sequence_number=4
#150520 14:23:11 server id 88 end_log_pos 5159 CRC32 0x06a6f531 GTID last_committed=0 sequence_number=5
#150520 14:23:11 server id 88 end_log_pos 6386 CRC32 0xd6cae930 GTID last_committed=0 sequence_number=6
#150520 14:23:11 server id 88 end_log_pos 7610 CRC32 0xa1ea531c GTID last_committed=6 sequence_number=7
...

Transactions sharing the same last_committed belong to the same group and can be replayed in parallel. The logical clock is implemented in the source as:

class Logical_clock {
private:
  int64 state;
  int64 offset;
  ...
};

In MYSQL_BIN_LOG two logical‑clock members are defined: max_committed_transaction – records the logical clock of the last group commit (maps to last_committed). transaction_counter – records the logical clock of the current group (maps to sequence_number).

Parallel Replication Testing

Enabling MTS dramatically increases slave QPS. Using sysbench’s single‑table full‑update test, 16 worker threads achieve over 25,000 QPS, while single‑threaded replay stays around 4,000 QPS. MySQL 5.6’s schema‑based MTS cannot improve this workload.

Parallel Replication Configuration and Tuning

master_info_repository

After enabling MTS, set master_info_repository=TABLE to gain 50‑80% performance, because the master‑info file updates become a contention point.

slave_parallel_workers

Setting slave_parallel_workers=0 disables parallelism. Setting it to 1 creates a coordinator thread but only one worker, which can be slower than the single‑threaded case (about 20% slower in tests).

If the master load is low, group commits may contain only one transaction, causing parallel replication to perform worse than single‑threaded replication.

Enhanced Multi‑Threaded Slave Configuration

Enable the enhanced multi‑threaded slave with the following settings:

# slave
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

Monitoring can still use SHOW SLAVE STATUS\G, and MySQL 5.7 adds performance_schema tables for finer‑grained metrics:

mysql> show tables like 'replication%';
+---------------------------------------------+
| Tables_in_performance_schema (replication%) |
+---------------------------------------------+
| replication_applier_configuration          |
| replication_applier_status                 |
| replication_applier_status_by_coordinator   |
| replication_applier_status_by_worker        |
| replication_connection_configuration       |
| replication_connection_status              |
| replication_group_member_stats             |
| replication_group_members                  |
+---------------------------------------------+
8 rows in set (0.00 sec)

Conclusion

MySQL 5.7’s Enhanced Multi‑Threaded Slave eliminates the long‑standing replication lag problem, proving that MySQL’s physical replication can match or exceed logical replication solutions when properly configured.