Small latency issue after insert/update using proxySQL and MySQL Cluster

Question

We are using MySQL Cluster with proxySQL and have the following setup:

• Server 1 is the main server hosting the master database and is located in Europe
• Server 2 is a server in Asia used to serve local users. This server contains a read-only version of the database that Server 1 updates when changes occur.

The problem is this: User on server 2 triggers SQL UPDATE, UPDATE client SET name= 'New Name' WHERE id = 123. (This update is sent to server 1, then server 2 updates)

In the same code block, if we select the record (SELECT name FROM customers WHERE id =123), the query will be sent to the local server and the result will be "old name" unless we pause the code for 1 second (for testing) .

One solution we thought of was to change the connection (PHP) and go directly to server 1 to get the rest of the code block, but that kind of defeats the purpose and is far from ideal (quietly large code base needs updating).

Is there a solution involving proxySQL?

Answer 1

To prevent queries from being routed to slave servers with high replication latency, you can set a threshold maximum replication latency in the table mysql_server column Maximum replication delay. Reference: max_replication_lag

max_replication_lag – If greater than 0, ProxySQL will Regularly monitor replication latency and whether it exceeds configured limits threshold, it will temporarily avoid the host until replication catches up.

You can also create query rules to split query reading and writing. ref: ProxySQL split read/write

Important: proxysql-gtid-causal-reads If you are using Mysql version 5.7.5 or higher, this may mainly solve your problem.