3

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!

2 Answers 2

3

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.

6
  • Thanks for the response! Yes, actually we configured to route all SELECT FOR UPDATE queries to the master. And what do you think about "5) ProxySQL 2.0 GTID consistent reads" from this article percona.com/blog/2018/11/29/… Commented Jan 31, 2022 at 17:10
  • @VolodymyrBilovus - Good article. First, make sure you have a new-enough version (or are using Galera) to have GTIDs / wsrep / etc. What version are you running?
    – Rick James
    Commented Jan 31, 2022 at 17:19
  • Currently it's 5.7.3 and It looks like it will be easy to update to the 5.7.5 (which is necessary) Commented Jan 31, 2022 at 17:23
  • 2
    I would go to at least the GA release of 5.7.9, but you may as well go to the "latest" 5.7, namely 5.7.36 (or perhaps newer). Anyway, 5.7.xx has GTIDs, etc.
    – Rick James
    Commented Jan 31, 2022 at 17:41
  • 1
    @VolodymyrBilovus - See what I added to my Answer.
    – Rick James
    Commented Jan 31, 2022 at 17:48
4

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.

5
  • Thanks for the detailed response! And what do you think about "5) ProxySQL 2.0 GTID consistent reads" from this article percona.com/blog/2018/11/29/… Commented Jan 31, 2022 at 16:00
  • I haven't used it. It sounds like it could work, but in general, expecting too much to happen "transparently" is not wise. And as I point out in my presentation, it's not necessary to be so strict about replication lag in many cases. Commented Jan 31, 2022 at 16:07
  • BTW Thanks, man! Commented Jan 31, 2022 at 16:09
  • The original link to the presentation doesn't work. It looks like it was captured on web.archive.org: web.archive.org/web/20160624011643/https://www.percona.com/… Commented May 15 at 20:15
  • @telotortium Thanks... Slideshare removed that slide deck but won't tell me why, and won't let me restore it at the same URL. Commented May 15 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.