Running a Petabyte MySQL Fleet: Pods, Replica Read Consistency and 30-Minute Restores
Shard by tenant, move tenants with a log-tailing mover, route related reads to one replica, and back up with disk snapshots instead of file copies.
On this page
At small scale a database problem is a slow query. At Shopify’s scale it is a merchant who cannot take orders during a flash sale. The interesting part of their approach is how ordinary the techniques are: a sharding key, a log tailer, a hash and a snapshot, applied with discipline.
Pods: a shard plus a copy of the app
Each pod is an isolated copy of the core application and one MySQL shard. A request arrives at a load balancer that looks the shop up in a routing table and forwards it to that shop’s pod. This resembles a cell-based architecture: the cells are identical code, but each holds different tenants’ data.
The model works because nearly every table has a shop_id. That column is a natural sharding key, and it keeps one merchant’s data together, so queries rarely cross shards.
The problem: tenants are not equal
Over time some shards fill with large, busy shops and others sit nearly idle. Both are expensive:
- Over-used shards risk failure, and a flash sale makes it worse.
- Under-used shards waste money.
Even distribution by shop count does not work, because one shop can outweigh a hundred. The rebalancing decision is data driven, using:
| Input | Why it matters |
|---|---|
| Historical utilisation per shard | Shows who is actually hot, and when |
| Shop size | Large shops are harder and slower to move |
| Time to move | Fits moves into maintenance windows |
| Flash-sale patterns | A quiet shop may spike predictably |
Moving a shop with no downtime
The move must satisfy three constraints at once: the storefront stays available, no write is lost, and it finishes in reasonable time. The tool Shopify built and open-sourced, Ghostferry, works in three phases.
- Copy and tail, together
Iterate over each table, select that shop’s rows and write them to the target in small separate transactions. At the same moment, tail the source’s binary log, the authoritative record of every change, and replay changes on the target so late writes are not missed.
- Cut over briefly
When the replay queue is nearly empty, stop writes for that shop on the source, record the final binary log position as the stopping point, and replay the remaining events up to it. Source and target are now identical. This is the only moment of possible visible pause, so it is kept short.
- Switch routing, verify, prune
Update the routing table to point the shop at its new pod. Verify the move, and only then delete the shop’s stale data from the old pod.
This is the same snapshot-then-tail-the-log shape used by most online migrations, with the routing table as the final switch.
Keeping the old copy until verification passes costs a little disk and buys a rollback path. Pruning stale data is the very last step, never part of the cutover.
Reading from replicas without lying to users
Read replicas offload reads from the primary, but they lag. Time-sensitive reads, such as a user checking their just-saved profile, still go to the primary. The harder case is a request assembled from several reads.
Order for Item A and Item B
read inventory(A) -> replica 1 (up to date)
read inventory(B) -> replica 2 (lagging; still shows stock after B sold out)
Each answer is individually plausible, together they are inconsistent. Two textbook fixes were rejected:
| Option | Why Shopify did not use it |
|---|---|
| Tight consistency (every replica fully current) | Removes the point of replicas and lets one down replica fail writes |
| Causal consistency with transaction ids | Needs special software on every replica to report its position |
They chose monotonic read consistency: reads in a series follow a consistent timeline, even if that timeline is slightly behind. The mechanism is cheap:
- The application attaches a unique id to related requests, passed in a query comment as
consistent_read_id. - The database proxy extracts the id and hashes it to choose a replica.
- Every request with that id goes to the same replica, so successive reads never go backwards.
The trade-off, which they accepted, is that a replica outage can briefly break the guarantee for the requests that were pinned to it.
Backups that restore in minutes
The first design was conventional: run Percona XtraBackup, write files, archive them to cloud storage. It was robust and slow. Backing up a petabyte took long, and restoring one shard took over six hours, which meant a very high recovery time objective.
The redesign used what the platform already offered. The servers run on cloud VMs with persistent disks, and the platform can snapshot a disk at the block level.
Whatever storage you use, a backup you have not restored is a hope. Practise the restore.
Lessons you can use at any size
Do
Pick a shard key your data already has (tenant id), keep a routing table you can change, copy-and-tail for every move, route related reads together, and measure recovery time by actually restoring.
Don't
Demand perfectly consistent replicas, or assume a backup works because the job reports success. Both ideas fail at exactly the moment you need them.
Takeaway
Large-scale database operation is mostly three unglamorous capabilities done well: moving tenants live, reading from replicas without surprising users, and restoring fast. Tenant-keyed shards, a log-tailing mover, a hash that pins related reads, and storage-level snapshots are all simple, and together they are what makes zero visible downtime a routine property instead of a heroic one.
Frequently asked questions
Why shard a multi-tenant database by tenant id?
When almost every table belongs to one tenant, the tenant id is a natural shard key: one tenant's data stays together, queries rarely cross shards, and tenants can be moved as a unit. The risk is uneven tenant sizes, which is why shards need rebalancing.
What is replication lag and why does it cause bugs?
Replicas apply the primary's changes slightly later, so a read from a replica may not show a write that just happened. A user can save a change and not see it, or an app can combine reads from replicas at different points in time and get an inconsistent picture.
What is monotonic read consistency?
A guarantee that successive reads in a sequence never go backwards in time. You can achieve it cheaply by routing all related reads to the same replica, chosen by hashing a request-group id, so they see a consistent, if slightly stale, timeline.
Why are disk snapshots faster to restore than file-based backups?
A snapshot is a block-level copy managed by the storage layer, so restoring means creating a disk from it and starting the database on top, with no data to stream or replay at the file level. File-based backups must be downloaded, unpacked and often prepared, which scales with data size.

/* Comments */
Comments are offline right now — we reconnect automatically, nothing is lost.