A more stable MySQL with HAProxy

If you administer a MySQL server chances are you’ve seen it go astray. A some point in time for no apparent reason, maybe for a traffic spike, the number of running queries starts snowballing until MySQL eats 100% of your CPU grinding your whole server to a halt. In the context of this article let’s just call this pattern The Snowball Effect.

The Snowball Effect can be caused by slow queries and locking issues especially on MyISAM tables. After you’ve done your homework and optimized your queries, using better indexes and tweaking MySQL configuration, there’s still a little detail, one that doesn’t get noticed: you reduced the probability of triggering The Snowball Effect. It can still happen if the load on database ever increases. For some applications and datasets you may never be able to optimize enough to prevent the Snowball Effect from happening.

So what to do? We can really prevent the Snowball Effect only by limiting the concurrent number of queries executed by MySQL. Unfortunately MySQL cannot queue connections that exceed a configured maximum. It is black or white: a connection can be accepted and have its queries immediately executed or it can be rejected, causing an error. You can never be sure a sudden connection spike won’t start the chain reaction that will ultimately hang your server.

Sure, you can control the maximum concurrent connections at the application level using common connection pooling techniques. Except that many times you cannot work at that level as a system administrator and many platforms, notably PHP, due their stateless (aka start-all-over-again) nature, just can’t do it.

Enter HAProxy. HAProxy is an incredibly versatile and powerful TCP/HTTP Load Balancer. We’ll use a tiny amount of its countless features. Using it as a simple TCP proxy sitting between our webserver and MySQL, we can take advantage of its neat queuing system and finally gain control of the maximum number of simultaneous connections that will reach our MySQL server. Let’s see how.

Get HAProxy from your distribution repositories, it is surely in there. Once installed, edit your haproxy.cfg (which is probably located in /etc/haproxy/).

global
	log 127.0.0.1	local0
	log 127.0.0.1	local1 notice
	maxconn 4096
	user haproxy
	group haproxy
	daemon

defaults
	log	global
	mode	http
	retries	3
	option redispatch
	maxconn	4096
	contimeout	50000
	clitimeout	50000
	srvtimeout	50000

listen  MySQL localhost:3305
  mode tcp
  balance roundrobin
  server mysql_slave localhost:3306 maxconn 20

listen admin_page localhost:3307
	mode http
  balance roundrobin
	stats uri /

The important bits are the last two listen groups. The MySQL one establishes the TCP proxy on port 3305 allowing only 20 simultaneous connections. The admin_page stuff configures HAProxy’s web control panel on port 3307. Don’t set maxconn value too high, especially if your db doesn’t span multiple disks. At the end of the day your disk has just one head and it’s good at doing one read at the time. Values higher than one just compensate for the filesystem cache and the MySQL query cache that cause many connections to never hit the disk.

Now change your application configuration so that it connects to HAProxy instead of MySQL. For example if you have a WordPress blog set DB_HOST to 127.0.0.1:3305 in wp-config.php. It is important to use 127.0.0.1 and NOT localhost. By using localhost most MySQL drivers will connect via a UNIX socket and not through the TCP loopback interface, ignoring the port number and bypassing HAProxy!

This setup assumes a small number of queries per connection. Never use persistent connections with this setup.

The complexity of your architecture doesn’t really matter here. You can have dozens of servers, but the point still remains. Put a HAProxy in front of every single MySQL instance. Used as described above it provides a functionality that should be built inside MySQL.

You can use MySQL Administrator or mtop to monitor the connections on your MySQL and actually see that they get capped when they reach the configured maximum value. Now sit back and enjoy a more stable MySQL.