Preferred approach for MySQL infrastructure - eCommerce website

Question

We have been using Percona Mysql master-slave configuration for our e-commerce website i.e single master and 2 slaves for read only, and an additional slave to carry out backups and reporting.

We observed that the load is not distributed, as our master server is loaded with the all write operations and the slaves being just used for read operations.

Considering this limitation, we are planning to make enhancements to our DB infrastructure.

One option we have is moving towards Percona XtraDB Cluster (master-master).

Please suggest any other preferred approach which can be useful for a eCommerce site.

Regards

Charles

Answer 1

You can use Galera Cluster solution. We use that with MariaDB for a big API with hundreds requests.

If you want more details just write me. Give it a try ;)

Answer 2

We observed that the load is not distributed

This is 100% correct. Async Master-Slave is not an automatic distributed setup. You must either A) code your application to use all servers or B) use middleware to distribute the connections (ie: ProxySQL).

Considering this limitation...

It is not a limitation; it is standard behavior ever since replication was first added to MySQL.

One option we have is moving towards Percona XtraDB Cluster (master-master).

Percona XtraDB Cluster is not "master-master". "master-master" is an asynchronous replication term, limited to 2 MySQL servers. PXC is a cluster and uses synchronous replication, meaning all nodes participate as one unit.

A master/slave-slave setup (like yours) should easily handle several thousand connections/transactions per second. If your current setup cannot handle that, you either have A) seriously misconfigured MySQL and/or B) extremely low hardware.

Please understand that switching to PXC/Galera will also not automatically balance connections/queries. You will require middleware in this case as well. All writes will still go to 1 node, even in PXC.