Improve read consistency with database replicas

[zsparal]

Background

At Mollie, we have been using ProxySQL for our MySQL databases for a while now. Some time ago, we started using read-replicas as an alternative, read-only data-source in our applications. The decision of which DB handle (rw/ro) to use is ultimately left to the application itself: it can choose to either read from the primary database or from read-replicas, if the use-case allows. In the latter case, the application has to be tolerate some replication lag, but this is not a problem for a lot of situations.

Problem

The issue we have run into is that read operations are not always atomic and might have dependencies. Consider a case where we insert to records (A and B) in quick succession to the database, where B causally depends on A. The application loads B and then tries to load A, assuming it exists (because of the causal dependency). Because these queries can be routed to replicas with different amounts of replication lag, the query to load A might return no results if the writes haven't been replicated yet.

In certain cases, this lead to high error rates whenever replication lag for one of the replicas increased (a 3 orders of magnitude jump)

Solution

We implemented a solution for this problem in our own fork of ProxySQL that we have been using in production since. The patch has eliminated there errors and made our systems more predictable. We opened a Pull Request with the patch in #4553.

Considered Alternatives

We considered using GTID Consistent Reads as a potential solution, but decided to go with our patch for the following reasons:

1. GTID requires more components deployed in the cluster (the binlog reader), increasing operational burden
2. It passes the implementation complexity to the client applications if they use separate connections for reads and writes. In these cases, clients would need to keep a track of GTID's and send them as query annotations along all queries

We believe that our implementation provides a simple solution when an application just wants to make sure that all reads get a consistent state from the replicas in the pool.