In this blog post, we’ll look at how to use ProxySQL to help the database load by handling PINGs.
I’ve blogged before about one of our regular clients, Life360. One of the issues they recently had was the PING command taking about 30%-40% of total queries per second across their database infrastructure. This is a non-trivial amount and was easily tens of thousands of pings per second. This added a significant amount of latency to real queries.
A large number of pings is due to the use of PHP PDO with persistent connections. Persistence, or pooling, is necessary to reduce time spent on connecting, disconnecting and reconnecting.
Unfortunately, in PHP (and other) implementations, the driver checks if the database is still alive with a PING before sending the actual command. Logic dictates that you could use the actual command as the PING, and if it fails it could return the same error it would have if the ping itself failed. Baron Schwartz has a lot to say about how unwise the use of a PING is within the drivers.
Barring rewriting PHP PDO, we thought up another solution: ProxySQL.
Before testing ProxySQL, we didn’t know how much gets forwarded to the actual hosts (including these com_admin commands). We wanted to test a quick PoC to discover what the actual behavior of ProxySQL was with respect to these commands. We had two hypothesis that we wanted to check:
- ProxySQL forwards everything including com_commands
- ProxySQL responds to the com_commands itself
In the event that ProxySQL forwarded everything, we set up a “decoy” MySQL instance to respond to the pings using ProxySQL query filtering. As it turned out, we found that ProxySQL quickly and silently replies to PINGs and doesn’t forward it onto the underlying database server backend. This is the case for other commands as well, as ProxySQL isn’t strictly a forwarding proxy (but more of a reverse proxy).
By placing ProxySQL on the application servers, Life360 was able to reduce QPS significantly. The other advantage of introducing ProxySQL is that it does connection pooling and multiplexing for you.
Here is the graph of com_ping on the day of the deployment:
Overall, we are talking about hundreds of millions of pings per day down to 0.
We can see that the vanilla install of ProxySQL also reduced active threads significantly:
This change has enabled Life360 to put off some of their scaling plans and provided other operational gains.
In conclusion, you can use ProxySQL as a simple (or advanced) firewall between your application and database. It consumes very little resources, but provides an immense performance gain.
While ProxySQL can be used as a more advanced firewall, these features are beyond the scope of this post. There are very specific ways to configure it as an advanced firewall. We plan to blog more on this soon.
I would have thought that PHP will issue a COM_CHANGE_USER once it re-uses a connection from the pool (I don’t know how clever the implementation actually is, e.g. does it only store file descriptors hashed by server or the whole combo of server + user + db), but the COM_PING is pretty asinine, often made worse by the fact that most PHP Frameworks don’t actually handle Database errors very well…
Running a local SQL Proxy usually makes sense just for the sake of improved connection pooling.
Nice sharing, what does “decoy” MySQL instance mean?
As far as I understand it they set up another MySQL instance and configured ProxySQL to forward Pings to that instance. They instead found out that ProxySQL can itself answer the pings, saving a round trip when deployed locally.
understood, thank you Nils