- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 7.5.3 The MySQL Query Cache
- 7.5.3.1 How the Query Cache Operates
- 7.5.3.2 Query Cache SELECT Options
- 7.5.3.3 Query Cache Configuration
- 7.5.3.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.1.6, “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.3.3, “Query Cache Configuration”.
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
I am using RESET QUERY CACHE automatically every 24h and it appears that it takes like 120 - 180 seconds to complete (my cache is very large). What I don't understand is why this takes so long, my understanding is that this should be faster than FLUSH QUERY CACHE.
how can i cache a union select like
(select a as b from g) union (select c as b from i) order by b
making this only cache the selects not the union...
(select sql_cache a as b from g) union (select sql_cache c as b from i) order by b
?!?!?!
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).
Be careful not to set the query cache too large. If you can get approximately the same percentage of queries served from the cache whether its size is 32MB or 512MB then go with 32MB. This is not only due to the extra 480MB that could be used for key or data cacheing but is also due to the higher overhead of cache lookups and maintenance. In the worst case, performance can actually decrease beyond that of not having any cache at all even though cache utilization is high.
I was also looking for a way to figure out which specific queries were using the cache and which were not, so I wrote a C# application called "MyQCache" to replay a mysql log one query at a time and tell me what the cache did with each query (insert, use cache or ignore cache). It's a free command-line application - more info here:
http://people.stdnet.com/jonathan/default.php?schr101=Software_MyQCache.php
When checking how your cache is performing, it is well worth remembering that if you run
SELECT * FROM t;
SHOW STATUS LIKE 'qc%';
SELECT * FROM t;
SHOW STATUS LIKE 'qc%';
SELECT * FROM t;
SHOW STATUS LIKE 'qc%';
the Qcache_not_cached will increment by 1 every time you run: [SHOW STATUS LIKE 'qc%';] - so don't be confused when you get 3 cache hits and 3 non cache hits at the same time!
Add your own comment.