Causes of Replication Delay

MySQL replication lag typically stems from several key factors:

• Single-threaded SQL Thread: Before MySQL 5.7, replication was single-threaded on the slave, causing delays when handling concurrent transactions from the master.
• Hardware Disparity: Slaves often have less powerful hardware than the master, affecting processing speed.
• Poor Schema Design: Missing primary keys or unique constraints can lead to inefficient row searches during replication.
• High Read Load on Slave: Long-running or inefficient queries on the slave can interfere with replication thread performance.
• Large Transactions: A long-running transaction on the master can cause significant lag on the slave as it waits for the full transaction to be applied.
• Random Disk I/O: While the master writes sequentially to the binary log, the slave performs random I/O operations when applying changes, which is slower.
• High DDL Load: Frequent schema changes can overwhelm a single SQL thread.
• Network Latency: Slow or unstable network between master and slave affects binary log transfer speed.

Replication involves three threads: Binlog Dump (master), IO Thread, and SQL Thread (both on the slave). Delays usually occur due to:

1. SQL Thread Bottleneck: Most common issue where the SQL thread cannot keep up with the volume of changes or due to lock contention.
2. Network Issues: Less common but can occur due to high latency or low bandwidth.

Slave Configuration Adjustments

Optimize slave-side I/O operations to reduce replication delay:

Adjust sync_binlog

This parameter controls how frequently binary logs are flushed to disk:

• sync_binlog=0: Only writes to cache (fastest, but risky).
• sync_binlog=1: Flushes to disk on every commit (safest, but slowest).
• sync_binlog=N: Flushes every N transactions (balanced approach).

For replication delay, consider setting this to 1000 temporarily to reduce I/O pressure.

Disable Binary Logging on Slave

If not required (e.g., not part of a chained replication topology), disable binary logging on the slave to save I/O resources:

SET GLOBAL log_bin = OFF;

Tune innodb_flush_log_at_trx_commit

Controls transaction log flushing behavior:

• 1: Flushes and fsyncs on every commit (default, safest).
• 2: Flushes to OS cache every commit, fsyncs once per second (recommended for replication delay mitigation).
• 0: Flushes every second (least safe).

Optimization Techniques

Optimize I/O Setings

Temporarily adjust I/O-related parameters to speed up replication catch-up:

SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 1000;
SET GLOBAL innodb_io_capacity = 20000;
SET GLOBAL innodb_flush_neighbors = 0;

Once replication is caught up, revert to safer values:

SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL sync_binlog = 1;

Double Commit Settings

For safety on standalone instances:

SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL sync_binlog = 1;

For replication lag mitigation on slaves:

SET GLOBAL innodb_flush_log_at_trx_commit = 0;
SET GLOBAL sync_binlog = 0;

Relay Log Flushing Frequency

Reduce relay log flush frequency to minimize I/O:

SET GLOBAL sync_relay_log_info = 10000;
SET GLOBAL sync_relay_log = 10000;

After catching up, reset to default:

SET GLOBAL sync_relay_log_info = 1;
SET GLOBAL sync_relay_log = 1;

Optimize Row Search Algorithms

Use hash scanning to reduce full table scans on tables without primary keys:

SET GLOBAL slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN';

This improves performance for row-based replication on tables without indexes.

Manage Semi-Synchronous Replication

In high-delay situations with ACK backlog, consider temporarily switching to asynchronous replication:

STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

Use this during peak loads or when catching up, then revert to semi-sync afterward.

Parallel Replication

MySQL 5.6: Database-Level Parallelism

MySQL 5.6 introduced parallel replication by database:

SET GLOBAL slave_parallel_type = 'DATABASE';
SET GLOBAL slave_parallel_workers = 4;

Effective only when multiple databases are in use.

MySQL 5.7: Logical Clock Parallelism

Improved parallelism using group commit information:

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 16;

Enable in configuration file for persistence:

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

Parallel Replication Principles

Key concepts:

• Transactions with the same last_committed value can be applied in parallel.
• Use binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count to batch commits and improve parallelism.
• Transactions in the prepare phase can be executed in parallel.

Parallel execution is limited by row-level conflicts and transaction ordering requirements.

Architectural Optimization

• Sharding: Distribute data across multiple databases to reduce per-node load.
• Caching: Use caching layers to reduce read load, but be cautious with frequently updated data.
• Hardware Upgrades: SSDs, faster CPUs, and more RAM can improve replication throughput, though cost considerations may limit this approach.