When to Scale MySQL: From Single Instance to Distributed Architecture

1. Why a Single MySQL Instance Fails

When traffic grows, a single MySQL node can be constrained by three independent pressures, each suggesting a different scaling technique:

Heavy read load (e.g., product detail or order lookup) –> read‑write splitting, adding replica(s) to share reads.

Heavy write load or huge tables (tens of millions to billions of rows) –> sharding (horizontal/vertical) to spread data across machines.

High availability requirement (master must not be a single point of failure) –> HA architecture with master‑slave replication and automatic failover.

These pressures often overlap; however, the author stresses that before any of them, one should first exhaust single‑node optimizations such as better indexing and adding a Redis cache.

2. Read‑Write Splitting and Advanced Replication

Read‑write splitting routes writes to the primary and reads to one or more replicas. It relies on MySQL master‑slave replication, where the master writes changes to the binlog and each replica pulls and replays them.

Asynchronous replication (default) : master returns after writing the binlog, does not wait for replicas – best performance, risk of data loss if the master crashes before replicas receive the binlog.

Semi‑synchronous replication : master waits until at least one replica acknowledges receipt of the binlog – slightly lower performance, stronger durability.

Group Replication (MGR) : Paxos‑based multi‑primary group that provides strong consistency and built‑in automatic failover – heavier overhead.

Read‑write splitting can be implemented at the middleware layer (e.g., ProxySQL, MyCat, ShardingSphere‑Proxy) where the application talks to a proxy that routes queries, or at the application layer using client‑side routing libraries such as Sharding‑JDBC.

3. Master‑Slave Lag: The Unavoidable Pitfall

The biggest hidden cost of read‑write splitting is replication lag: the replica may not have replayed the latest writes, causing a “read‑after‑write” miss.

Master writes are multi‑threaded while early replicas replay binlog single‑threaded.

Large transactions keep both master and replica busy for a long time.

Replica hardware may be weaker or overloaded with read traffic.

DDL statements block replication on the replica.

Mitigation strategies include:

Parallel replication (LOGICAL_CLOCK in MySQL 5.7, WRITESET in MySQL 8.0) with slave_parallel_workers to increase replay threads.

Splitting large transactions into smaller ones.

Routing critical reads (e.g., immediate order‑status checks) to the master.

Enabling semi‑synchronous replication to guarantee at least one replica has received the binlog.

4. High Availability: What If the Master Crashes

Read‑write splitting solves read scaling but leaves the master as a single point of failure. HA aims to automatically promote a replica to master when the original master fails. The core actions are failure detection, master‑slave switchover, and routing update.

MHA (Master High Availability) : a dedicated manager monitors the master and promotes the most up‑to‑date replica; mature but adds another potential single point.

MGR + MySQL Router : native MySQL 8.0 solution; MGR provides multi‑primary strong consistency and automatic election, Router directs traffic to the current primary.

Orchestrator : open‑source topology manager from GitHub that visualizes and controls replication topology.

Cloud‑hosted RDS : managed MySQL services where HA is provided out‑of‑the‑box.

The trade‑off is between RPO (how much data loss is acceptable) and RTO (how long downtime is acceptable). Asynchronous replication offers fast switchover but non‑zero RPO; semi‑sync or MGR give stronger durability at the cost of slower failover.

5. Sharding: Vertical and Horizontal Partitioning

When a single table exceeds ~20 million rows or write pressure overwhelms the master, data must be split.

Vertical split : separate databases by business domain (e.g., orders, users, products) or split a wide table into a “skinny” main table and an auxiliary table holding infrequently used columns.

Horizontal split :

Horizontal table partitioning – create tables like order_0, order_1, … order_n with identical schema.

Horizontal database sharding – distribute those tables across different machines to spread write load.

The key is choosing an effective sharding key. Common strategies:

Modulo (e.g., user_id % shard_count) – uniform distribution but costly re‑balancing.

Range (e.g., by month) – easy to add new shards, but can create hot spots on the newest range.

Consistent hashing – minimizes data movement when adding shards.

A good sharding key is frequently used in queries and yields even data distribution; otherwise cross‑shard queries become expensive.

6. Problems Introduced by Sharding

Sharding eliminates a single‑node bottleneck but brings a suite of distributed challenges:

Distributed IDs : AUTO_INCREMENT can collide across shards. Solutions include Snowflake (64‑bit timestamp‑machine‑sequence), leaf‑style segment allocation, or Redis INCR. UUIDs are discouraged due to size and index fragmentation.

Cross‑database joins : must be rewritten as two‑step queries or avoided via data redundancy.

Cross‑shard pagination : deep pagination requires each shard to return top‑N rows, then merge and sort in the application; common mitigation is to limit page depth or use cursors.

Cross‑shard transactions : require XA/2PC (high overhead) or eventual‑consistency patterns such as TCC, local message tables, or transaction messages.

Cross‑shard aggregations : functions like COUNT, SUM, GROUP BY must be executed per shard and then aggregated.

Scaling shards : adding shards may require massive data migration for modulo‑based schemes; consistent hashing or pre‑sharding can reduce migration cost.

These drawbacks are why the author repeatedly warns against “premature sharding”.

7. Evolution Summary

The typical growth path for a MySQL service is:

Stage 1 – Optimize SQL and add indexes (single‑node performance).

Stage 2 – Add a Redis cache to offload most reads.

Stage 3 – Deploy read‑write splitting with one master and multiple replicas.

Stage 4 – Introduce master‑slave HA and automatic failover.

Stage 5 – Archive cold data to keep tables slim.

Stage 6 – Apply sharding (vertical then horizontal) as the final, most costly step.

Each step is driven by business growth; the later the step, the higher the operational cost and complexity. In an interview, explaining this progression and the trade‑offs of sharding demonstrates solid engineering judgment.