Loadbalancing read/write

Help!
1

Hello,

I have a 3 node galera cluster that I loadbalance with HAproxy. We ran into a problem with deadlock when using a certain php function on our sites that uses this setup. I know I can use check backup for db-02 and db-03 but I still want to load balance on all three, any config I can use for this?

The message I get when using the function is: Fatal error: Uncaught Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error: Got error 35 “Resource deadlock avoided” during COMMIT.

Thanks!

2

It works when I put check backup on db-02. So I can use HAproxy as a failover but not as loadbalancer. Can also mention that we use 3 nodes in the Galera cluster but only try to loadbalance 2 of them. Here is the config:

global
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
stats timeout 30s
user haproxy
group haproxy
daemon

    maxconn 2048
    tune.ssl.default-dh-param 2048

defaults
log global

    mode http
    option forwardfor
    option http-server-close
    stats enable
    stats uri /stats
    stats realm Haproxy\ Statistics
   

    option  httplog
    option  dontlognull
    timeout connect 5000
    timeout client  86400
    timeout server  86400
    errorfile 400 /etc/haproxy/errors/400.http
    errorfile 403 /etc/haproxy/errors/403.http
    errorfile 408 /etc/haproxy/errors/408.http
    errorfile 500 /etc/haproxy/errors/500.http
    errorfile 502 /etc/haproxy/errors/502.http
    errorfile 503 /etc/haproxy/errors/503.http
    errorfile 504 /etc/haproxy/errors/504.http

MySQL Cluster FE configuration

frontend mysql_cluster_frontend
bind *:3306
bind *:3307
mode tcp
option tcplog
default_backend galera_cluster_backend

MySQL Cluster BE configuration

backend galera_cluster_backend
mode tcp
option tcpka
option mysql-check user haproxy
balance leastconn
server db-01 192.168.11.102:3306 check weight 1
server db-02 192.168.11.103:3306 check weight 1

3

Hello,

From what I understand, you’ve got a function in your app that causes a deadlock. You should first create the app so it can handle deadlocks. Still, if that’s out of the question, the config from above, with “option mysql-check user haproxy” should work properly.
When there’s no deadlock, it will load balance among the two servers. When one is having a lock, the queries should be sent to the other server. Still, I think that this can cause an issue when the two servers will try to sync, because each will have data written in them at the same time, on the same row, basically, as db-02 is unaware that db-01 is already writing. I could be wrong though…

Still, as I said, deadlocks are a MySQL thing and apps should have a mechanism to work (retry transaction after a few seconds or something) even if they hit a lock. HAProxy’s job is to load balance / failover, not to avoid a MySQL issue, so you’re tackling the issue from the wrong perspective. If I were you, I’d tackle this from the app perspective / DB perspective (get faster disks, use more threads, etc, depending on what’s slowing the DB down).

4

It’s wierd though, I actually get “deadlock avoided” in the error message on the websites. I now use db-02 with check backup and the php function works great without any errors. Shame though because now our setup is only a backup instead of load balancing.

I think you are right though, this is DB related and nothing todo with HAproxy. The function we use that has errors actually copies a database entry so must be something there, the rest of the site works great no other function generates errors.