System design practice

Read Replicas

medium Based on a system published by Shopify replication read-heavy real-world

Shopify's MySQL: reads spread over replicas behind ProxySQL, writes and fresh reads on the primary.

Solve it in your browser Read the lesson first

Shopify runs on MySQL. For years its replicas were there only for redundancy and failover; then Shopify began reading from them, to serve read-heavy applications faster and to take load off the primaries, which are needed for writes and time-sensitive reads. Applications reach MySQL through ProxySQL, a proxy that groups interchangeable servers and picks one for each query, so the application never needs to know the topology.

Replicas copy the primary asynchronously, so a replica can be seconds (or more) behind. The design question is which reads may go to a replica and how many replicas the read load needs.

Functional requirements

Every query goes from app through proxy to MySQL. Use these use case names exactly: the traffic, requirements and tests in problem.proschi refer to them.

Scale

Constraints

What is given

problem.proschi declares app, Shopify's application servers (the client of this problem), proxy, three ProxySQL instances, and primary, the MySQL primary with a standby that takes over if it fails. Add the replica pool as one MySQL node, connect it, and write the three use cases.

What this model leaves out

Based on

How your design is checked

You write the design as text in Proschi. Tests run in your browser: a simulation of the traffic above checks latency, availability, cost and what happens when a machine fails. How the simulation works.

Start designing

Lesson · 13 min read

Learn it: Read Replicas #

Read Replicas: scaling reads without touching the writes #

What you'll learn #

The problem, explained #

Shopify runs on MySQL. For a long time its replicas existed for redundancy: if a primary failed, a replica took over. Then Shopify started reading from them, to make read-heavy applications faster and to take load off the primaries, which are needed for writes and for reads that cannot be out of date. Applications reach MySQL through ProxySQL, a proxy that groups interchangeable servers and picks one for each query, so the application never needs to know how many replicas there are or where they live.

Three use cases:

The rates are assumptions for the exercise (Shopify does not publish them): 60k catalog reads, 3k orders and 2k order checks a second at peak. The limits are a p99 under 40 ms for catalog reads and under 80 ms for the other two, 99.95% availability, surviving the loss of any one machine, and at most $3,400 a month.

What is given. app is the application (the client), proxy is three ProxySQL instances, and primary is the MySQL primary with a standby ready to take over. You add the replica pool as one MySQL node, connect it, and write the three use cases.

What the tests check: every use case starts at app and goes through proxy before any database, and there is no path from app to a database; catalog reads reach a database in every scenario but never the primary; orders are written to the primary before the answer, and order checks always read the primary.

Deep dive What the model leaves out

The simulation does not model replication lag: its replicas are always current. That is why the rule "fresh reads go to the primary" is a flow test rather than a number. It also does not model which replica answers a given query, so monotonic reads are taught here but not graded.

Back-of-the-envelope #

The simulation's figures for a MySQL server are 20k reads and 5k writes a second, $400 a month. MySQL is single-primary: every server in a replica set can serve reads, but writes go to the one primary.

catalog reads
60k rps
writes, 60% of one primary
3k rps
primary load if it serves every read
155%
replicas, one of them spare
5
monthly cost
$3,100
QuantityArithmeticResult
Reads60k catalog + 2k order checks62k rps
Writesgiven3k rps
Read:write ratio62k : 3kabout 20 : 1
Primary write load3k ÷ 5k60%
Primary and standby, read capacity2 × 20k40k rps
Every read on the primary62k ÷ 40k155%: saturated
Replicas at 100%60k ÷ 20k3
Replicas at 75%60k ÷ 15k4
Replicas at 75% with one down4 + 15
Cost5 × $400 + 2 × $400 + 3 × $100$3,100

The ratio says where the work is: the writes already fit on one primary, so this is a read problem. A primary and its standby serve about 40k reads a second between them; the catalog alone needs half as much again. Moving the catalog to replicas leaves the primary with 3k writes and 2k order checks. The simulation reports it at 60%: for a single-primary store it shows the busier side, here the writes (3k of 5k), while the 2k reads use 5% of the 40k.

Why 75%? A busy server queues. At 75% each read waits a little, and the p99 stays comfortably under 40 ms. At 100% the queue grows without limit and every latency limit breaks. So the pool must stay below about 75% on its worst day, which is the day a replica is down.

Budget. The proxies and the primary cost $1,100, which leaves $2,300 for replicas: five fit, six ($2,400) do not.

Concepts #

Replicas scale reads, not writes #

A read replica holds a full copy of the database and applies every change the primary makes, in the same order. Any replica can therefore answer any read, and adding one adds a whole server's worth of read capacity. No change to the schema or the queries is needed: the data model stays one database.

The cost is that every replica also applies every write. Replicas cannot share the write load: three replicas each do all of the primary's writes. So replicas are the right tool exactly when reads dominate and the writes fit on one primary, which is the common case for storefronts, catalogs, feeds and dashboards.

Sizing the replica pool #

Size the pool in three steps. First, the load: 60k catalog reads a second. Second, the target utilisation: about 75%, so each replica carries 15k. Third, the failure: one replica may be down at any time (a crash, a restart, a rebuild after a disk failure, a planned upgrade), so the pool needs one more than the load requires. That is N + 1: four replicas to carry the load, one spare.

The spare is not idle. All five share the load at 60% each, and when one fails the other four rise to 75%. With only four, a failure leaves three at 100%, and the pool saturates during the very event it was supposed to absorb.

ReplicasNormal loadOne down
3100%150%
475%100%
560%75%
650%60%

Six would also work, at a higher cost; the budget here rules it out.

Replication lag and which reads can tolerate it #

MySQL replication is asynchronous: the primary commits, answers the client, and ships the change to the replicas afterwards. A replica is therefore always slightly behind: usually by a fraction of a second, but, as Shopify's post notes, sometimes by seconds or even minutes.

So the question for every read is: what happens if it sees data a few seconds old?

The primary has room for such reads because the replicas took the bulk away. That is the whole point: Shopify's post describes the primaries as needed for writes and time-sensitive reads, and the replicas as a way to keep load off them.

Deep dive Other ways to get read-your-writes

Reading from the primary is the simplest fix. Others: route a user's reads to the primary only for a short window after they write; or remember the position of the user's last write and read from a replica only once it has applied that position. MySQL's global transaction ids (GTIDs) make the second possible. Shopify considered it and set it aside: it needs software on every replica that reports its position to the proxy, and Shopify's use cases did not need a guarantee that strong.

Monotonic reads: one replica per series of reads #

Lag causes a subtler problem too. Suppose a background job runs several related queries, and the proxy sends each to a random replica. One replica is 1 second behind, another 10 seconds. The job may see a row in its first query that is missing from a related table in its second, or see time go backwards between two reads. If the job then writes based on what it read, the inconsistency spreads.

The guarantee that prevents this is monotonic reads: successive reads never move backwards in time, even if they are not the latest. Shopify considered stronger options (waiting until every replica caught up, which removes the benefit of replicas; and GTID-based causal consistency) and chose monotonic reads as enough for its uses.

The mechanism is simple. An application that wants it tags its related queries with a shared id, a UUID for the job, in a SQL comment (consistent_read_id). ProxySQL hashes the id and takes it modulo the number of servers: the same id always lands on the same replica, so the job reads from one timeline. The application still knows nothing about the topology.

Two lessons came out of running it:

The weakness that remains: if a replica goes down between two reads of a series, that series switches replica and may see an inconsistency. Shopify accepts that for applications that tolerate the occasional failure.

A proxy in front of the database #

The app could connect to each replica itself, but then every application server must know the list of replicas, notice when one fails, and be redeployed when one is added. A proxy keeps that in one place: it groups interchangeable servers, picks one for each query, and stops sending to a server that is down. Adding a replica becomes a proxy configuration change. It is also where routing rules live, such as "writes to the primary" and "hash this id to one replica".

Replicas or shards? #

Sharding splits the data across several primaries, each with its own copy of part of the data. It is the only way to add write capacity to a single-primary database, and it is expensive in complexity: every query must name its shard, and queries across shards become fan-outs.

Here the writes need 60% of one primary. Four shards would add three primaries nobody needs, each with its own standby for failover, and the reads still would not have a home that keeps them off the primaries. Shard when the write rate outgrows one primary (the Notion problem); until then, replicas.

Designing it step by step #

1. Scope the problem #

Ask for the read and write rates (60k reads, 3k writes, 2k order checks a second). Ask which reads must see the latest data: the order status page must; the catalog may be a few seconds old. Ask whether a cache is in scope (not here; the catalog is read from MySQL).

2. High-level design #

The flow for every use case is app → proxy → MySQL. Only the last hop differs:

Use caseGoes toWhy
Read catalogreplicasBulk of the load, tolerates lag
Place orderprimaryWrites go to the one primary
Check orderprimaryMust see the order just written

Add the replica pool as one MySQL node and connect the primary to it (replication), the proxy to both.

3. Deep dive #

Pool size. Five replicas: four carry the load at 75%, one is the spare. Confirm in the analysis that losing one keeps the catalog's p99 under 40 ms.

The primary. 3k writes at 60%, plus 2k order checks; it is given with a standby, so a primary failure promotes the standby.

Consistency. State which reads are routed where and why. If the interviewer asks about related reads seeing different replicas, explain monotonic reads by hashing.

4. Wrap-up #

Summarise: replicas for the reads that tolerate lag, the primary for writes and fresh reads, N + 1 sizing, the proxy hiding the topology. Name the next steps: monitoring replica lag and taking a far-behind replica out of the pool, a cache for the hottest catalog pages, and sharding once writes approach the primary's limit.

Common mistakes #

Every read on the primary (wrong/all-reads-on-primary.proschi). No replicas: the primary and its standby get 62k reads a second against about 40k of capacity, so the database saturates. It fails the catalog's p99 and "Catalog reads go to the replicas", and, because the same saturated server is on every path, the other latency limits and the failure test too.

No room for a failure (wrong/no-room-for-a-failure.proschi). Four replicas run at 75% and pass every latency limit, until one is lost and three must carry 60k reads at 100%. It fails survive any node failure. Size for the worst day, not the average one.

Reading a fresh write from a replica (wrong/fresh-read-from-a-replica.proschi). All reads go to the replicas, including the order status page. The numbers pass, which is what makes the mistake dangerous: in production a lagging replica tells a paying customer their order is missing. It fails "Writes and time-sensitive reads go to the primary".

Sharding for reads (wrong/shard-for-reads.proschi). Four shards of the primary, no replicas. There is enough read capacity, but it takes eight servers ($3,200, plus the proxies: $3,500), breaks the budget, and puts the catalog back on the primaries. It fails "Catalog reads go to the replicas" and the cost limit. It fails the failure test too: when one server of a shard is lost, its partner takes that shard's reads alone and the catalog's p99 breaks.

In the interview #

Open with the ratio, then the classification of reads: those two sentences carry most of the design.

Expected follow-ups:

Further reading #

Now design it

Review cards for this problem

Short questions on the ideas it needs, from the review cards. Review them daily so they stick.

More system design problems