Uncommon Sense

For the last couple of days I’ve been playing around with HAProxy MySQL/Galera to create a fault tolerant, load balancing database backend that is invisible to the application.

Unfortunately, I was having an issues, that after a set amount of time the MySQL nodes would stop accepting connections.

After a bit of failed googling I visited the #haproxy irc chat on Freenode and a couple of helpful fellows called @vr and @meineerde pointed me to this section of the HAProxy configuration guide:

  …It was reported that it can generate lockout if check is too frequent and/or if there is not enough traffic. In fact, you need in this case to check MySQL “max_connect_errors” value as if a connection is established successfully within fewer than MySQL “max_connect_errors” attempts after a previous connection was interrupted, the error count for the host is cleared to zero. If HAProxy’s server get blocked, the “FLUSH HOSTS” statement is the only way to unblock it.

I had already set the max_connect_errors in my.cnf file to be 1000 but I guess that was no way enough. After looking around the net a bit looking for inspiration I’ve decided to set it to: