Re: Galera 3 node cluster + HAProxy "Deadlock found when trying to get lock;"

[Ilias Bertsimas]

Hi,

Yes that means changing the load balance mode on HAPROXY. I assume you have round robin ?

You could try a mode that splits requests between nodes in a way that does not change all the time so clientX will always hit nodeX that could help before you resort to single master mode for writes.

Kind Regards,

Ilias.

On Monday, November 12, 2012 10:37:43 AM UTC, r0k5t4r wrote:

Hi,

I have setup a galera cluster with 3 nodes balanced by haproxy. For testing I have copied the db of our zabbix server to the cluster and noticed the following error in the logs:

3410:20121112:113014.718 [Z3005] query failed: [1213] Deadlock found when trying to get lock; try restarting transaction [commit;]

Google came up with a solution to have HAProxy setup so it always writes to a single node.

I guess this means changing the loadbalance policy on ha proxy?

Regards,
Oliver

[Alex Yurchenko]

On 2012-11-12 12:37, r0k5t4r wrote:
> Hi,
>
> I have setup a galera cluster with 3 nodes balanced by haproxy. For
> testing
> I have copied the db of our zabbix server to the cluster and noticed
> the
> following error in the logs:
>
> 3410:20121112:113014.718 [Z3005] query failed: [1213] Deadlock found
> when
> trying to get lock; try restarting transaction [commit;]
>
> Google came up with a solution to have HAProxy setup so it always
> writes to
> a single node.
>
> I guess this means changing the loadbalance policy on ha proxy?
>
> Regards,
> Oliver

Hi Oliver,

Does this affect zabbix functionality? Perhaps you can just ignore this
if zabbix can deal with it (retries the transaction)? This is not
exactly an error, it is a cluster-wide transaction conflict and is
something to be expected in multi-master cluster.

Regards,
Alex

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

[r0k5t4r]

Hi,

yes it is set to roundrobin.

I guess it doesn't affect zabbix. It states that it will retry the transaction.

[Dustin Marquess]

In my experience, Zabbix doesn't like multi-master at all. I had to force HAproxy to only send requests to a single server and then failover if that one dies.

Even in that config, I still get deadlocks sometimes, but that seems to be a Zabbix 2.x bug. I see the same thing on stock MySQL & PostgreSQL.

--

[r0k5t4r]

How did you force HAProxy to only write to on node? I'm not an export on HAProxy, this is my config:

global
        daemon
        stats socket /var/run/haproxy.stat mode 600 level admin
        maxconn 40000
        ulimit-n 144536
        pidfile /var/run/haproxy.pid
        log 127.0.0.1 syslog
defaults
        mode            http
        contimeout      4000
        clitimeout      3600000
        srvtimeout      3600000
        balance roundrobin
listen  mysql-cluster 192.168.44.101:3306
        log global
        mode tcp
        option httpchk
        balance leastconn
        option tcplog
        option persist
        option redispatch
        server node1 192.168.7.162:3306 check port 9200 inter 2000 rise 2 fall 2
        server node2 192.168.7.160:3306 check port 9200 inter 2000 rise 2 fall 2
        server node3 192.168.7.161:3306 check port 9200 inter 2000 rise 2 fall 2 backup

[Alex Yurchenko]

Thanks, Dustin, that's good to know.

On 2012-11-12 15:59, Dustin Marquess wrote:
> In my experience, Zabbix doesn't like multi-master at all. I had to
> force
> HAproxy to only send requests to a single server and then failover if
> that
> one dies.
>
> Even in that config, I still get deadlocks sometimes, but that seems
> to be
> a Zabbix 2.x bug. I see the same thing on stock MySQL & PostgreSQL.

[Dustin Marquess]

Simply add "backup" to the 2nd server line also.

-Dustin

> --
>
>

[r0k5t4r]

Hi,

using backup also on the 2nd server would be no load balancing. I tried balance source but this seems to break my setup. I get an error:

connection to database 'zabbix' failed: [1040] Too many connections.

I guess I have to tune my max_connections somewhere.

[The Hunter]

Thank It's work with haproxy + 3 nodes Galera(balance roundrobin check / check backup / check backup)