I have 3 MySQL8.0 instances. Two of them are slaves. Each site call I‘m reading the slave till the first write Request is made. Afterwards Only the Master is used during the request. Now, if I write something into the database and redirect the user to a new page, the slave isn‘t updated yet. Is there any way I can handle such things?

Solutions I already found downgrade to MySQL 5.7 with Galera Cluster/Percona xtraDB/Group Replication.

Any other ideas? Which would you prefer?

  • Since you are on 8.0, why not simply add on InnoDB Cluster? – Rick James Feb 11 at 19:15
  • I did'nt want to change too much on the environment. It's a legacy software which is migrating to PHP Symfony and therefore it's not optimized for database queries. Each site call does >600Req. Most of them are not needed, I guess. Our Goal was to be able to scale that over multiple vm's (Solr, Redis, PHP, MySQL, and more). Maybe it's the second step. – Demigod Feb 17 at 15:17
  • 600 req per day? per second? – Rick James Feb 17 at 16:40
  • Splitting up tasks across VMs provides some level of isolation, but not scaling. – Rick James Feb 17 at 16:41
  • Group Replication and Galera are 'competing' technologies; have you managed to enable both on the same system? Sounds scary. – Rick James Feb 17 at 16:42

While an application-independent solution could be possible, in general, a replication-aware application could be much better, because you can use the high performance of asynchronous replication for most of the time, and only revert back to (slower) synchronous replication when it is needed.

This is not a theoretical method, we at Wikipedia use this model with specific code called "chronology protector", which only gets triggered when an important event happens (e.g. an edit) and you don't want the user to be served outdated results.

In order to do this, after doing your master writes, you should keep your master replication file and position (if not using GTID), or the GTID coordinates (if using GTID) after your write (once the transactions has been commited on the master), and then on the replica connections, wait until the server reaches those positions or higher. There is some extra details to consider, such as when exactly to wait (on write or on read), for how long until you give a timeout (e.g. if replica breaks or lags), but that is highly dependent on your environement.

In terms of implementation, MASTER_POS_WAIT() / WAIT_FOR_EXECUTED_GTID_SET() are the functions commonly used for this.

Some of the clusters you mention give you shortcuts to perform this easier (e.g. Galera's causal reads), but the logic is similar- you don't want to be on fully consistent mode all the time; just forcing it when necessary.

|improve this answer|||||
  • MASTER_POS_WAIT works perfect! Thanks for the answer. I wouldn't have figured this out alone. – Demigod Feb 7 at 14:35
  • Thanks for the link about "chronology protector". It seems somewhat complex, and perhaps more aimed at a site which expects 30-second lags as opposed to 1-second lags. – Rick James Feb 11 at 19:17
  • I don't disagree about the complexity, but I am curious why you think the second part of your comment? A 1-second lag will create around the same async issues than a 30 second one, there are points in which you need sync reads anyway. In fact, in our case, we are 99% on 0-1 seconds range as transactions larger than 1 second are killed automatically. – jynus Feb 12 at 18:19
  • Same here... 99% are already replicated, when they are needed. BUT if you redirect your user after writing to somewhat like a result page, you need a synchronous database. For those cases we're now encrypting the information in a new cookie which is read at the next request. That's a really quick implemented and scalable behaviour. – Demigod Feb 17 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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