query_cache_size

What are the Query Cache Key Performance Indicators?

* See previous DBTuna.com article for a basic description of the MySQL Query Cache   http://www.dbtuna.com/article.asp?id=29 

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 (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.

To find out your query cache effectiveness, and to monitor your MySQL Servers 24x7 why not trial dbtuna now? Download Here

Copyright ©2010 DBTuna. All rights reserved.