Magento + MySQL Query Cache: A Case Study
Published: April 1, 2018
The MySQL query cache is one of the most controversial MySQL features. While Percona concluded a blog post titled “The MySQL query cache: Worst enemy or best friend?” by stating that “the MySQL query cache is a good fit” for Magento, one of the world’s leading ecommerce platforms, the MySQL team announced that the query cache will be removed from MySQL 8.0.
On the Magento 1.14 Enterprise Edition site of a client I work with at Something Digital the query cache was inadvertently disabled, and later re-enabled once the issue was caught. This gave us great visibility into the impact the query cache was having on this application. Here I’ll share our findings…
NOTE: Note the application in question was running Magento 1.14 Enterprise Edition
Some Background - Throughput By Database Operation
Before we take a look at the data, it’s worthwhile to get a sense of how the application interacts with the database in terms of reads vs. writes. In “The MySQL query cache: Worst enemy or best friend?”, Percona described Magento’s write workload as “light”, but as you’ll see in the graph below, that’s really a bit of an understatement.
In fact, when looking a throughput by database operation, the INSERT, UPDATE and DELETE queries are barely visible in comparison the SELECT workload.
While this graph just shows you the throughput for one specific Magento site (which has it’s own set of customizations) looking at the same graph on other Magento sites looks more or less the same (for both Magento 1 and Magento 2).
Additionally, while it’s not represented by this visualization, the SELECT queries Magento issues to the database are often quite complex.
With this in mind, let’s look at the impact the query cache had on this application.
The Big Picture
As you can see, when the query cache became disabled for this site, the performance of MySQL suffered severely. The average time spent on MySQL by the application increased from around 200ms per request to closer to around 500ms, a 2.5X increase. Here’s another visualization that shows only time spend on MySQL by the application…
The query cache was very clearly having significant positive impact.
Impact On Specific Queries
There were a few queries that benefited the most from the query cache.
For example, with the query cache on SELECTs against the catalog_product_flat_1 table took an average of 1.39ms.
However, with the query cache off they too over 30X (
) as long with an average of 48ms, making them the most time consuming query.
The second most pronounced case was SELECTs against eav_attribute_option.
With the query cache on they took an average of 9.87ms.
However, with the query cache off they took roughly 10X (
) as long with an average of 97.1ms.
Performance for many other queries was significantly assisted by the query cache, but these two queries offered the best demonstration.
Takeaways
With this in mind, it’s clear that the query cache can sometimes be used to significantly improve application performance. As such, it’s troubling that the MySQL team has chosen to sunset the feature.
In the “MySQL 8.0: Retiring Support for the Query Cache” announcement, the MySQL team advocate the use of ProxySQL in cases where caching is desired. While ProxySQL looks really cool (I haven’t actually used it) I do have serious concerns about the limitations of it’s caching implementation, which only supports invalidations via TTL. Regardless of the solution, engineers at companies such as Magento need to become planning for the future as MySQL 8.0 will be here before you know it.
Hi, I'm Max!