MySQL’s query cache is a useful tool to improve performance and scalability. However, if not implemented correctly, it can do more harm than good…
The query cache offers the potential for substantial performance improvement, but do not assume that it will do so under all circumstances. With some query cache configurations or server workloads, you might actually see a performance decrease.
One critical data point to look at when measuring the effectiveness of query caching is the query cache hit rate. Let’s take a look at how to do that.
There are three server status variables involved in calculating the query cache hit rate…
Each time a query result is returned from cache, MySQL will increment this variable. This is only maintained at a global level.
Each time a query result is inserted into the query cache, this counter will be incremented. This is only maintained at a global level.
Each time a query result is returned without the cache, this counter will be incremented. This is maintained at both a global and a session level. When calculating your query cache hit rate you must fetch this value at a global scope.
SHOW GLOBAL STATUS WHERE Variable_name = 'Com_select';
There are two ways that one can measure query cache hit rate. I would suggest monitoring both.
Not all queries are cache-able. For example, queries using
CURRENT_TIME() are not cacheable. More details on which types of queries MySQL will not cache can be found here.
The formula for measuring your hit rate amongst cacheable queries is…
Qcache_hits / (Qcache_hits + Qcache_inserts)
It’s also interesting to monitor your hit rate amongst all queries. In order to do so, use the following formula…
Qcache_hits / (QCache_hits + Com_select)
Obviously, the higher the hit rate, the more likely it is that query cache is helping. Much of my work is done with Magento, which is typically greatly assisted by the query cache. In the Magento sites I looked at the hit rate was typically ~80 - 85%.
This article suggests that upwards of 50% likely means that query cache is helping. My advise would be to use look at overall application performance with any configuration changes to the query cache to understand whether or not it is helping.
I highly suggest you install the MySQL plugin. Once hooked up it will monitor your query cache hit rate over time for you.
If you have any questions or comments, feel free to drop a note below, or, as always, you can reach me on Twitter as well.
Hi, I'm Max!
I'm a software developer who mainly works in PHP, but also dabbles in Ruby and Go. Technical topics that interest me are monitoring, security and performance.
During the day I solve challenging technical problems at Something Digital where I mainly work with the Magento platform. I also blog about tech, work on open source and hunt for bugs.
If you'd like to get in touch with me the best way is on Twitter.