Recommended loadbalancer?

MySQL & MariaDB Percona XtraDB Cluster 5.x
, ,
1

(educational purposes)

Hey comm,

as my next question is - i’m using the xtraDB Cluster for high available databases for different applications.

As when i connect the application to the (for example) node1 and node1 fails, how should it be high available? Sure - a loadbalancer!

But whats the recommended loadbalancer and which algorithm? Round Robin? Least Connection? Others? And how much simultaneously connections should it support? (Cause i maybe would use a hetzner cloud loadbalancer).

Thanks in advance.

2

Hello Keon,

We have an excellent document for loadbalancing PXC. I encourage you to go through it and you should have your answers.

Thanks,
K.

1 Like
3

Hey @kedarpercona,

so on that documentation ProxySQL will be a SPOF and i just found older documentations from 2017 about ProxySQL HA with keepalived. Is there anything “newer” in connection with xtraDB Cluster where ProxySQL do not will be a SPOF?

As its known from the “webhosting” sector, later i will have customers (fictitious) which just want to have a hostname, port, database, database-user and password to access their database with their application. Behind that hostname and port, it should be a HA system and not a SPOF within it.

4

Hello @Keon,

I believe you’re talking about this article by Marco. I’d also recommend reading the ProxySQL Cluster from documentation page in addition to that.

Finally, here are some more solutions consideration for you to keep in mind:

  • MySQL Failover for Asynchronous replication with Orchestrator, ProxySQL and Consul (PLive talk by Yunus)

Thanks,
K.

5

Hello @Keon
You can also set up a load balancer behind a proxysql farm (or at least 2 proxysql nodes) to enable HA in the proxysql layer.
Here’s an example on how it works in AWS Load Balancing ProxySQL in AWS

But there are very alike solutions for Azure and GCP. I’m referring to cloud as to me is a simpler solution than on-premises, however there are also other LB solutions to enable HA for proxysql