MySQL Semi-Synchronous vs Asynchronous Replication

This article explains the key differences between MySQL semi-synchronous replication and traditional asynchronous replication. It covers how each method handles data transmission, the impact of each on write latency, and how to choose the right replication strategy to balance data safety with database performance.

Understanding Traditional Asynchronous Replication

In traditional asynchronous replication, the source (master) server writes transactions to its binary log and commits them locally without waiting for any confirmation from the replicas. The replica servers pull the events from the source’s binary log and apply them to their own databases independently.

• How it works: The client sends a write request to the source. The source executes the transaction, writes it to the binlog, commits it, and immediately sends a success response to the client. The replication of data to the replicas happens entirely in the background.
• Advantages: It offers the highest performance and the lowest write latency because the source does not wait for network round-trips to the replicas before responding to the client.
• Disadvantages: There is a risk of data loss. If the source crashes before the replica has copied the binary log events, those transactions are lost upon failover to the replica.

Understanding Semi-Synchronous Replication

Semi-synchronous replication bridges the gap between fully synchronous and asynchronous replication. With this method, the source commits the transaction locally but waits to return a success status to the client until at least one replica has received the transaction and written it to its relay log.

• How it works: The client sends a write request. The source executes the transaction, writes it to the binlog, and sends the events to the replica. The source then pauses and waits. Once a replica receives the events, writes them to its relay log, and sends an acknowledgment back, the source completes the transaction commit and responds to the client.
• Advantages: It guarantees high data integrity. If the source crashes, you are assured that the committed data has been successfully copied to at least one replica, preventing data loss during failover.
• Disadvantages: It introduces write latency. Every write transaction must wait for a network round-trip between the source and the replica, which can slow down write-heavy applications.

Key Differences Side-by-Side

1. Data Safety and Durability

• Asynchronous: Lower. If the source fails, transactions that were committed on the source but not yet sent to the replica are lost.
• Semi-Synchronous: Higher. No transaction is considered committed from the client’s perspective until it is safely stored in the relay log of at least one replica.

2. Performance and Latency

• Asynchronous: Faster. Write performance is limited only by the local source server’s hardware capabilities.
• Semi-Synchronous: Slower. Write performance is bounded by the network latency between the source and the nearest replica.

3. Fallback Behavior (Timeout)

MySQL semi-synchronous replication includes a fallback mechanism. If a replica does not acknowledge a transaction within a configurable timeout period (controlled by the rpl_semi_sync_master_timeout variable), the source automatically switches to asynchronous replication. Once at least one replica catches up and begins acknowledging transactions again, the source automatically switches back to semi-synchronous mode.

Which Replication Method Should You Choose?

• Choose Asynchronous Replication if your application requires maximum write throughput, low latency, and can tolerate occasional data loss in the rare event of a master node crash.
• Choose Semi-Synchronous Replication if data loss is unacceptable (such as in financial transactions, billing systems, or user state databases) and your application can tolerate the additional network latency required to secure the data on multiple nodes.