How do you scale MySQL when traffic grows 100x?

Question

IT Interview Practice · Accepted Answer

Scale in stages: **read replicas + caching for reads first, then sharding (Vitess) for writes** — and add connection pooling before anything else, because it's the cheapest 10x. Reads and writes scale differently, so don't reach for sharding on day one.

```text
        ┌─▶ Cache (Redis) ── hit ──▶ return
App ─▶ Pool(PgBouncer/ProxySQL)      miss ▼
        │                    ┌─▶ Read replica 1 ┐
        ├──── reads ─────────┼─▶ Read replica 2 ┼─ (async replication)
        │                    └─▶ Read replica N ┘
        └──── writes ──▶ Primary ──▶ (later) Vitess shards ─▶ Primary A / Primary B
```

## Step 0: connection pooling

Every MySQL connection costs memory and a thread. At 100x traffic you'll exhaust `max_connections` long before CPU. A pooler like **ProxySQL** or **PgBouncer** multiplexes thousands of app connections onto a few hundred DB connections. It's a config change, not a rearchitecture — do it first.

## Step 1: cache the reads

Most workloads are read-heavy (often 90%+). Put **Redis** in front for hot rows — user profiles, product pages. A cache hit never touches MySQL. Use cache-aside: read cache → miss → read DB → populate cache. This alone can absorb the majority of the 100x.

## Step 2: read replicas

Add **async read replicas** and route SELECTs to them, writes to the primary. This scales reads horizontally: N replicas ≈ N× read capacity. The catch is **replication lag** — a replica may be milliseconds-to-seconds behind, so read-your-own-writes needs care (route that user's reads to the primary, or read from cache).

## Step 3: shard the writes

Replicas don't help writes — every write still hits the single primary. When write throughput saturates one box, **shard**. **Vitess** (built at YouTube) sits in front of MySQL and does sharding, query routing, and online resharding without rewriting the app. Shard by `user_id` so most queries stay single-shard.

## Interview perspective

Interviewers watch for **ordering**: candidates who immediately shard reveal they don't know replicas/caching solve read scaling far more cheaply. Good: "add replicas and a cache." Great: you separate the read path from the write path, name replication lag as the cost of replicas, and only introduce sharding when *writes* — not reads — are the bottleneck.

**Common trap:** "just add read replicas" when the actual bottleneck is write throughput. Replicas make that *worse* (more replication load on the primary).

## Trade-offs & failure modes

- **Replication lag:** stale reads; pin critical reads to primary or use semi-sync replication.
- **Cache stampede:** many misses hit DB at once on expiry — use locks or staggered TTLs.
- **Sharding complexity:** loses cross-shard joins and transactions; only pay this once writes force it.
- **Failover:** primary death needs automated promotion (Orchestrator); a stale promoted replica can lose recent writes.

Step 0: connection pooling

Step 1: cache the reads

Step 2: read replicas

Step 3: shard the writes

Interview perspective

Trade-offs & failure modes

Related questions