Does SQL Query Cache matters on shared hosting?
Query cache, once a popular feature of MySQL database engine was removed from it many years ago. Developers from MariaDB kept it and that was a reason for us, as a hosting company, to use MariaDB on all our shared-hosting servers with query cache being enabled.
Several weeks ago I had an amazing zoom session with Adam Smith, co-founder of new control clustered control panel Enhance and he pointed out, that indeed – with synthetic tests of wpbenchmark.io you might get a feeling, that MariaDB has a huge advantage over classical MySQL8. This made me think and I went on doing the following experiment.
I have picked 5 shared hosting servers, which run latest MariaDB 10.6 and disabled query cache. Besides MariaDB – all these servers are running CloudLinux and cPanel on top of it. On average each server has 600-800 accounts. And now let’s look at the graphs of them with and without query cache. Unfortunately we do not have any similar servers with MySQL8, therefore I can not compare, which would be amazing to see.. But it is what it is, sorry folks – I will do MariaDB vs MySQL vs Percona comparison a bit later.
Thoughts about results
Graphs above contain metrics from 5 different servers. I have positioned it, so that first half of each are metrics with query cache enabled and then – with query cache disabled. Thin vertical red line shows moment, when SQL query cache was disabled.
To be honest – I am very surprised, that there’s almost no significant change in CPU, disk or memory usage. Yes, we can clearly see peak in table scan (last metric), but in general – disabled query cache made almost no difference.
Yes – the first server (the left side) is a bit different. This particular server hosts several big webshops, that run on some very old and super inefficient code, that is not being well maintained. It puts significant load on database in terms of number of small SQL queries and here we see increase in CPU usage. I can definitely say, that first server benefits from enabled SQL query cache, but that relates to specific systems, that are hosted there.
But in general – all five servers are shared hosting servers with various systems (many WordPress sites) and SQL query cache is not mandatory anymore. If you have fast disks, if you have fast CPU, if some of your sites or system components use other cache subsystems and you are hosted on a shared hosting – query cache will not make significant difference.
I hope this was helpful! 🙂
P.S: when time comes, next cPanel server will be configured with MySQL8, so hopefully in about a few months we could do another relatively fair comparison.