Mitigating MySQL Replication Lag and Reducing Read Load at Github

Ideas you will meet

Replication

Leader/follower, multi-leader and leaderless replication.

Databases

Schemas, indexes, query plans and choosing a store.

What we already know about this

Our own lessons on these ideas. Read them here before you start, so it makes sense.

• Databases Deep Dive · 12 minReplication topologies and multi-region databasesSingle-leader, multi-leader and leaderless replication, how they handle conflicts and lag, and what changes across regions.Read hereClose

  Why replicate

  Replication keeps copies of data on several machines for three reasons: availability (survive a node failure), read scaling (serve reads from replicas) and latency (put data near users). The hard part is keeping copies consistent while writes continue. (Basics are in the replication and sharding lesson of the foundations course; this lesson compares the topologies.)

  Single-leader (primary and replicas)

  One node, the leader (primary or master), accepts writes and ships its changes to followers (replicas), which serve reads.

  100%

  Figure SDA-DDV-04-1 · Single-leader replication · © forcsguy.com

  • Synchronous replication waits for a follower before acknowledging: safer, slower, and a stalled follower can block writes. Asynchronous is faster but a failover can lose the latest writes. A common compromise: one synchronous follower, others asynchronous.
  • Failover must detect failure, choose the most up-to-date replica, redirect clients and ensure the old leader does not come back believing it still leads (split brain).
  • Replication lag causes stale reads. Techniques: read-your-writes (route a user's reads to the leader shortly after they write), monotonic reads (keep a user on one replica), and checking replica position.

  Multi-leader

  Several nodes accept writes (often one per region) and replicate to each other. Benefits: local write latency and continued operation if a region is cut off. Cost: write conflicts.

  Conflict handling options:

  • Avoid them: route all writes for one record to one leader (for example by user or region).
  • Last write wins by timestamp: simple, but silently drops one of two concurrent updates and depends on clocks.
  • Merge values (union of sets, counters) using mergeable data types (CRDTs; see the collaborative editing lesson).
  • Custom resolution in application code, or ask the user.

  Multi-leader also appears in offline-capable apps (each device is a leader) and collaborative editing.

  Before: single leader

  100%

  After: multi-leader across regions

  100%

  What changed

  • Users in each region write to a nearby database
  • Concurrent edits to the same row need conflict handling
  • More moving parts to run and monitor

  Leaderless (Dynamo-style)

  Any replica accepts writes; clients write to several replicas and read from several, using quorums (W + R > N) to get fresh data (see the key-value store lesson). Background anti-entropy and read repair fix stale copies; versions or vector clocks detect conflicts. It favours availability and is typical of Cassandra and DynamoDB-style stores.

  100%

  Figure SDA-DDV-04-2 · Choosing a replication model · © forcsguy.com

  Multi-region designs

  • Active-passive: one region serves writes; another is a warm standby. Simple; failover loses a little data (the replication lag) and takes minutes.
  • Active-active: several regions serve traffic. Requires either partitioning data by home region (each record has one home, cross-region reads are replicas) or true multi-leader conflict handling.
  • Global databases use consensus across regions (see the consensus lesson) for strong consistency at the cost of cross-region write latency; some offer tunable consistency per query.
  • Latency numbers matter: a cross-ocean round trip is around 100 ms or more, so synchronous cross-region replication adds that to every write.
  • Data residency laws may require keeping certain users' data in a region.

  Partitioning and replication together

  Real systems shard data into partitions and replicate each partition (a leader per partition spreads write load across nodes). Rebalancing, hot partitions and cross-partition transactions are the new problems (see sharding and the consistent-hashing lesson).

  Master/slave naming

  You will see "master/slave", "primary/replica" and "leader/follower" for the same single-leader idea; most current documentation prefers the last two.

  Checklist

  • What happens to writes if the leader or a region is lost, and how long?
  • How much data can we lose (RPO) and how long can we be down (RTO)?
  • How do we handle stale reads from replicas?
  • Who resolves conflicts, and how?
  • Have we practised failover?

  Real-world reading

  Related reading

  • Active-Active for Multi-Regional ResiliencyNetflix Engineering · NetflixCase Studies
  • Designing Data-Intensive ApplicationsMartin Kleppmann · O'ReillyBooks
  • Read Consistency with Database Replicas at ShopifyShopify engineers · ShopifyCase Studies
  • Uber Schemaless: A Scalable MySQL DatastoreUber Engineering · UberCase Studies

  Show 6 moreShow fewer

  • Mitigating MySQL Replication Lag and Reducing Read Load at GithubGithub engineers · GithubCase Studies
  • Cassandra architecture overviewApache Cassandra · Apache Software FoundationDocumentation
  • How Discord Stores Trillions of MessagesBo Ingram · DiscordBlogs
  • Spanner: Google's Globally Distributed Database (paper)Corbett et al. · GooglePapers
  • Scaling Multitenant Architecture Across Multiple Data Centres at Shopify - Weingarten, Engineering Lead at ShopifyShopifyYouTube
  • MIT 6.824: Distributed SystemsRobert Morris · MITCourses

  Open this lesson with its quiz and exercise

• System Design Foundations · 11 minReplication and shardingTwo ways to spread data over many machines: copy it for safety and read speed, or split it for capacity.Read hereClose

  Replication: keep copies

  Replication keeps the same data on more than one machine. Reasons:

  • Availability: if one machine dies, another has the data.
  • Read capacity: reads can go to any copy.
  • Locality: place a copy near users in another region.

  The most common arrangement is leader and followers. All writes go to the leader, which sends the changes to followers. Reads can use followers.

  100%

  Figure SDA-FND-05-1 · Leader and follower replication · © forcsguy.com

  Many teams hide the split between the primary and the replicas behind a small router, so the application does not need to know:

  100%

  Figure SDA-FND-05-2 · Read and write splitting through a router · © forcsguy.com

  Synchronous or asynchronous

  • Synchronous: the leader waits for followers to confirm before saying "done". Safer, but slower, and a down follower can block writes.
  • Asynchronous: the leader confirms immediately and followers catch up. Fast, but a follower can be behind, and a leader crash can lose the most recent writes.

  Most systems use asynchronous replication for most followers, sometimes with one synchronous follower as a safety net.

  Replication lag

  Because followers can lag, a user may write something and then read an older value from a follower. Fixes include reading the user's own recent writes from the leader, or tracking how fresh each replica is. This is the first taste of a bigger idea, consistency, covered in lesson 7.

  When the leader fails

  A follower must be promoted (failover). That needs failure detection and agreement on who the new leader is, so that two machines do not both believe they are the leader (a split brain). This is why teams rely on tested tooling or managed databases for failover rather than hand-written scripts.

  Read-after-write consistency

  With asynchronous replicas, a user can save something and then not see it: the write went to the primary, the next read hit a replica that is a few seconds behind. Three common fixes:

  • Send reads that must be fresh (the user's own profile, an order just placed) to the primary.
  • For a short window after a user writes, route that user's reads to the primary.
  • Let the router check whether a replica has caught up to the user's last write; if not, read from the primary or wait.

  A database proxy in front of the cluster can do this routing so application code stays simple, at the cost of one more hop and one more component that must itself be highly available.

  Before: one database

  100%

  After: primary with read replicas

  100%

  What changed

  • Reads are spread across copies
  • A copy can be promoted if the primary fails
  • Replicas may lag slightly behind the primary

  Sharding: split the data

  Replication does not help when the data or the writes no longer fit on one machine. Sharding (partitioning) divides the data so each shard holds a part. Each shard is usually replicated too.

  100%

  Figure SDA-FND-05-3 · Sharding by key range · © forcsguy.com

  Choosing a shard key

  The shard key decides which shard owns a row. A good key:

  • Spreads data evenly, so no shard is much bigger than the others.
  • Spreads traffic evenly, so no shard is a hot spot.
  • Matches your queries, so most queries touch one shard.

  Common schemes:

  Scheme	How	Watch out for
  Range	key ranges per shard	hot spots, e.g. newest dates
  Hash	hash(key) mod N	adding shards remaps most keys
  Directory	a lookup table says where each key lives	the directory is another thing to run and scale
  Consistent hashing	keys and nodes placed on a ring	needs virtual nodes to balance well

  Consistent hashing in one picture

  Place both servers and keys on a ring. A key belongs to the first server clockwise from it. Adding a server only takes keys from its neighbour; the rest do not move.

  100%

  Figure SDA-FND-05-4 · Consistent hashing ring · © forcsguy.com

  Horizontal and vertical partitioning

  Sharding is horizontal partitioning: the rows of a table are split across machines, each shard holding the same columns for a different set of rows. Vertical partitioning splits by columns or by feature instead: rarely used or large columns (a profile photo, a long description) move to a separate table or store, or whole groups of tables go to different databases (users on one, orders on another). It is simpler than sharding and keeps frequently read data small and fast, but it does not help when a single table's rows outgrow one machine.

  100%

  Figure SDA-FND-05-5 · Splitting a table by rows and by columns · © forcsguy.com

  What sharding costs you

  • Cross-shard queries and joins become slow or need extra work.
  • Transactions across shards are hard (lesson 8 touches on how systems cope).
  • Rebalancing when a shard gets too big is operational work.
  • More moving parts to monitor.

  A worked example: sizing replicas and shards

  Replicas for reads. A social app has 20,000 reads and 1,000 writes per second. One database node can do about 5,000 simple operations per second. A primary alone is hopeless. With writes of 1,000 per second on the primary, you have capacity left for about 4,000 reads, so you need at least four replicas to serve 20,000 reads (16,000 on replicas plus 4,000 on the primary) with no headroom, so six in practice. Adding replicas scales reads almost linearly. It does nothing for the 1,000 writes, which still all go to one primary.

  Shards for writes. If writes grow to 12,000 per second, no single primary can take them. Split the data into shards, each with its own primary: with 4 shards each takes about 3,000 writes per second. Each shard then has its own replicas for reads. Your final count is shards x (1 primary + replicas), for example 4 x (1 + 2) = 12 database nodes.

  100%

  Figure SDA-FND-05-6 · Replicas scale reads, shards scale writes · © forcsguy.com

  Walking through a failover

  Replication is only useful if you know what happens when a primary dies. With asynchronous replication:

  1. The primary acknowledges writes 100 to 140, but the follower has only received up to 135 (replication lag).
  2. The primary crashes. Writes 136 to 140 exist only on the dead machine.
  3. A monitor notices missing heartbeats, usually after several seconds, and promotes the most up-to-date follower to primary.
  4. Clients are redirected. Writes 136 to 140 are lost (or recoverable only when the old primary returns).
  5. When the old primary comes back it must not accept writes as if nothing happened (that would create two primaries, "split brain"); it rejoins as a follower and discards or reconciles what the new primary does not have.

  With synchronous replication to at least one follower, step 2 loses nothing, but every write waits for that follower, adding latency and making writes fail if the follower is unavailable. Many systems choose a compromise: wait for one follower to confirm (semi-synchronous), accepting a little latency to avoid losing acknowledged writes.

  100%

  Figure SDA-FND-05-7 · What an asynchronous failover can lose · © forcsguy.com

  Choosing a shard key: three examples

  • User id for a messaging app. Almost every query is "this user's messages", so a hash of the user id keeps each user's data together and spreads users evenly. The pain: a query across all users (an admin search) must ask every shard.
  • Order date for an orders table. Range sharding by date makes "last month's orders" cheap, but all new writes land on the newest shard, a hot spot, while old shards sit idle. A better key adds a hash of the customer id.
  • Country for a multi-region product. Good for keeping data near users and meeting data-residency rules, but shards are uneven: one country may hold half your users.

  A good key has high cardinality (many distinct values), even load and matches your most common query. Changing it later means moving most of your data, so test it against a sample of real traffic first.

  Mistakes people make

  • Reading from a replica right after writing. The replica may not have the write yet, so users do not see their own change. Read your own writes from the primary for a short time, or route that user to the primary.
  • Sharding too early. Sharding multiplies operational work: backups, schema changes, cross-shard queries and transactions all get harder. First try indexes, caching, replicas and a bigger machine.
  • Counting on replicas as backups. A mistaken delete is replicated within milliseconds. Keep real backups and test restoring them.
  • Forgetting to rebalance. Shards grow unevenly. Plan how to split a shard, ideally with consistent hashing or many small virtual shards that you can move between machines.

  Try it yourself

  In the System Builder, open "The database goes down". Run it with no replicas and see reads and writes fail, then add replicas and a write queue and compare. For hashing itself, build the consistent hash ring lab and measure how many keys move when you add a node. To place replicas and a queue yourself, open the database-outage design on the free canvas and kill the database.

  More on this site

  Related reading

  • How Discord Stores Trillions of MessagesBo Ingram · DiscordBlogs
  • MongoDB: Shard KeysMongoDB · MongoDB Inc.Documentation
  • Uber Schemaless: A Scalable MySQL DatastoreUber Engineering · UberCase Studies
  • How Discord Stores Billions of MessagesDiscordCase Studies

  Show 6 moreShow fewer

  • Consistent Hashing and Random Trees (paper)Karger et al. · MITPapers
  • Amazon Dynamo: Highly Available Key-Value StoreWerner Vogels · AmazonCase Studies
  • Mitigating MySQL Replication Lag and Reducing Read Load at GithubGithub engineers · GithubCase Studies
  • MongoDB at Baidu: Multi-Tenant Cluster Storing 200+ Billion Documents across 160 ShardsMongodb engineers · MongodbCase Studies
  • Shopify EngineeringShopify engineers · ShopifyBlogs
  • Sharding MySQL at QuoraQuora engineers · QuoraCase Studies

  Open this lesson with its quiz and exercise

• Advanced Distributed Systems · 12 minRedis: persistence, replication, Sentinel and ClusterWhat keeps an in-memory database from losing data and from being a single point of failure: snapshots, the append-only file, replicas, Sentinel and hash slots.Read hereClose

  Fast because it is in memory, durable because of logs

  Redis keeps its dataset in RAM (the reason it is so fast; see the Redis and Memcached lesson). To survive a restart it offers two persistence mechanisms, which can be combined.

  Snapshots (RDB)

  Redis periodically writes a point-in-time snapshot of the whole dataset to disk, using a forked child process so the main process keeps serving. Snapshots are compact and quick to load, but anything written since the last snapshot is lost on a crash.

  Append-only file (AOF)

  Redis appends every write command to a log. On restart it replays the log. The fsync policy sets the loss window:

  Policy	Behaviour	Risk
  always	Flush after every command	Slowest, almost no loss
  everysec	Flush once per second	At most about a second lost (common default)
  no	Let the OS decide	Fastest, larger loss

  The AOF grows, so Redis periodically rewrites it into a compact form. Many deployments use AOF with everysec, sometimes plus snapshots for fast backups.

  100%

  Figure SDA-ADS-04-1 · Redis persistence choices · © forcsguy.com

  Replication and Sentinel

  A Redis primary replicates to one or more replicas asynchronously. Replicas serve reads (possibly slightly stale) and can take over. Sentinel is a small set of monitor processes that:

  1. watch the primary and replicas,
  2. agree by quorum that the primary is down,
  3. promote a replica and tell clients where the new primary is.

  100%

  Figure SDA-ADS-04-2 · Sentinel failover · © forcsguy.com

  Because replication is asynchronous, a failover can lose the last few acknowledged writes. Redis offers settings to reduce this (for example requiring a minimum number of replicas to acknowledge), at the cost of availability.

  Redis Cluster: sharding

  When data or traffic outgrow one node, Redis Cluster shards the keyspace into 16,384 hash slots. A key's slot is a hash of the key modulo that number; each node owns a range of slots, and each primary has replicas. Clients learn the slot map and talk to the right node; a wrong guess gets a redirect.

  • Resharding moves slots (and their keys) between nodes while the cluster runs.
  • Multi-key operations only work when all keys are in the same slot. Hash tags (a {tag} part of the key) force related keys into one slot.
  • Failover is built in: replicas of a failed primary are promoted by the cluster.
  • This is the "fixed number of slots" cousin of consistent hashing (see the consistent-hashing lesson).

  Which setup when

  Need	Choice
  Pure cache, loss acceptable	Single node or replicas, little or no persistence
  Must survive restart	AOF (everysec) and snapshots
  Must survive node loss	Replica plus Sentinel
  Data larger than one node, or very high throughput	Redis Cluster

  Also keep memory in mind: set a max memory and an eviction policy (see the caching lessons) so Redis behaves predictably when full, and watch for large keys and slow commands, which block a single-threaded executor.

  Real-world reading

  Related reading

  • Redis DocumentationRedis · Redis Ltd.Documentation
  • Redis persistenceRedis · Redis Ltd.Documentation
  • Redis UniversityRedis · Redis Ltd.Courses
  • Redis BlogRedis engineers · Redis Ltd.Blogs

  Show 6 moreShow fewer

  • Scale with Redis ClusterRedis · Redis Ltd.Documentation
  • RedisRedis · Redis Ltd.GitHub
  • Failover with Redis Sentinel at VintedVinted engineers · VintedCase Studies
  • Memory Optimization in Redis at WattpadWattpad engineers · WattpadCase Studies
  • Speeding Up Redis with Compression at DoorDashDoorDash engineers · DoorDashCase Studies
  • Availability in Globally Distributed Storage Systems at GoogleGooglePapers

  Open this lesson with its quiz and exercise