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 9.10.3.3, “Query Cache Configuration”.

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).

Posted by
Vlatko Šurlan
on
July 5, 2010

But you do not want to have MySQL spend a bunch of time sorting the cache index. Apparently there are some production case reports where too big caches caused MySQL to spend over 70% of it's processing time doing just that - sorting it's cache index. Ideally you want queries worth caching in the cache and the rest ignored. Here is a nice little treaty on a few nasty gotchas and tricks that can shave a few hours off of your 'what the heck is going on here' time: http://www.docplanet.org/mysql/mysql-query-cache-in-depth/