MySQL 5.0 Reference Manual  /  ...  /  Query Cache Status and Maintenance Query Cache Status and Maintenance

To check whether the query cache is present in your MySQL server, use the following statement:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
| Variable_name    | Value |
| have_query_cache | YES   |

You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.

The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this.

To monitor query cache performance, use SHOW STATUS to view the cache status variables:

mysql> SHOW STATUS LIKE 'Qcache%';
| Variable_name           | Value  |
| Qcache_free_blocks      | 36     |
| Qcache_free_memory      | 138488 |
| Qcache_hits             | 79570  |
| Qcache_inserts          | 27087  |
| Qcache_lowmem_prunes    | 3114   |
| Qcache_not_cached       | 22989  |
| Qcache_queries_in_cache | 415    |
| Qcache_total_blocks     | 912    |

Descriptions of each of these variables are given in Section 5.1.6, “Server Status Variables”. Some uses for them are described here.

The total number of SELECT queries is given by this formula:

+ Qcache_hits
+ queries with errors found by parser

The Com_select value is given by this formula:

+ Qcache_not_cached
+ queries with errors found during the column-privileges check

The query cache uses variable-length blocks, so Qcache_total_blocks and Qcache_free_blocks may indicate query cache memory fragmentation. After FLUSH QUERY CACHE, only a single free block remains.

Every cached query requires a minimum of two blocks (one for the query text and one or more for the query results). Also, every table that is used by a query requires one block. However, if two or more queries use the same table, only one table block needs to be allocated.

The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache. Tuning information is given in Section, “Query Cache Configuration”.

Download this Manual
User Comments
  Posted by Frank Speiser on December 13, 2004
The optimal value of Qcache_lowmem_prunes is 0. You want to make sure you set the query_cache_size high enough to hold your frequently used queries. MySQL trims what is held in the query_cache when it needs space. Having said that, a total of zero Qcache_lowmem_prunes might not be possible, so you should strive to keep it as low as possible. See: query_cache_size and of course query_cache_type (should be set to 1).
  Posted by Vlatko Šurlan on July 5, 2010
But you do not want to have MySQL spend a bunch of time sorting the cache index. Apparently there are some production case reports where too big caches caused MySQL to spend over 70% of it's processing time doing just that - sorting it's cache index. Ideally you want queries worth caching in the cache and the rest ignored. Here is a nice little treaty on a few nasty gotchas and tricks that can shave a few hours off of your 'what the heck is going on here' time:
Sign Up Login You must be logged in to post a comment.