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.7, “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 7.5.5.3, “Query Cache Configuration”.
User Comments
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).
Add your own comment.