Best practice connecting to a galera cluster?

Question

If I have 7 servers, with 7 IPs, what is the best way to connect to them?

I have thought of: 1.) Pointing all my queries through one IP 2.) Using rand() to randomly select an IP and connect to it, distributing all the queries across the servers

Answer 1

Use HAProxy or any other intelligent loadbalancer in front of galera cluster. Best way how you can utilize your galera cluster is distribute all queries across all active and healthy servers.

How to detect if server is active and heatlhy?

It's quite easy. Since HAProxy AFAIK hasn't anything like mysql check, it cannot do it itself. But HAProxy has HTTP health check. So you can run some little http server on each galera node (nginx is perfect for this) and there will be script what will connect to local node, make some operations and return status code depends on response of galera. You should try to perform all common types of queries (INSERT, SELECT, UPDATE and DELETE) to be sure node is heathty, but when WSREP fails, every query on this node fails, even so simple like select now();. HAProxy will periodically run this script and react on returned status code.

How to simply avoid to be HAProxy SPOF?

Install it on each application server! HAProxy is quite small piece of software, so you can run it with your app. Bonus for this approach is that your application will point all database queries to localhost and will no nothing about cluster, so you can scale it as you want, just updates HAProxy config (can be done on the fly with socket api).

Why is pointing to one IP or rand() bad idea?

Rand() cannot react to outage of database server. And as I can see in my cluster, galera is not rock stable technology.

Using one IP to point all queries is bad because you are wasting resources of another 6 nodes. And you have to prepare scripts for healthchecks to move VIP across cluster.