What's New with ProxySQL in ClusterControl v1.4.2

ClusterControl v1.4.2 comes with a number of improvements around ProxySQL. The previous version, also known as “The ProxySQL Edition”, introduced a full integration of ProxySQL with MySQL Replication and Galera Cluster. With ClusterControl v1.4.2, we introduced some amazing features to help you running ProxySQL at scale in production. These features include running ProxySQL in high availability mode, keeping multiple instances in sync with each other, managing your existing ProxySQL instances and caching queries in one click.

High Availability With Keepalived

ProxySQL can be deployed as a distributed or centralized MySQL load balancer. Setting up a centralized ProxySQL usually requires two or more instances, these are coupled with a virtual IP address that provides a single endpoint for your database applications. This is a proven approach that we have been using with HAProxy. By having a primary ProxySQL instance and a backup instance, your load balancer would not be a single-point-of-failure. The following diagram shows an example architecture of centralized ProxySQL instances with Keepalived:

ClusterControl allows more than two ProxySQL instances to share the same virtual IP address. From the ClusterControl web interface, you can manage them from the Keepalived deployment wizard:

Note that Keepalived is automatically configured with a single-master-multiple-backups approach, where only one ProxySQL instance (assuming one instance per host) will hold the virtual IP address at a time. The rest will act as a hot-standby proxy, unless you explicitly connect to them via the instance IP address. Your application is simplified where you only need to connect to a single virtual IP address and the proxy will take care of load balancing connections on one of the available database nodes.

Add Existing ProxySQL

If you already installed ProxySQL manually, and want to manage it using ClusterControl, use the ‘Import ProxySQL’ feature to add it into ClusterControl. Before importing the instance, ensure ClusterControl is able to perform passwordless SSH to the target host. Once done, simply provide the ProxySQL host, the listening port (default to 6033) and ProxySQL administrator user credentials:

ClusterControl will then connect to the target host via SSH and perform some sanity checks before connecting to ProxySQL admin interface via local socket. You will then see it listed in the side menu of the Nodes tab, under ProxySQL section. That’s it, simple and straightforward. By ticking the ‘Import Configuration’, you can also choose to update the ProxySQL node with the configuration from an existing ProxySQL node.

Sync ProxySQL Instances

It is extremely common to deploy multiple instances of ProxySQL. One would deploy at least one primary and one standby for high availability purposes. Or the architecture might mandate multiple instances, for example one proxy instance per web server. Managing multiple ProxySQL instances can be a hassle though, as they need to be kept in sync – things like configurations, hostgroup definitions, query rules, backend/frontend users, global variables, and so on.

Consider the following architecture:

When you have a distributed ProxySQL deployment like the above, you need a way to synchronize configurations. Ideally, you would want to do this automatically and not rely on manually changing the configurations on each instance. ClusterControl v1.4.2 allows you to synchronize ProxySQL configurations between instances, or simply export and import the configuration from one instance to another.

When changing the configuration on a ProxySQL instance, you can use “Node Actions” -> “Synchronize Instances” feature to apply the configuration to another instance. The following configuration will be synced over:

• Query rules
• Hostgroups and servers
• Users (backend and frontend)
• Global variables
• Scheduler
• The content of ProxySQL configuration file (proxysql.cnf)

Take note that by clicking on “Synchronize”, the existing configuration on the target instance will be overwritten. There is a warning for that. A new job will be initiated by ClusterControl, and all changes will be flushed to disk on the target node to make it persistent across restarts.

Simplified Query Cache

ProxySQL allows you to cache specific read queries, as well as specify how long they should be cached. Resultsets are cached in native MySQL packets format. This offloads the database servers and applications have faster access to cached data. This also reduces the need for a separate caching layer (e.g. Redis or Memcached).

You can now cache any query digested by ProxySQL with a single click. Simply rollover the query that you would like to cache and click “Cache Query”:

ClusterControl will provide a shortcut to add a new query rule to cache the selected query:

Define the Rule ID (query is processed based on this ordering), destination hostgroup and TTL value in milliseconds. Click “Add Rule” to start cache the query. You can then verify from ProxySQL’s Rules page if the current incoming query matches the rule by looking at the “Hits” column:

That’s it for now. You are welcome to install ClusterControl and try out the ProxySQL features. Happy proxying!