5.13.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
Section 5.2.4, “Server Status Variables”. Some uses for them
are described here.
The total number of SELECT queries is given
by this formula:
Com_select
+ Qcache_hits
+ queries with errors found by parser
The Com_select value is given by this
formula:
Qcache_inserts
+ 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 5.13.3, “Query Cache Configuration”.
