6.9.4 Query Cache Status and Maintenance

With the FLUSH QUERY CACHE command you can defragment the query cache to better utilise its memory. This command will not remove any queries from the cache. FLUSH TABLES also flushes the query cache.

The RESET QUERY CACHE command removes all query results from the query cache. You can check whether the query cache is present in your MySQL version:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

You can monitor query cache performance in SHOW STATUS:

Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.

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 (big) free block remains.

Note: Every query needs 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 needs one block, but if two or more queries use same table only one block needs to be allocated.

You can use the Qcache_lowmem_prunes status variable to 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.

User Comments

I would be interested in the ability to *see* the
cached queries.

Also, do bind parameters properly come into play
with the query cache? For instance, would the
statement:
SELECT * FROM table_name WHERE id = ?
be cached and return the appropriate results when
executed with various id's?

MySQL does not support bind parameters. If you
are using them, then the API you are using must be
silently expanding them before passing the query
to MySQL. So yes, the query cache will perform
correctly.

I wonder what would be an optimal value
for "Qcache_lowmem_prunes"...

reset query cache doesn't reset the counters pertaning to query_cache(inserts/hists/not cached) in version 4.0.12

Add your own comment.