How to avoid replication lag in case all writes on master and reads on replica?

Question

I have stuck with the replication lag problem. I am trying to refactor my DB infrastructure. The first step was read-write split. I used ProxySQL то implement it. Now I have masterDb where all INSERT and UPDATES executing and 2 replicas where I route all SELECT.

But the main problem I faced with, replication lag. Because usually when you change something in DB you immediately read data and expect to have new data in the query result, but with replication lag, I receive outdated data. After googling I found this article https://www.percona.com/blog/2018/11/29/mysql-high-availability-stale-reads-and-how-to-fix-them/ and according to this info the best option in 2018 was "ProxySQL 2.0 GTID consistent reads"

Any updates from 2018? Maybe you guys know a better solution? Please share!

Answer 1

Some SELECTs exist to support the Inserts/Updates/Deletes that you are about to do. You must not send them to the Replica; they must be done in the Primary, usually inside the same transaction with the write operation. Check to see that ProxySQL is not doing that.

Aside from what Bill mentions, other tricks are possible. By inserting something into the replication stream and then checking it on the Replica, you may be able to see if your writes have made it to the Replica. This worked fine before replication became multi-threaded. So beware of old advice that suggests this trick.

You may as well plan on changing your application to at least have two ways to connect -- either through a Proxy versus deliberately going to the Primary.

Another gotcha -- Any check of a Replica's freshness must be in the same connection with the SELECT you are about to do. This is because a different connection may hit a different Replica. Replicas are not necessarily equally fresh. (A big SELECT one one Replica can, in many ways, slow down replication.)

Galera

Since you are already running with 3 servers, you may as well consider switching to Percona PXC (or MySQL 5.7 + Galera, or MariaDB with Galera built-in). It provides a 'wsrep' method at the SELECT level for making sure the data is synced up. Meanwhile, it provides better HA support, automated failover, and (optionally) writing to any node.

Answer 2

Replication always has lag, if only a very small amount, because your data changes are not even written to the binary log until you commit a transaction. Then the replica has to download those events in the binary log and apply them on the replica instance. This is normally very quick, but by definition, it is > 0 lag for every event.

It is also possible for your application to attempt to read data it just wrote, before it commits its own transaction. In that case, there's no way the replica can have the same change, since it hasn't even been committed on the source instance yet.

If your app needs absolutely current data, with no possibility of reading stale data, then the queries must read from the source instance, not a replica.

But not every read needs to read such strictly current data. Each query in your application has potentially different sensitivity to reading data that is lagging a little bit behind the source.

I wrote a presentation Read Write Splitting with MySQL and PHP for Percona, which describes several different solutions for different levels of replication lag tolerance.