AppDynamics for Databases
2.9.x Documentation
The MySQL Query Cache can have a huge positive impact on your database performance if you have a database which processes lots of reusable SELECT statements. By reusable I am referring to statements which repeated by multiple users, therefore if user A executes a SELECT, and then user B issues the exact same statement, the MySQL Query Cache will just return the results of the first execution without needing to re-execute the SQL.
What are the Query Cache Key Performance Indicators?
This article addresses what metrics to look for when assessing the benefits of your query cache.
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
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';
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.
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 (as indicated in the sceenshot above) 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.