I was using the latest version of HAProxy 1.3 and was load balancing backend MySQL servers while also checking their ports, so if one server went down it would be taken out of the load balancing pool. However, since the port checks in HAProxy happen at the TCP level, the MySQL instance which was being hit by the port checks wasn't happy, because it wasn't a proper MySQL connection. As a result, after a number of some checks, MySQL refused to allow clients to connect, with a message like this:
OperationalError: (1129, "Host 'myhost' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'")
Solution: upgrade HAProxy to the newly released version 1.4 (at the date of this writing, the exact version is 1.4.0). Documentation is here.
For MySQL specific checks, you can specify 'option mysql-check' in a backend or 'listen' section of the configuration file. For example, I have something similar to this in my HAProxy configuration file:
listen mysql 0.0.0.0:3306
mode tcp
option mysql-check
balance roundrobin
server mysql1 10.0.1.1:3306 check port 3306
server mysql2 10.0.1.2:3306 check port 3306 backup
Subscribe to:
Post Comments (Atom)
-
A short but sweet PM Boulevard interview with Jerry Weinberg on Agile management/methods. Of course, he says we need to drop the A and actu...
-
Here's a good interview question for a tester: how do you define performance/load/stress testing? Many times people use these terms inte...
-
Update 02/26/07 -------- The link to the old httperf page wasn't working anymore. I updated it and pointed it to the new page at HP. Her...
5 comments:
Using HAProxy is not a measure of MySQL health. Querying MySQL is a measure of MySQL health. Extract useful metrics and correlate them with related activity and latency to determine the true "health" of your storage engine.
Hey Jason -- thanks for the feedback. I am not relying on HAProxy to do any kind of deep MySQL monitoring, I have many nagios and munin checks for that purpose. I use HAProxy as a load balancer in front of multiple MySQL instances, and I merely want HAProxy to flag a backend instance as 'down' when it doesn't respond anymore on a particular port number. That's what I mean by 'health check' in this particular scenario.
You know that... and I know that... but there are too many people that still equate availability with a healthy system. Doing my part to debunk the myth. ;-)
HA-Proxy version 1.4.3 2010/03/30.
listen mysql1 127.0.0.1:3340
mode tcp
option mysql-check
balance roundrobin
server mysql1 mysql_host:3306 check port 3306
[WARNING] 091/134441 (5601) : Server mysql1/mysql1 is DOWN, reason: Layer7 wrong status, code: 0, info: "Host 'client_host' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'", check duration: 0ms.
[ALERT] 091/134441 (5601) : proxy 'mysql1' has no server available!
azalio -- it turns out MySQL health checks are still not working right in 1.4. I ended up applying this solution:
http://sysbible.org/x/2008/12/04/having-haproxy-check-mysql-status-through-a-xinetd-script/
Grig
Post a Comment