Skip to content
Curiosity
#scaling #replication #caching #architecture

Scaling MySQL before sharding

Sharding adds an order of magnitude of complexity. Exhaust the cheaper options first — split the DB onto its own host, add a cache, add read replicas, scale the box up — and only shard when those run out.

Updated May 24, 2026
On this page

Sharding — partitioning data across multiple independent database servers — works, but it permanently changes how every part of the system behaves: queries can’t JOIN across shards, transactions can’t span them, schema changes have to be rolled out N times, and rebalancing is a multi-day operation. Before paying that complexity tax, work through the cheaper levers below in order. Most workloads never need to shard.

The pre-sharding ladder

  1. Move the DB onto its own host

    If the app and database share a box, the database is competing with the application for CPU, memory, and disk I/O. Moving it to its own machine is the smallest possible change with measurable wins on every dimension — and it adds zero application-side complexity. The tables stay intact; the connection string changes.

  2. Add a cache in front of read-heavy paths

    If read latency is the bottleneck, an in-memory cache (Redis, Memcached) absorbs the hottest queries before they hit MySQL. The right shape depends on the access pattern: per-row cache by primary key, per-query cache by serialized arguments, or a derived view materialized into the cache. The cost is cache-invalidation discipline, but the latency win is typically 10–100×.

  3. Add one or more read replicas

    Asynchronous replication copies writes from a primary to N replicas. Writes still go to the primary; reads can be routed to any replica. Two costs: extra hardware, and replication lag — a read on a replica may not see a write that just succeeded on the primary. Code that reads-after-write must either pin to the primary or tolerate the lag.

  4. Scale up the box

    A larger instance with more RAM and faster storage usually outperforms the architectural rewrites it would let you avoid. RAM that fits the working set turns disk-bound queries into memory-bound ones; NVMe storage turns IOPS-bound workloads into CPU-bound ones. Vertical scaling has a ceiling, but the ceiling is high — a db.r7g.16xlarge is 512 GB RAM, which is more than most application databases ever need.

The decision

Decision
Chose: Walk the ladder in order, only shard when the top four are exhausted
2026-05-24

Each rung adds at most one new failure mode and is reversible. Sharding multiplies failure modes and is, in practice, irreversible. The cost ordering on the application side is monotonic — move the DB, then cache, then replicate, then scale up, then shard.

Separate DB host
Pros
  • Trivial change
  • No app code changes
  • Frees app server resources
Cons
  • Adds one network hop
  • Doesn't help if the DB is the bottleneck on its own
Add a cache
Pros
  • 10–100× latency win for hot reads
  • Absorbs traffic spikes
  • Independent of DB shape
Cons
  • Invalidation complexity
  • Stale-read failure modes
  • Doesn't help write-heavy paths
Read replicas
Pros
  • Linear read scaling per replica
  • Hot standby for failover
  • No application rewrite for read-only queries
Cons
  • Replication lag breaks read-your-writes
  • Hardware cost grows linearly
  • Doesn't help write throughput
Vertical scale
Pros
  • Highest performance gain per unit of complexity
  • Same operational shape
  • Working set fits in RAM
Cons
  • Hard ceiling
  • Cost grows non-linearly at the top end
  • Doesn't help if a single query is the problem
Shard
Pros
  • Effectively unbounded horizontal scale
  • Each shard stays a normal MySQL
Cons
  • Cross-shard joins and transactions become hard or impossible
  • Rebalancing is an event
  • Every schema change runs N times
  • Routing layer is a single point of complexity

When sharding really is the answer

A workload genuinely needs sharding when a single write-master can no longer keep up even on the largest instance, and the dataset cannot be partitioned along a natural domain boundary that would let you split into independent databases. Concrete signals:

  • The primary is CPU-saturated at peak and the queries are already index-optimal (see 0007 EXPLAIN · MySQL ).
  • Storage on the primary is at the instance limit and growing.
  • Replication lag is chronic because the replica can’t apply binlogs as fast as the primary writes them.
  • Read replicas help with read throughput but the write workload alone is enough to saturate the primary.

If the issue is a single hot table, functional partitioning (splitting by domain — orders, users, billing into separate databases) is dramatically simpler than range/hash sharding and often sufficient. Reach for full sharding only when the hot table itself is too big for a single primary.

What to remember

Sharding is a one-way door — once the data is partitioned, every operation pays the routing tax. Walk the ladder: separate host, cache, replicas, scale up. Only shard when a single write primary is the actual bottleneck, and even then prefer functional partitioning by domain over hash sharding when the data shape allows it. The cheaper levers compound — caching + replicas + a big box is most workloads’ permanent answer.

Related