Database Replication

Database replication

From Wikipedia: "Database replication can be used in many database management systems, usually with a master/slave relationship between the original (master) and the copies (slaves)."

Note: The industry has largely moved to the terms primary/replica for the same idea, but the roles are identical, so this post keeps the master/slave wording from the source.

The split of responsibilities:

• The master database handles writes only: every insert, update, and delete goes to the master.
• Each slave database holds a copy of the master's data and handles reads only.

Most applications read far more than they write, so a system typically has one master and several slaves. Reads, being the bulk of the traffic, get spread across many machines; writes stay on the one.

Replication is usually asynchronous: the master commits a write and returns to the client immediately, then ships the change to the slaves after the fact. So a slave can trail the master by a small window, from milliseconds under light load to seconds when it is catching up, and a read issued right after a write may not see that write yet.

Why replicate

• Better performance. Writes and updates all land on the master, while reads fan out across the slaves. More queries run in parallel, so total throughput goes up.
• Reliability. If a database server is lost, to a disk failure or a physical disaster at one site, the data still exists on the other replicas. There is no single copy to lose.
• High availability. With data mirrored across locations, the site keeps serving even when one database is down, because another one can answer.

Reading right after a write

The lag from asynchronous replication creates a visible bug: a user updates their profile, the page reloads, and the old value is still there because the read landed on a slave that has not received the change yet. This is the read-after-write consistency problem. Two ways to deal with it.

Route that user's reads to the master for a short window.

After a user issues a write, send that same user's reads to the master instead of a slave, for a few seconds, long enough for the slaves to catch up. Everyone else keeps reading from slaves the whole time.

• The window is keyed on the user, usually by user ID or session ID held in a cookie or in the app's session store. Only the user who just wrote is affected; the rest of the traffic is untouched.
• Ending the window can be crude or precise. The crude version is a fixed timer, for example "route to master for 5 seconds after any write." The precise version records the master's replication position at the moment of the write, then on each read checks whether the slave has applied up to that position and only falls back to the master if it has not.
• The master load stays low because at any moment only a small slice of users are inside their post-write window, so this does not undo the read scaling that replication bought you.

Pin the user to the master for the whole session.

Coarser and simpler: once a user performs any write, mark the session as "sticky to master" and serve all of that session's reads from the master until the session ends.

• Easier to reason about, since there is no timer and no replication-position tracking. A session either reads from the master or it does not.
• It costs more. A user who writes once early on then browses for twenty minutes spends that entire time hitting the master, so the master carries read traffic it did not need to. On a site where many sessions contain at least one write, this can erase much of the benefit of having slaves.
• A middle option is to pin the session to one specific slave rather than the master. The user gets a consistent view of their own writes as that slave applies them in order, and the master is spared, at the cost of routing complexity and a still slightly stale view.

In practice sites lean on the first approach for follow-up reads, and often avoid the problem entirely for the immediate response by rendering it from the data the app already has in hand rather than issuing a fresh read at all.

What if a database goes offline?

We asked this about the load balancer; we ask it again here. Replication handles it.

A slave goes offline.

• If it was the only slave, reads are temporarily sent to the master until a replacement slave is brought up.
• If other slaves are healthy, reads are just redirected to them. A new slave replaces the failed one.

The master goes offline.

• One slave is promoted to be the new master. All writes go to it from then on.
• A fresh slave is added to take over replication for the promoted node.

Which slave gets promoted?

If there are several slaves, they are not all equally good candidates. The system (or the failover tool running it) looks at a few things before picking one.

• How up to date it is. Replication is asynchronous, so when the master dies, each slave has applied a slightly different amount of the master's recent writes. The slave that is furthest ahead, meaning it has the most of those writes already, is the safest choice, because whatever it is missing is the only data at risk of being lost.
• How far behind it was running. A slave that normally lags by a few milliseconds is a better bet than one that is usually seconds behind and still catching up.
• Whether the machine can handle the job. The master does more work than a slave: it takes every write and also feeds all the other slaves. The promoted machine needs enough CPU, memory, disk space, and disk speed to carry that, not just the lighter read load it had before.
• Where it sits. A slave in the same region or data center as the application servers keeps write latency low and has a fast network path to the other slaves it now has to feed.
• Whether it has been stable. A machine that has been restarting or throwing errors recently is passed over for one with a clean recent history.
• What the config says. Failover tools let you rank the slaves in advance, or mark some as "never promote." A slave kept on purpose as a delayed backup, or one used only for analytics, or one on weaker hardware, is usually excluded so it is never chosen.
• Same database version. The other slaves have to re-point at the new master and keep replicating, so the promoted node should run the same engine version as the rest.

In systems that use a voting mechanism (a quorum), the replicas vote and the one that a majority agrees is the most up to date wins.

In real systems, promoting a master is still messier than it sounds. Even the best candidate may not have every write the old master had at the moment it died, so the missing data has to be reconciled with recovery scripts. Other schemes like multi-master and circular replication exist, but they add their own complexity and are outside the scope here.

The design so far

Putting the load balancer and database replication together:

A request now flows like this:

1. The user gets the load balancer's IP address from DNS.
2. The user connects to the load balancer at that IP.
3. The load balancer routes the HTTP request to Server 1 or Server 2.
4. That web server reads user data from a slave database.
5. That web server sends any write, update, or delete to the master database.

The web tier and the data tier are both solid now. The next step is improving load and response time, by adding a cache layer and pushing static content (JavaScript, CSS, images, video) onto a content delivery network (CDN).