When and How to Implement Master‑Slave Read/Write Splitting in MySQL

When Should You Use Read/Write Splitting?

In systems where read traffic far exceeds write traffic—such as a public‑account platform where most users only read articles—splitting reads from writes can improve performance. The rule of thumb is to benchmark your MySQL instance; on a 4‑core, 8 GB machine running MySQL 5.7 you can handle roughly 10,000 QPS. If peak traffic exceeds about half of that (≈5,000 QPS), consider read/write splitting.

How to Implement Read/Write Splitting

The implementation hinges on two key points:

Database layer: master‑slave data replication.

Middleware layer: provide a transparent interface so developers interact with a single logical database without worrying about master‑slave details.

The resulting architecture places writes on the master and reads on one or more slaves.

Database Layer: How MySQL Handles Master‑Slave Sync

The master records every INSERT, UPDATE, DELETE in a binary log (binlog).

The slave’s I/O thread reads the binlog from the master and writes it to a relay log.

The master runs a log‑dump thread that streams the binlog to the slave’s I/O thread.

The slave’s SQL thread reads the relay log, parses the statements, and executes them, using multiple SQL threads for concurrency.

This design, with an intermediate relay log, prevents the slave’s I/O thread from being blocked by slow SQL execution, a pattern that can be applied to service‑to‑service communication as well.

Middleware Layer: Designing for Generality and Ease of Use

Two goals guide middleware design:

Language‑agnostic architecture (supporting Java, Go, PHP, etc.).

Developer‑friendly API that behaves like a single master database.

For multi‑language environments, a proxy layer can be added. Open‑source projects such as MyCat implement a MySQL protocol proxy, allowing clients to connect as if to a single server while the proxy forwards queries to the appropriate master or slave.

Advantages: multi‑language support; disadvantages: extra network hop and the need to deploy the proxy in a clustered, highly‑available fashion.

Another popular solution is ShardingSphere , which provides a configurable middleware that abstracts master‑slave routing and can be extended with custom plugins.

Problems Introduced by Read/Write Splitting and Their Solutions

Replication lag can cause data inconsistency between master and slaves. Real‑world examples include delayed product updates visible to users. To address this, three layers of mitigation are recommended:

1. Monitoring and Alerting

Two common methods:

Query SHOW SLAVE STATUS on the slave and inspect Seconds_Behind_Master. Values: NULL: I/O or SQL thread failure. 0: replication healthy. Positive number: lag in seconds.

Use the mk‑heartbeat tool from Maatkit: create a heartbeat table on the master, continuously update a timestamp, and compare it on the slave. A non‑zero difference indicates lag.

2. Business‑Layer Mitigations

Cache the newly written data on the page and display it directly.

Use a NoSQL cache (e.g., Redis) that is updated together with the database.

For strict consistency, read from the master after a write, provided the master can handle the extra load.

3. Database‑Layer Solutions

Two common approaches:

Semi‑synchronous replication: the master waits for an ACK from the slave before confirming the write to the client. Suitable for single‑master‑single‑slave setups.

GTID‑based replication: MySQL 5.7.6+ can return a GTID after a transaction. By setting session_track_gtids=OWN_GTID and using SELECT wait_for_executed_gtid_set(gtid_set, 1), the client can verify that the slave has applied the transaction before reading from it.

Summary

The article covered the criteria for adopting read/write splitting, the mechanics of MySQL master‑slave replication, middleware design considerations, and practical ways to monitor and mitigate replication lag. When designing a system, evaluate the consistency requirements and decide early whether to invest in monitoring, middleware, or stricter replication modes.