(mysql.info.gz) Query Cache Status and Maintenance
Info Catalog
(mysql.info.gz) Query Cache Configuration
(mysql.info.gz) Query Cache
5.11.4 Query Cache Status and Maintenance
-----------------------------------------
You can check whether the query cache is present in your MySQL server
using 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 Server
status variables. Some uses for them are described here.
The total number of `SELECT' queries is equal to:
Com_select
+ Qcache_hits
+ queries with errors found by parser
The `Com_select' value is equal to:
Qcache_inserts
+ Qcache_not_cached
+ queries with errors found during columns/rights 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 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 Query Cache Configuration.
Info Catalog
(mysql.info.gz) Query Cache Configuration
(mysql.info.gz) Query Cache
automatically generated byinfo2html