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.