Percona XtraDB MySQL cluster, ProxySQL singlewrite to loadbal, wsrep_sync_wait=7

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi there,

On our Production environment, we have a 3 Percona XtraDB cluster “MySQL 5.7.26-xx”, enforcing synchronization using wsrep_sync_wait=7.
We use Percona ProxySQL v1.4.5, singlewrite mode.

In the next days, we are going to upgrade our Percona ProxySQL 1.4.5 to Percona ProxySQL v1.4.14 or something like that, also we want to change the mode, switching from singlewrite to loadbal “Load Balancer”.

Has anybody tried this?

We have one DEV/INT and 3 QAs environments, meaning another 4 full environments (one ProxySQL and 3 Percona XtraDB cluster), where we have upgraded to ProxySQL 1.4.14, and set to loadbal mode. We noticed improvement running ProxySQL using loadbal mode instead of singlewrite, when we ran stress tests.

Also, on those 4 environments we have the same setup Percona XtraDB cluster “MySQL 5.7.26-xx”, enforcing synchronization using wsrep_sync_wait=7 without issues or lag.

On our Production environment, on peak hours we have about 3K statements, where about 500-700 are inserts/updates and over 2K are reads/selects.

We are running our business on 3 servers: DELL PowerEdge R730xd, with VMWare 6.0 update 1. Our virtual machines are running Debian 8. Each virtual machine “VM” running Percona XtraDB Cluster, runs at 2.4Ghz, with 4 cores on a single CPU, it has 8GB of RAM assigned.

Our database is about 100 GB of disk space. We have the innodb_buffer_pool_size=2GB or maybe 3GB, I cannot recall exactly now :-p

Then, we can’t simulate the traffic we have on Production, on our QA environments. The only tangible difference is the ProxySQL 1.4.5 running as a singlewriter instead of loadbal “Load Balancer”.

Any thoughts, suggestions about keep it as singlewrite or switch to loadbal?

Thanks in advanced.

2

I just want to share, after 6 days, we haven’t had any issues, the Percona XtraDB “MySQL” Cluster is working properly, the Percona ProxySQL set to load-balancer is doing a great work, we are having about 4-5K statements per second on our peak hours and the Grafana/Prometheus is showing the 3 servers having very equally the amount of SQL statements.

3

Thanks for sharing, that’s sounds like great news.
Did you have any remaining things we need to help with? Sounds like you’re on point…