When we started working with a customer they asked us to setup for them more reliable and performant MySQL database. At the time they were using Percona XtraDB cluster with ELB to balance read traffic between PXC nodes. There was no writer redundancy – if a writer node was down, the app was down. The app by the way is a Magento backed online shop. All stuff was running in AWS.
We started to design the new database architecture. The first component we threw away was ELB. A simple OLTP sysbench test showed that ELB was a bottleneck. The ELB was capable to deliver somewhat around 6800 qps while with ProxySQL the Percona XtraDB Cluster was a bottleneck. We squeezed 10k qps from ProxySQL + PXC. That basically means we got all ProxySQL feature free of charge.
But one ProxySQL instance is a SPOF. To make ProxySQL highly available we put another instance and managed it with keepalived.
Eventually the architecture looked like …[Read more]