Ensuring Data Consistency in MySQL Replication: Balancing Lag and Latency [Interview Experience]

Hey there, friends! Welcome back. I hope everything’s going well for you. Today, I want to talk about something called data consistency in master-slave architecture. I’ll be sharing a recent experience from an interview, I had. Don’t worry, I’m going to make it an interesting chat so you won’t get bored. Alright, let’s kick things off. 🙅🏻‍♂️

Our conversation started with a question about designing a system to keep track of how many visitors a website gets. While talking about that, we ended up discussing how to make a database handle a lot of information at once. This led us to talk about separating tasks like reading from and writing to the database. And this discussion brought us to the idea of keeping the information consistent when using a system where some parts are in charge of reading (like a reader/replica/slave) and others are in charge of writing (like a writer/primary/master).

Note: The terms (writer, master, and primary), and (reader, slave, and replica), refer to the same entities and will be used interchangeably in this context.

Interviewer: As you said that having writer and reader instance will help to handle increasing Read-Write throughput, where all writes will go to the writer instance and all reads will go to reader instance. Could you please tell me how would reader instance will sync up data with writer instance ?(Here, Interviewer asked about Replication Mode)

Asynchronous vs Synchronous Replication

Replication mode refers to how data is copied and synchronized between different database instances in a replication setup. There are three main modes :

1. Asynchronous Replication: Primary database sending changes to replicas without waiting for confirmation from replicas, potentially causing data lag and inconsistencies.

2. Synchronous Replication: Data changes are confirmed on both primary and replica before moving forward, ensuring consistency but possibly introducing latency.

3. Semi-Synchronous Replication: Primary database waits for acknowledgment from at least one replica before proceeding, balancing between consistency and performance.

Me: Data sync has several strategies which is replication mode. we can sync data asynchronously (Asynchronous Replication). In asynchronous mode Writer will commit the changes and give the success and Reader instance will sync the data from Writer asynchronously.

Interviewer: That sounds comprehensive. but it could have data inconsistency when read requests arrive before data syncing in a replica?How can we ensure data consistency while managing potential replica lag?

Me: Right, To address this, we can have synchronous replication. This ensures that data consistency is maintained between the master and the replica, as changes are applied in a synchronized manner.

Interviewer: Impressive suggestions! But what about the concern regarding increased latency with synchronous replication?

Me: Absolutely, latency is a trade-off. We’ll evaluate the application’s latency tolerance and workload. We could explore a quorum-based approach or optimize replica performance to mitigate latency. Hybrid approaches, caching, and CDNs are also options to balance consistency and latency.

Quorum

Quorum-Based Approach: Imagine you’re making important decisions in a group, and you need a majority vote to proceed. In a similar way, a quorum-based approach in database replication means that changes are only confirmed when a certain number of replicas acknowledge them. This helps balance consistency and performance. For instance, if you have 3 replicas and set the quorum to 2, changes are only considered successful when at least 2 replicas have them. This prevents a single slow replica from causing unnecessary delays.

Interviewer: Okay!! but what if you have 5 replicas and require 3 of them to acknowledge a change for it to be considered successful (quorum of 3), it’s possible that one of the replicas might not have the most recent data. If a read request goes to that specific replica, it could provide outdated information, leading to data inconsistency for that particular request.

Me: Yes definitely, while a quorum-based approach is effective in maintaining consistency among replicas for write operations, but there is a trade-off where some read requests might not get the most up-to-date data. To address this, we can send read requests to the master node immediately after a write operation for critical data could be an option.

Interviewer: Okay, great !! Yes, immediate master reads for writes, use synchronous replication zones, and implement a quorum-based strategy can help. Do you also know about the replication format/strategies ?

Me: Hmmm…I told you all the strategies right. Sync,Async & Semi Sync.

Interviewer: These are replication channels or mode, not strategies. There are two core types of replication strategies or format, Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows.

Me: Ohhkay !! I am not well aware about that. I will go through it.

Summary : we’ve discussed ensuring consistency through replication mode selection, and immediate master reads for writes. To manage data inconsistency, we can stagger read traffic, use synchronous replication zones, and implement a quorum-based strategy. While addressing increased latency from synchronous replication, we’ll balance with workload analysis, hybrid approaches, and performance optimisation, keeping the application’s requirements in mind.

Key Points :

1. Every strategy comes with trade-offs, and it’s essential to have a conversation about them with the interviewer.
2. When you’re unsure, opting for a hybrid approach is often a reliable choice.
3. The Master-Slave architecture is useful for managing Single Points of Failure and increased throughput. However, it also presents challenges like latency and consistency in a distributed setup.
4. Same question can be asked as Leader-Follower Principle, Reader-Writer Segregation, Master-Slave consistency, Latency in Replicas.

Thanks for reading the blog. I hope this will help you to connect dots in your next interview. In my next blog I will share my learning on replication format. Happy Learning !!