---
topic: system-design
author: Crashtech Editorial
published: Nov 14, 2026 · read: 5 min
---

Scaling MySQL Without Leaving MySQL: A Proxy Layer for Pooling, Routing and Safety

Replica reads, replication lag and cache priming, sharding without app rewrites, query guards and automatic failover, as a MySQL scaling layer provides them.

– –

Every successful application passes through the same database stages. One server, then replicas, then a split, then shards. The expensive part is not any one stage, it is the amount of routing logic that leaks into application code on the way. A proxy layer puts that logic where it belongs.

Stage 1: one database, then trouble

A single MySQL and a few web servers is simple, well understood and fast to build on. Success brings the usual list of problems: slow queries on growing tables, downtime for backups and upgrades, a single point of failure, and distance from users in other regions.

Stage 2: reads from replicas

Add copies of the primary kept up to date by asynchronous replication, and send read traffic to them. Load spreads out and redundancy improves. The cost is staleness: a replica may lag the primary by seconds, so a user who just changed a setting might not see it.

The practical rule is to classify reads.

Read typeWhere it goesExample
Freshness-tolerantReplicaShowing a video page, listing items, approximate counts
Freshness-criticalPrimaryThe user’s own profile right after an edit, anything feeding a decision

Not all reads are equal, and treating them equally either overloads the primary or confuses users.

Stage 3: writes outgrow replication

Heavy writes expose another limit. Traditional MySQL replication applies changes on a single thread, so replicas fall behind exactly when write rates are highest, and stale reads multiply.

Prime the cache, not the CPU

YouTube’s workaround, Prime Cache, reads the replica’s relay log ahead of the applier, inspects the WHERE clauses of upcoming writes and loads the relevant rows into memory first. The single applier thread then finds its data already cached, so replication runs from memory instead of waiting on disk. It is a bridge that postpones sharding, not a replacement for it.

Stage 4: split and shard

Vertical split by table group
Move related tables, such as user profiles and video metadata, to separate databases that scale and fail independently. Easy, but it ends where one group alone gets too big.
Sharding by key
Divide one table’s rows across databases by a key such as user id. Reads and writes spread over many machines.

Sharding’s bill arrives in application code and in guarantees:

  • Transactions across shards are hard, so atomicity across them is often given up.
  • Queries that span shards, or join across them, need special handling.
  • Clients must decide: replica or primary, which shard from the WHERE clause, and how to maintain cross-shard indexes.

That is the point where teams are tempted to embed routing in every service, and where a routing layer pays for itself.

Stage 5: the proxy layer

Two components do the work.

ComponentRole
VTGateThe application’s single entry point. Parses the SQL, decides which shard or shards it touches, fans it out, merges results. Applications do not need to know the topology
VTTabletA lightweight proxy in front of each MySQL instance, enforcing safety and efficiency

What VTTablet adds in front of MySQL:

  • Connection pooling. Each MySQL connection costs memory. With tens of thousands of client requests, direct connections would exhaust the server. A small pool serves many app connections.
  • Query safety. Statements missing a LIMIT, or likely to return huge result sets, can be blocked or rewritten. Long-running or runaway queries are killed.
  • Row cache with invalidation. Hot rows are served from memory and invalidated on change, without hitting MySQL each time.
  • Query de-duplication. Identical in-flight queries (a hot row under load) can share one result instead of all executing.
Advertisement

Resharding without a weekend

Splitting a shard that has become too big or too hot is automated:

  1. Declare the split

    An operator chooses to split one shard into several, say four or sixteen.

  2. Background copy

    New MySQL instances are created with the schema, and data is copied while the old shard keeps serving. Changes during the copy are streamed across.

  3. Validate

    Tools report progress and verify that source and targets match.

  4. Switch traffic

    When ready, the operator authorises the cutover. The routing metadata changes, and queries start going to the new shards.

  5. Retire the old shard

    Only after confirmation is the original removed.

Operations at thousands of instances

Reparenting. When a primary fails or needs maintenance, someone must identify the failure, pick a replica, promote it, repoint the other replicas and reroute traffic. Each step is seconds, the total is minutes, and one mistake corrupts data. An orchestration layer with a lock service does the steps in order, so failover becomes a routine, repeatable action.

Backups. Tablets can take backups from replicas without interrupting service, scheduled and managed automatically, so recovery is a procedure rather than an investigation.

What to take from it

LessonPractice
Keep the engine, change the layerHide topology behind a router so apps keep using SQL
Classify readsReplica for tolerant, primary for critical
Guard the databasePooling, LIMIT enforcement and timeouts belong in a proxy, not in each app
Automate the dangerous stepsResharding, failover and backup should be tested workflows
Know what you gave upCross-shard transactions and joins become your design constraints

Do

Reach for a routing layer when more than one service needs shard-aware logic, set explicit staleness rules per read, and rehearse failover and resharding in staging before relying on them.

Don't

Shard early because it sounds scalable. If replicas, indexing and a vertical split still buy years, they cost far less than the distributed-transactions problem you are importing.

Takeaway

MySQL can scale a very long way if the layer in front of it takes over routing, pooling, safety and operations. Use replicas with a clear freshness rule, prime replication when writes spike, split vertically before you shard, and let a proxy layer own the topology so applications do not have to.

Advertisement

Frequently asked questions

What is Vitess?

An open-source layer on top of MySQL that adds query routing, connection pooling, sharding, online resharding and automated failover while keeping MySQL as the storage engine. It started at YouTube so applications could keep using a familiar database while scaling horizontally.

Why does MySQL replication lag under heavy writes?

Traditionally replicas apply changes from the primary's log on a single thread, so a replica cannot absorb writes as fast as a busy primary produces them, and falls behind. Lag means stale reads. Parallel apply and priming the replica's cache with rows the next writes will touch both help.

Which reads can safely go to replicas?

Reads that tolerate a few seconds of staleness, such as displaying content or approximate counts. Reads that must reflect the user's own latest change, such as account settings right after an update, should go to the primary or be pinned to a consistent source.

What does reparenting mean?

Promoting a replica to primary and repointing the other replicas and traffic to it, when the primary fails or must be taken out. Doing this by hand across hundreds of clusters invites mistakes, so mature systems automate detection, promotion, replica reconfiguration and routing.

Sources & further reading

/* Comments */