Tips for Migrating from HAProxy to ProxySQL

ProxySQL is a dedicated load balancer for MySQL which comes with a variety of features including, but not limited to query redirecting, query caching or traffic shaping. It can be used to easily set up a read-write split and redirect queries to separate backend nodes. As a result, it provides many compelling reasons to use. On the other hand, HAProxy is a great load balancer but it is not dedicated for databases and while it can be used, it cannot be really compared feature-wise with ProxySQL. This might be the reason for environments that still rely on HAProxy to attempt to migrate to ProxySQL. 

In this short blog post we’ll share a couple of suggestions regarding the migration process.

Planning Your Upgrade

This is pretty much obvious and should go without question, but we would still like to have it in writing. Plan your upgrade. Make sure you are familiar with the process, that you tested everything extensively. Set up a test environment in which you can verify different approaches to the upgrade and decide which would work the best for you.

Test Read/Write Split in ProxySQL if You Consider Using It

Depending on your requirements, you may want to consider using read/write split in ProxySQL. This is, probably, one of the most compelling reasons for upgrade. Instead of implementing it on the application side (or not implementing it at all if you cannot accomplish it in the application), you can rely on ProxySQL to perform the read/write split for you. The setup is very easy, especially if you deploy ProxySQL using ClusterControl – it happens pretty much automatically.

As long as you do not use implicit transactions, ClusterControl will set up the read/write split for you using a set of query rules:

Even though it is very simple to implement read/write split, you should exercise caution when you plan to do so. Applications may rely on some functionality that does not really work out of the box in ProxySQL. In most of the cases additional configuration will let you benefit from this feature but it is very important during the test phase to identify if your app will just work or do you need to add some custom configuration. Especially tricky parts are read-after-write issues – in that case you may have to reconfigure ProxySQL to disable connection multiplexing for some of the queries.

Forget About the Configuration File in ProxySQL

This is one of the things that comes as a surprise for new users of ProxySQL. It does not really use configuration files. There is one, yes, but it pretty much acts as a way to bootstrap ProxySQL during the first start. ProxySQL uses a SQLite database that contains its configuration and the proper way of making any configuration changes is through a MySQL client connected to the administrative port of ProxySQL. From there you can make the configuration changes in runtime, pretty much without any need to restart ProxySQL.

Of course, ClusterControl UI also allows you to reconfigure the ProxySQL:

 

 

ProxySQL Deployment Patterns

There are two main ways in which you want to deploy ProxySQL. You can either use a dedicated servers to deploy ProxySQL on:

Or you can collocate ProxySQL with application servers:

This lets your application connect to the local ProxySQL instance using Unix socket, which is better performance-wise than using a remote TCP connection. It also simplifies the setup – there is no need to implement Keepalived or some other Virtual IP provider to load balance across ProxySQL instances. Application only connects to the local ProxySQL and that’s pretty much it.

Use ProxySQL Clusters for Larger Deployments

Making sure your ProxySQL instances contain the same configuration all the time might be challenging, especially if their numbers are large. There are numerous ways of dealing with such challenges – Ansible/Chef/Puppet, shell scripts and so on. We would suggest to rely on the built-in solution – ProxySQL Cluster. With just a couple of configuration changes you can configure ProxySQL nodes to form a cluster where a configuration change on one of the nodes will be propagated across all of the members of the cluster.

Tinker With SO_REUSEPORT for Graceful Load Balancer Switching

One of the more challenging parts might be to ensure that you will switch the traffic from HAProxy to ProxySQL in a way that it will minimize the impact to the application. Typically you would have to change at least one setting – hostname or port that the application should connect to. Depending on your environment, this might be not ideal, especially if the database connectivity configuration is built in the application. It would, pretty much, require making a change in the code base and pushing a new code to the production. Not the biggest of deals but you can do better than that.

The interesting part is that both ProxySQL and recent versions of HAProxy (starting from 1.8) are able to utilize SO_REUSEPORT. This socket option is available in Linux starting from the 3.9 kernel and it allows multiple processes to share the same port. ProxySQL can use it for graceful upgrades between ProxySQL versions, HAProxy uses it to reload configuration without any impact to the application. What is interesting, it is possible to configure ProxySQL to share the port with HAProxy for the seamless migration between those two load balancers.

There are a couple of things you have to consider when attempting to do this – first, ProxySQL does not use this option by default, you have to add -r flag to ProxySQL at startup. You can do that by editing ProxySQL systemd unit file:

root@vagrant:~# systemctl edit proxysql --full

and changing ExecStart directive to:

ExecStart=/usr/bin/proxysql -c /etc/proxysql.cnf -r

Another limitation that you should be aware of in Linux is that only processes started by the same user ID may share the port. This will mean that you will have to reconfigure ProxySQL to be executed as a “haproxy” user.

As usual, you may want to run tests before you attempt to perform this operation in a production environment. It is definitely possible to accomplish this feat but you should exercise caution and double-check that it won’t impact your production because of some sort of non-standard configuration related to your environment.

We hope this short blog will give you some insight into the migration process from HAProxy to ProxySQL. For the database backends this change will be very beneficial, even if the preparation part might be time-consuming. If you go through proper testing, the final migration should be fairly straightforward and safe.