Introduction

In this article, I’m going to explain what Single-Primary Database Replication is, and how you can use it to increase both application availability and scale read-only transactions.

Single point of failure

The database server is a central part of an enterprise system, and, if it goes down, service availability might get compromised.

If the database server is running on a single server, then we have a single point of failure. Any hardware issue (e.g., disk drive failure) or software malfunction (e.g., driver problems, malfunctioning updates) will render the system unavailable.

Limited resources

If there is a single database server node, then vertical scaling is the only option when it comes to accommodating a higher traffic load. Vertical scaling, or scaling up, means buying more powerful hardware, which provides more resources (e.g., CPU, Memory, I/O) to serve the incoming client transactions.

Up to a certain hardware configuration, vertical scaling can be a viable and simple solution to scale a database system. The problem is that the price-performance ratio is not linear, so after a certain threshold, you get diminishing returns from vertical scaling.

Another problem with vertical scaling is that, in order to upgrade the server, the database service needs to be stopped. So, during the hardware upgrade, the application will not be available, which can impact underlying business operations.

Database Replication

To overcome the aforementioned issues associated with having a single database server node, we can set up multiple database server nodes. The more nodes, the more resources we will have to process incoming traffic.

Also, if a database server node is down, the system can still process requests as long as there are spare database nodes to connect to. For this reason, upgrading the hardware or software of a given database server node can be done without affecting the overall system availability.

The challenge of having multiple nodes is data consistency. If all nodes are in-sync at any given time, the system is Linearizable, which is the strongest guarantee when it comes to data consistency across multiple registers.

The process of synchronizing data across all database nodes is called replication, and there are multiple strategies that we can use.

Single-Primary Database Replication

The Single-Primary Replication scheme looks as follows:

The primary node, also known as the Master node, is the one accepting writes while the replica nodes can only process read-only transactions. Having a single source of truth allows us to avoid data conflicts.

To keep the replicas in-sync, the primary nodes must provide the list of changes that were done by all committed transactions.

As I explained in this article, relational database systems have a Redo Log, which contains all data changes that were successfully committed.

PostgreSQL uses the WAL (Write-Ahead Log) records to ensure transaction Durability and for Streaming Replication.

Because the storage engine is separated from the MySQL server, MySQL uses a separate Binary Log for replication. The Redo Log is generated by the InnoDB storage engine, and its goal is to provide transaction Durability while the Binary Log is created by the MySQL Server, and it stores the logical logging records, as opposed to physical logging created by the Redo Log.

By applying the same changes recorded in the WAL or Binary Log entries, the replica node can stay in-sync with the primary node.

Synchronous Replication

If the current transaction is waiting for one or multiple nodes to acknowledge that the currently committed changes have been applied to replicas, then the replication process is synchronous.

The advantage of Synchronous Replication is that the replicas are in-sync with the primary node, therefore reads are linearizable.

In the case of the primary node failure, the database system can promote any of the synchronous replicas to be the next primary node, and no committed transaction is going to be lost.

The disadvantage of Synchronous Replication is the latency incurred by applying the current transaction changes to one or more replicas. If the only synchronous replica is down, availability could be compromised as well.

Asynchronous Replication

When using Asynchronous Replication, the primary node doesn’t wait for replicas to acknowledge that all changes have been applied prior to returning the control to the application. For this reason, the asynchronous replicas lag behind the primary node.

Because the primary node no longer waits for replicas to confirm that all changes have been applied, the transaction response time is lower, and availability is not affected if one or more replicas crash.

The disadvantage is data inconsistency. If the replication time window is larger than the read-only transaction arrival time, then a read-only transaction can return stale data.

Horizontal scaling

The Single-Primary Replication provides horizontal scalability for read-only transactions. If the number of read-only transactions increases, we can create more replica nodes to accommodate the incoming traffic.

This is what horizontal scaling, or scaling out, is all about. Unlike vertical scaling, which requires buying more powerful hardware, horizontal scaling can be achieved using commodity hardware.

On the other hand, read-write transactions can only be scaled up (vertical scaling) as there is a single primary node.

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Single-Primary Database Replication is very useful since it provides both fault-tolerance and load splitting. For this reason, Single-Primary Replication is used by any non-trivial enterprise application.

For instance, the Stack Overflow architecture uses a Single-Primary SQL Server Replication Cluster with one primary node and a single asynchronous replica.

Insert details about how the information is going to be processed

DOWNLOAD NOW