Is using ProxySQL a requirement to direct queries in Galera Cluster or MySQL Master-Slave?

Question

I still don't have much experience using these tools (Galera Cluster and MySQL Master-Slave Cluster ) and I'm testing some scenarios in my Wordpress environment at Jelastic/Virtuozzo.

My platform has a large volume of queries per minute, both from requests that happen randomly, and from actions via Cron, which in my case does not use the native wp-cron schedule, but the linux cron schedule, every 5 minutes .

I use applications that access the database a lot, such as Woocommerce, Wocoomerce Subscriptions, Learndash, Automator, among others.

Testing scenario 1 - MySQL Master-Slave with ProxySQL: it was satisfactory, but I'm afraid to keep only one node as a master, in case of failure.

Testing scenario 2 - MySQL Master-Master with ProxySQL: it worked for some time, but then it started to have problems with out of sync queries in some tables, mainly the options table (wp_options). The error 'ha_err_key_not_found' became common in the log.

Testing scenario 3 - MariaDB Galera Cluster without ProxySQL: This is the default configuration in the Wordpress Cluster on the Jelastic Marketplace. It was satisfactory, but at times it produced [Warning] Aborted connection 6388 to db:.. (Got an error writing communication packets). And this was a nuisance, as it always happened in some Woocommerce task requests, scheduled in Action Sheduler, causing these tasks to fail.

Testing scenario 4 - Maria DB Galera Cluster with ProxySQL: It was also satisfactory, there were fewer task errors in Action Sheduler, but not completely.

About such errors in Action Sheduler, in the last two scenarios, they were always the same: "action timeout after 300 seconds", although the server timeout is greater than this time (600). Which leads to believe that the task was simply killed. And these only happened when the action was triggered via asynchronous request.

All this illustration was just to report my experiences, but I realize that, despite some flaws, using Galera Cluster for me is the best option. But my doubts arise:

1) Is it better to use ProxySQL in a Wordpress environment or not?

2) If I don't use ProxySQL on the Galera Cluster, I know that replication will work normally, but if I connect Wordpress directly to the master node, will the queries be balanced between the other Galera nodes? Or will queries always be performed on the Master node, regardless of whether they are written or read?

3) If the answer to question 2 is that the queries will not be balanced, would it be correct to connect each node of the app server layer to a different Galera node?

4) In the case of using MySQL Master-Salave without ProxySQL, would SELECT queries still be performed on the slave node? Or is only the master node actually responding?

Thank you in advance for the possible answers and I apologize if it was not clear or if the question is a little too naive.

Answer 1

As far as I know when working with Galera Cluster for production environment. Here are my opinion to your questions:

1. Is it better to use ProxySQL in a Wordpress environment or not?

Yes, it's better to use ProxySQL for your Wordpress deployment. Let say we have node1, node2 and node3. For example, by using ProxySQL we could pin node1 as write node. Then we could pin node2 and node3 as read node. So, every write operation will be redirected to node1. But every read node, will be redirected to node2 or node3

2. If I don't use ProxySQL on the Galera Cluster, I know that replication will work normally, but if I connect Wordpress directly to the master node, will the queries be balanced between the other Galera nodes? Or will queries always be performed on the Master node, regardless of whether they are written or read?

No, you only able to point into single node of your Galera Cluster. It would be even bad if your node down, but your Wordpress couldn't pointing the other node dynamically.

By using ProxySQL, you only need point out to ProxySQL IP Address.

3. If the answer to question 2 is that the queries will not be balanced, would it be correct to connect each node of the app server layer to a different Galera node?

Without using load balancer software you might have to try another trick. For example by using HAProxy or Nginx load balancing.

4. In the case of using MySQL Master-Slave without ProxySQL, would SELECT queries still be performed on the slave node? Or is only the master node actually responding?

It's depend on your configuration. Let say you have node1, node2 and node3. node1 is master. Node2 and node3 are slaves to node1. You can define that write operation could be redirected to node1. For the read operation could be load balanced to node2 or node3.

Those opinions are based on my experience working with ProxySQL this year.

Example tutorial about ProxySQL that you can visit: https://proxysql.com/blog/effortlessly-scaling-out-galera-cluster-with-proxysql/

Hope you enjoy that article.