Deploy and Configure ProxySQL on Top of MySQL Replication Using ClusterControl

A proxy layer is an important building block for any highly available database environment. HAProxy has been available for a long time, as a generic TCP load balancer. We later added support for MaxScale, an SQL-aware load balancer. We’re today happy to announce support for ProxySQL, which also belongs to the family of SQL-aware load balancers. We blogged about ProxySQL sometime back, in case you would like to read more about it.

As from ClusterControl 1.4, we now support deployment and configuration of ProxySQL. It is done from a simple wizard:

You need to pick a host on which ProxySQL will be installed, you can choose to install it on one of your existing hosts in your cluster or you can type in a new host.

In the next step, you need to pick username and password for administration and monitoring user for ProxySQL.

You also need to define application users. ProxySQL works in the middle, between application and backend MySQL servers, so the database users need to be able to connect from the ProxySQL IP address. The proxy supports connection multiplexing, so not all connections opened by applications onto ProxySQL will result in connections opened to the backend databases. This can seriously increase performance of MySQL – it’s well known that MySQL scales up to some number of concurrent connections (exact value depends mostly on the MySQL version). Now, using ProxySQL, you can push thousands of connections from the application. ProxySQL opens a much lower number of connections to MySQL. It takes advantage of the fact that, for most of the time, connections wait for the application and do not process any query: they can be reused. This feature requires that the application authenticates against ProxySQL, so you need to add all users that you use in your application to ProxySQL.

You can either add existing database users, or you can create a new one from the UI – fill in username and password, pick what grants you want to assign to that user, and you are all set – ClusterControl will create this user for you in both MySQL and ProxySQL.

Finally, you need to pick which hosts you want to include in the ProxySQL configuration, and set some configuration variables like maximum allowed replication lag, maximum number of connections or weight.

At the end of the form you have to answer an important question: do you use implicit transactions or not? This question is mandatory to answer and based on your answer ClusterControl will configure ProxySQL. If you don’t use implicit transactions (by that we mean that you don’t use SET autocommit=1 to initiate a transaction) thenyour read-only traffic will be sent to slaves while your writes will be sent to a writable master. If you use implicit transactions, the only safe method of configuring ProxySQL is to send all traffic to the master. There will be no scale-out, only high availability. Of course, you can always redirect some particular queries to the slaves but you will have to do it on your own – ClusterControl cannot do it for you. This will change in the future when ProxySQL will add support for handling such scenario, but for now we have to stick to what we have.

ProxySQL will also work together with the new automatic failover mechanism (for master-slave replication setups) added in ClusterControl 1.4.0 – once failover happens, ProxySQL will detect the new writable master and route writes to it. It all happens automatically, without any need for the user to take action.

Once deployed, you can use ClusterControl to monitor your ProxySQL installation. You can check the status of hosts in all defined hostgroups. You can also check some metrics related to hostgroups – used connections, free connections, errors, number of queries executed, amount of data sent and received, latency. Below you can find graphs related to some of ProxySQL metrics – active transactions, data sent and received, memory utilization, number of connections and some others. This gives you nice insight in how ProxySQL operates and helps to catch any potential issues with your proxy layer.

So, give it a try and tell us what you think. We plan on adding more management features in the next release, and would love to hear what you’d like to see.