One of the more interesting products to hit the spotlight at this year's Percona Live Open Source Database conference was ProxySQL.
This open source MySQL proxy server has been around for a couple of years now and keeps adding more features. The current release (1.3.6) has the usual features that you would expect from a proxy server, like load balancing and failover support, but ProxySQL also has database specific features like a query cache and query routing.
The query cache is a scaling feature that serves data at the proxy layer to avoid contacting the database at all. This feature has become very relevant now that Oracle has announced that they're most likely going to remove the query cache from MySQL in MySQL 8.0. Query routing can map query types to specific servers for very specific forms of load-balancing and read/write splitting. Other interesting of features of ProxySQL that are specific to database connections are query blocking, which allows bad queries to be filtered out before they bring a server down and connection pooling, which can be used reduce connection overhead on the database server. ProxySQL also keeps track of session variables in those connections and can retry queries so the application doesn't have to.
Alternatives to ProxySQL
So what are the alternatives to ProxySQL? At the application level the only competitor is MariaDB MaxScale, while at the transport layer you can choose between HAProxy and MySQL Router.
MySQL Router is built specifically to support MySQL group replication so it's applicability to other topologies is either limited or non-existent. For example, MySQL master/slave replication is not fully supported and Galera replication is not supported at all. Obviously ProxySQL and MaxScale are the only two proxies that communicate through the MySQL native protocol, but ProxySQL also supports MySQL connection compression which MaxScale does not.
On the other hand MaxScale handles both frontend SSL encryption and backend SSL encryption, where ProxySQL only offers backend SSL encryption. Perhaps more important than differences in the feature set, MaxScale is not open source but is released under the Business Source License, which means it's free to use until a specified date where you have to start paying licensing fees.
ProxySQL and Drupal
So to see how ProxySQL can work together with Drupal let's take a look at how we use MySQL replication at Acquia.
Our setup typically consists of two MySQL masters with one being designated as the active database server which gets all the connections from Drupal. In case of connection issues we fail over to the other master which is handled in the PHP database connection layer.
By installing ProxySQL on each of the web servers and configuring it to connect to the active database server we can remove the extra layer in the application and let the proxy take care of the rest. ProxySQL will detect if a server is down and switch traffic to the other server. If we want to force a failover we simply set the currently active master to read-only and ProxySQL will fail over.
Of course this is basically what the other proxy server alternatives also do. The extra features is where ProxySQL shows a lot of potential. By having a query cache at the proxy layer we can potentially get a much higher query throughput and lower latency because network connections are no longer necessary for those queries. It also sidesteps the problem that the MySQL query cache is single-threaded and can be an obstacle for scaling database servers that have a very high traffic volume.
Similarly, if we find queries that run well past the PHP timeouts they typically have a big impact on server performance. With ProxySQL we can block these queries at the proxy server since we know that the PHP process will error out in the end anyway. ProxySQL also allows query mirroring which allows us to send the queries to two backends simultaneously. This will be really good for internal use as we prepare for upgrades. It will allow us to test whether the new version of MySQL will perform the same as the old one and identify performance issues up front.
Read/Write Splitting
I haven't mentioned read/write splitting because that is a not a trivial problem for Drupal. Using read replicas with asynchronous replication it is very likely that the read replica will serve stale data which can cause issues on the web site. Drupal has its own support for read replicas which is is the ability to mark views as read replica safe but that is very specific to views and therefore very dependent on how developers build their Drupal application. For some sites this is a lot more feasible than for others.
Either way, read/write splitting is not something that can be done at the proxy level without impacting the application. It is possible that we will offer this in the future to specific customers who have have built their site in such a way that stale data is acceptable and at that point we will be looking at ProxySQL to implement it.
Conclusion
All in all ProxySQL looks like a very promising product and we'll be investigating it here at Acquia to see if we can add it to our stack.
