This article addresses what metrics to look for when assessing the benefits of your query cache.
1. Current Size compared with maximum available size. To calculate the percentage used value for the query cache you can use the following formula:
((query_cache_size-Qcache_free_memory)/query_cache_size)*100
N.B. query_cache_size is a variable, which can be found from a show variables like ‘query_cache_size’; command. Qcache_free_memory is a status variable which can be retrieved from show status like ‘Qcache_free_memory’;
2. The Query Cache Hit Rate
The percentage hit rate on the cache can be calculated as follows:
((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)
This percentage figure shows how much the query cache is used e.g. the figure in the screenshot of 33% says that of all select statements executed, 33% of them can be satisfied by the cache and hence do not have to be re-executed.
3. Hits to Insert Ratio and Insert to Prune Ratio
These two ratios are calculated by the following two formulae:
Qcache_hits/Qcache_inserts
Qcache_inserts/Qcache_prunes
A ratio of Hits to Inserts is displayed in order to show the Query Cache effectiveness. A high ratio of hits to inserts tells us that there are lots of identical SQL statements being run on the database and are therefore being serviced directly from cache. A low ratio shows that the cache is not much utilized.
The ratio of Inserts to Prunes represents how many times SQL queries are being inserted into the cache compared with how many times a query is being removed from the cache (pruned). This is also a good indicator of SQL reuse on the database and hence query cache effectiveness.
