Sunday, June 14, 2015

MySQL Load Balancing with HAProxy

Today, we're going to be using HAProxy to queue and throttle connections towards a set of MySQL servers, alleviating the load. It's the same basic principle as when we used HAProxy to load balance requests to our web servers. This time, however, we're using a different connection scheme. Whereas before we used the roundrobin[1] scheme to select servers in turns, we're using leastconn to select the server with the least number of connections. Let's continue.

Prerequisites: It helps to be familiar with load balancing by reading this article, but you don't need to carry out those steps. You do, however, need to set up a multi-master MySQL cluster. Lastly, you'll want to be familiar with setting up VirtualBox with Vagrant. This guide was written for Mac OS X users.

Preparing Our MySQL Instances

After going through the prerequisites, you should already have a MySQL cluster up and running. If you haven't set that up, yet, head over to this article.

# prepare mysql1 for haproxy
vagrant ssh mysql1
vagrant@mysql1:~$ mysql -u root -e "INSERT INTO mysql.user (Host,User) values ('192.168.50.10','haproxy_check'); FLUSH PRIVILEGES;"
vagrant@mysql1:~$ mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'192.168.50.10' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES;"

# repeat for mysql2
vagrant ssh mysql2
vagrant@mysql2:~$ mysql -u root -e "INSERT INTO mysql.user (Host,User) values ('192.168.50.10','haproxy_check'); FLUSH PRIVILEGES;"
vagrant@mysql2:~$ mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'192.168.50.10' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES;"

Now, we're ready to configure our HAProxy instance. This will be a mostly manual process, but you'll learn a lot along the way.

Preparing Our HAProxy Load Balancer

# create directory and initialize
mkdir lb && cd lb
vagrant init

The following Vagrantfile will work best for our load balancer VM. It's just a simple Linux Ubuntu box with the IP address mapped, accordingly.

# spin up the new instance
vagrant up
# install programs
vagrant ssh lb
sudo apt-get install mysql-client haproxy

# test to make sure you can connect to both mysql instances
mysql -h 192.168.50.11 -u haproxy_root -p -e "SHOW DATABASES"
mysql -h 192.168.50.12 -u haproxy_root -p -e "SHOW DATABASES"

# you should receive the following output
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
# allow haproxy to be started by the init script
sudo sed -i "s/ENABLED=0/ENABLED=1/" /etc/default/haproxy

# edit the config
sudo mv /etc/haproxy/haproxy.cfg{,.original}
sudo vi /etc/haproxy/haproxy.cfg
# start haproxy
sudo service haproxy start

# now send a test query
mysql -h 127.0.0.1 -u haproxy_root -p -e "SHOW DATABASES"

# you should receive the following output
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

HAProxy vs MySQL Proxy

As an alternative, you may also opt to utilize MySQL Proxy, however, it's worth experimenting with first, before you use it in production[2]. There have been various complaints about MySQL Proxy's stability and performance. I'd also invite you to read up on discussions about load balancing MySQL clusters[3], in general.

Conclusion

You should be all set. If you're interested into looking at other ways to alleviate traffic to your datastores, read up on caching with Memcached. Also, make sure to check back in the future for a guide on how to set this up for our MySQL instances.

References:


1. ^ Wikipedia (22 April 2015). "Round-robin Scheduling"
2. ^ Cannaò, René (15 October 2013). "Simple benchmark on MySQL Proxy vs HAProxy"
3. ^ Stack Exchange (13 October 2009). "Load-balanced MySQL cluster without load balancer"

No comments:

Post a Comment