Understanding MySQL Replication: Threads, Logs, and Performance Tweaks

In this "MySQL with Diagrams" series, the replication process is explained: transactions are written to the primary server’s binary log, transferred to replicas, and applied.

The replica’s connection metadata repository stores information needed to retrieve transactions from the primary’s binary log in the mysql.slave_master_info table, while the application metadata repository stores information for applying transactions from the relay log in the mysql.slave_relay_log_info table.

Binary Log Dump Thread

This thread transfers binary‑log events to connected replica servers. On the primary you can identify it in the output of SHOW PROCESSLIST, where it appears as a "Binlog Dump" thread.

Replication I/O Receiver Thread

On the replica side, an I/O receiver thread connects to the primary, requests any new changes stored in the binary log, and writes them to the replica’s relay log. Its status can be checked in the output of SHOW REPLICA STATUS (field Slave_IO_running ).

Replication SQL Apply Thread

After updates are written to the relay log, the SQL apply thread processes them. If replica_parallel_workers is set to 0, a single SQL thread applies the transactions; if it is ≥ 1, a coordinator thread spawns multiple worker threads to apply transactions in parallel.

Setting the number of parallel workers requires care: more workers do not always mean faster application, and their efficiency can be examined via PERFORMANCE_SCHEMA.

Relay‑Log Index File : tracks relay‑log files to ensure replication can resume seamlessly after interruptions.

Binary Log Index File : tracks binary‑log files used by the primary server.

Sync and durability : the replica’s sync_binlog setting determines how often the relay log is flushed to disk, balancing performance against data safety.

Best Performance Configuration

replica_net_timeout : controls the connection timeout between replica and primary; adjusting it helps maintain stable replication under variable network conditions.

sync_binlog : on the source, determines how frequently the binary log is flushed to disk; a value of 1 provides maximum durability but can reduce performance.

binlog_cache_size : increasing this reduces temporary file usage, optimizing I/O during large transactions.

replica_preserve_commit_order : ensures the commit order on the replica matches the primary, preserving strict consistency at a possible performance cost.

replica_parallel_workers : defines the number of parallel replication threads; higher values can improve performance when transactions are independent.