Friday, September 19, 2014

MySQL with HAProxy for Failover and Load Balancing

As discussed in a previous blog post about different types of MySQL HA architectures, using HAProxy for failing over and load balancing over clusters of MySQL can be very effective for most  situations where a transparent application manual failover is required (OK I coined this term after years working on Oracle systems). In this article I will explain how to setup an architecture similar to Figure 1.

Figure 1 - Two MySQL "clusters" load balanced by HAProxy

For simplicity, the cluster will be made up of one node (the master), and for this setup we will use three machines:

  • m-mysql01 - First MySQL cluster
  • m-mysql02 - Second MySQL cluster
  • mysql-cluster - HAProxy

In this article I will assume that you have already installed MySQL on m-mysql01 and m-mysql02 and set them up as master-master replication. In the next steps we will create a user for HAProxy to determine the status of the MySQL servers (a non-privileged user with a blank password accessible only from HAProxy) and another user for the application to use to connect through HAProxy:

Please note that these users will be automatically replicated to the other node. Before we start looking at the HAProxy part, let's install MySQL client and test the connectivity to both nodes:
If you are able to list the databases, we can move on to install HAProxy:

Create a new HAProxy configuration:

A bit of explanation on this configuration might be handy for you guys especially if you're not familiar with HAProxy. The most important blocks are the last two where we tell HAProxy to listen to the network interface and based on the rules forward the requests. In the first listen block we are accepting MySQL connections (from the application) on HAProxy does not understand the MySQL protocol, but it understands TCP (hence mode TCP). The "option" tag is used to determine the node status by trying to TCP connect using the "haproxy_check" user. In the next two lines we put the MySQL nodes. Since in my particular case I would like to have one active server at a particular time (since the application is not robust enough to handle node crashing with async replication), I am commenting the second server.

In the second listen block I am configuring a simple stats application which comes by default on HAProxy. It is now time to start HAProxy:

When I point my browser to I can see the "cluster" status:

A green row indicates that HAProxy is able to communicate with the MySQL node. We can also perform another test using MySQL protocol (i.e. MySQL client):

And that's it! Now we can go on and test failovers by replacing commenting out m-mysql01 and activate m-mysql02 instead. And now for some stress tests I use "mysqlslap" tool.

Stress testing against HAProxy:

Stress testing against one of the MySQL nodes directly:

The stress test which was run a number of times on a cold and warm instance, shows that HAProxy acqually managed connections better, resulting in faster queries. Note that the stress test introduces both INSERTS and SELECT statements.

Another cool thing you can do with HAProxy is to limit the maximum number of connections to the MySQL servers. This can make sense not just to protect you against DoS attacks but to actually improve performance especially if your data files are not on a multidisk SAN. I normally like to set the maximum connections to 20, but this is subject to your environment:

Post a Comment