As a carry-over from my WordPress work, I host a few WordPress sites for friends and family on a virtual private server on SSDnodes. One of my focus areas in my WordPress side hustle was website performance – the faster the website, the more likely your end customer will spend time reading your content and using your product/services. And besides, Google loves fast websites and gives them a rating bump in search listings. It’s a win/win. In a future post, I’ll describe my current hosting layout in more detail, but in a nutshell, I use docker, distinct Apache/WordPress containers per site pointing to a shared MariaDB instance. In one of my late night rabbit holes, I was playing with MySQL Master/Slave database replication and considering how this could be used to improve website performance. This is where I discovered ProxySQL.
ProxySQL is an open-source product that is a high-performance, database protocol-aware proxy for MySQL. In short, ProxySQL sits between your web application servers and your database servers. Once ProxySQL is configured to know about your database backend servers, simply change your web application to point to ProxySQL instead of directly at the database backend – and then you can start to really make use of the features.
Read / Write splits
I won’t go into how to set up Master/Slave replication in MySQL – you can quickly find many tutorials on Google. In addition to having an off-server live backup of the MariaDB instance, the plan was to offload any read-only SQL requests to one of the MySQL slave instances and allow the master instance to purely handle the resource-intensive write operations. The ProxySQL documentation on “How To Setup ProxySQL read/write split” walks you through this process. My only critique here is that there is a small inconsistency between the hostgroup configurations found in the initial “How to configure ProxySQL for the first time” – the initial guide uses hostgroup_id 2 for read-only where the read/write guide uses hostgroup_id 20. This is a small whinge but I’ll admit I was caught out by this at a moment of caffeine deprivation.
There are several examples provided in the read/write guide to help find resource-expensive queries. It then becomes a small matter (?) of investigating those queries to see which ones should be pointed at the Slave instance or whether to implement ..
Query Result Caching
After a few days of letting WordPress use ProxySQL, I discovered some interesting insights. Some themes and/or plugins regularly hit the database layer for read-only queries with results that weren’t changing frequently. For example, do consumers of the blog content need to have up-to-the-millisecond updates on comment counts? Probably not. Let’s cache these requests for 5 seconds (for example) and take some pressure off the database layer. Even without having to setup Master/Slave database replication, ProxySQL makes it simple to implement query result caching, with tuneable cache time to live (ttl) for each specific query.
But Wait, There’s More
The feature set of ProxySQL is pretty rich, and I don’t think I will get to utilise things like database sharding for my future application. However, if my application needs to grow to that extent, then it is comforting to know that it won’t necessarily require an application rewrite to make it happen.