We present a solution where each OX node runs a HAproxy instance. This way we can implement a solution without the need for additional loadbalancer (virtual) machines.
We create two HAproxy "listener", one round-robin for the read requests, one active/passive for the write requests.
HAproxy should be shipped with the distribution.
Historical Wheezy note: haproxy is provided in wheezy-backports, see http://haproxy.debian.net/. More recent (Debian) distributions don't need this extra repo and provide it with their native repos.
# apt-get install haproxy
The following is a HAproxy configuration file
/etc/haproxy/haproxy.cfg, assuming the Galera nodes have the IPs 10.0.0.1..3.
global log 127.0.0.1 local0 log 127.0.0.1 local1 notice user haproxy group haproxy # this is not recommended by the haproxy authors, but seems to improve performance for me #nbproc 4 maxconn 256000 spread-checks 5 daemon stats socket /var/lib/haproxy/stats defaults log global retries 3 maxconn 256000 timeout connect 60000 timeout client 20m timeout server 20m option dontlognull option redispatch # the http options are not needed here # but may be reasonable if you use haproxy also for some OX HTTP proxying mode http no option httpclose listen mysql-read bind 127.0.0.1:3306 mode tcp balance roundrobin option httpchk GET / server db1 10.0.0.1:3306 check port 9200 inter 6000 rise 3 fall 3 server db2 10.0.0.2:3306 check port 9200 inter 6000 rise 3 fall 3 server db3 10.0.0.3:3306 check port 9200 inter 6000 rise 3 fall 3 listen mysql-write bind 127.0.0.1:3307 mode tcp balance roundrobin option httpchk GET /master # maybe be more prudent with the master for the fall parameter server db1 10.0.0.1:3306 check port 9200 inter 6000 rise 3 fall 1 server db2 10.0.0.2:3306 check port 9200 inter 6000 rise 3 fall 1 server db3 10.0.0.3:3306 check port 9200 inter 6000 rise 3 fall 1 # # can configure a stats interface here, but if you do so, # change the username / password # #listen stats # bind 0.0.0.0:8080 # mode http # stats enable # stats uri / # stats realm Strictly\ Private # stats auth user:pass
Note 1: the timeout options may seem exaggerated high, but they are required to ensure that it is not the loadbalancer shutting down MySQL connections while the systems still use it. Cf.
# Maximum time in milliseconds a connection will be used. After this time # the connection get closed. maxLifeTime=600000
We got a default of 10 minutes, so allowing for some extra time to allow running queries to finish plus some overhead, 20 minutes look like a reasonable value for the connection timeout here.
Note 2: If you are configuring a dedicated loadbalancer node which should loadbalancer for other clients on the network (rather than a distributed / colocated HAproxy instance which should only serve for localhost) change the
bind parameters accordingly.
Health check service
As you can see we use the
httpchk option, so we assume a health check service to be available. Please have a look at the Clustercheck page how to configure such a service. Please be aware that we assume you use our customized, improved
clustercheck script, so please don't use the standard one.
Contrary to other setup instructions which recommend to configure one node as regular node and the other two ones as
backup nodes, we recommend to leverage a health check which declares only the node with
wsrep_local_index=0 as available. This way we ensure that even in corner cases, multiple distributed HAproxy instances can not end up with declaring different nodes as designated write nodes, which would be problematic.
Besided using the Galera check service configured before, you can also speak to the stats socket of HAproxy using
# echo "show stat" | socat unix-connect:/var/lib/haproxy/stats stdio
The output is a CSV with long lines unsuitable for pasting here. Please test on your own.
There are more commands available via this socket to enable / disable servers; see the haproxy documentation for details. (As of writing that documentation could be found here: http://cbonte.github.io/haproxy-dconv/configuration-1.5.html#9.2 that URL seems unstable.)