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
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
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.