October 31, 2014 By Severalnines
When running a MariaDB Cluster or Percona XtraDB Cluster, it is common to use a load balancer to distribute client requests across multiple database nodes. Load balancing SQL requests aims to optimize the usage of the database nodes, maximize throughput, minimize response times and avoid overload of the Galera nodes.
In this blog post, we’ll take a look at four different open source load balancers, and do a quick benchmark to compare performance:
- HAproxy by HAproxy Technologies
- IPVS by Linux Virtual Server Project
- Galera Load Balancer by Codership
- mysqlproxy by Oracle (alpha)
Note that there are other options out there, e.g. MaxScale from the MariaDB team, that we plan to cover in a future post.
When to Load Balance Galera Requests
Although Galera Cluster does multi-master synchronous replication, you should really read/write on all database nodes provided that you comply with the following:
- Table you are writing to is not a hotspot table
- All tables must have an explicit primary key defined
- All tables must run under InnoDB storage engine
- Huge writesets must run in batch, for example it is recommended to run 100 times of 1000 row inserts rather than one time of 100000 row inserts
- Your application can tolerate non-sequential auto-increment values.
If above requirements are met, you can have a pretty safe multi-node write cluster without the need to split the writes on multiple masters (sharding) as you would need to do in a MySQL Replication setup because of slave lag problems. Furthermore, having load balancers between the application and database layer can be very convenient where load balancers may assume that all nodes are equal and no extra configuration such as read/write splitting and promoting a slave node to a master are required.
Note that if you run into deadlocks with Galera Cluster, you can send all writes to a single node and avoid concurrency issues across nodes. Read requests can still be load balanced across all nodes.
Load Balancers
HAproxy
HAProxy stands for High Availability Proxy, it is an open source TCP/HTTP-based load balancer and proxying solution. It is commonly used to improve the performance and availability of a service by distributing the workload across multiple servers. Over the years it has become the de-facto open source load balancer, is now shipped with most mainstream Linux distributions.