Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes I just started dabbling with the query cache for our CMS.

Can anyone tell me (or at least give a good guess) why I get a lot of Qcache_lowmem_prunes when more than half of Qcache_free_memory is free?

query_cache_size=512M
query_cache_limit=1M


This is how it looks after about 12 hours

show status like '%qcach%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 10338     | 
| Qcache_free_memory      | 297348320 | 
| Qcache_hits             | 10254104  | 
| Qcache_inserts          | 6072945   | 
| Qcache_lowmem_prunes    | 725279    | 
| Qcache_not_cached       | 2237603   | 
| Qcache_queries_in_cache | 48119     | 
| Qcache_total_blocks     | 111346    | 
+-------------------------+-----------+


This is how it looked after flush query cache;

show status like '%qcach%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         | 
| Qcache_free_memory      | 443559256 | 
| Qcache_hits             | 10307015  | 
| Qcache_inserts          | 6115890   | 
| Qcache_lowmem_prunes    | 725279    | 
| Qcache_not_cached       | 2249405   | 
| Qcache_queries_in_cache | 26455     | 
| Qcache_total_blocks     | 54490     | 
+-------------------------+-----------+
The query cache is a very nice feature, but don't be tempted to pay too much attention to it and don't be tempted to make it too large.  Understanding some of its internals will probably help in that regard.

The query cache starts out as one big contiguous chunk of available memory.  Then "blocks" are carved out of this big block:


each cached query takes a block
its companion resultset takes a block
each table referenced by any cached query (no matter how many queries referencing that table are in the cache) also takes a block, one per table.


Block size is dynamic, but the server allocates a minimum of query_cache_min_res_unit bytes per block, with a typical default of 4096 bytes.

Any time queries, their accompanying results, and table references are removed from the cache, either by becoming invalidated by the underlying tables changing or by pruning to make room for newer queries, this leaves new holes the size of however large those blocks were, and the number of "free blocks" usually increases... although if two or more contiguous blocks are freed, the number of "free blocks" only increases by 1, and the "free blocks" won't increase at all if the newly-freed blocks are contiguous with an already free block -- the size of that free block just becomes larger.  Any open block of free memory in the query cache is counted as 1 free block.

Of course, a free block smaller than query_cache_min_res_unit won't be used at all.

So, the query cache fragments.  If the server wants to cache a new query and no free blocks of sufficient size can be arranged (that description is deceptively simple, because the underlying algorithm is complicated), something else has to be pruned... that's your Qcache_lowmem_prunes.  There's a "least-recently-used" (LRU) algorithm that decides what gets pruned.

It would be sensible to ask why the server doesn't defragment the memory... but that wouldn't make sense.  The query cache helps when it can but it's not at all strategic.  You don't want to invest processing time (especially time spent in a global lock) with unnecessary maintenance tasks.

It would be counter-productive for the server to spend time rearranging -- defragmenting -- the memory in the query cache, since the cached results are constantly changing and the whole point of the cache is to improve performance.

The global lock is a very good reason why you don't want to use an excessively large query cache... the server will spend too much time there as queries wait their turn to see if they happen to be cached and your performance will suffer.

But the qcache_free_blocks is essentially an indicator of free-space fragmentation.  That's now many noncontiguous blocks of available memory exist in the query cache.  For a new query to be inserted into the cache, there has to be a a large enough chunk of free space to contain the query, its results, and (sometimes) its table references.  If there isn't, then something else has to go... which is what you're seeing.  Note, again, that the available space doesn't always necessarily have to be contiguous (from what I can tell by reading the source code), but not every hole will be filled when there's fragmentation.

But the fragmentation tends to level out over time, for a given workload, since nothing usually stays in the query cache for as long as you might expect.

This is because, in some ways, the query cache is brilliant in its simplicity.  

Any time the data in a table referenced by a cached query changes, all of the queries that involved that table are removed from the cache -- even if the change doesn't impact the cached results.  This is even true if a table changes, but doesn't change, as in the case of an InnoDB transaction that is rolled back.  The query cache entries referencing that table were already purged.

Also, the query cache is checked for each incoming query before the server actually parses the query.  The only thing that will match is another query that was precisely the same, byte-for-byte.  SELECT * FROM my_table and select * from my_table are not byte-for-byte identical, so the query cache doesn't realize they're the same query. 

FLUSH QUERY CACHE does not empty the query cache.  It defragments the query cache, which is why Qcache_free_blocks becomes "1."  All of the free space is consolidated.

RESET QUERY CACHE actually flushes (clears out all of the contents of) the query cache.

FLUSH STATUS clears the counters, but this isn't something you want to do routinely because this zeroes out most of the status variables in SHOW STATUS.

Here are some quick demonstrations.

Baseline: 

mysql&gt; show status like '%qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091120 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+


Run a query...    

mysql&gt; select * from junk where id = 2;


The total blocks has increased by 3, inserts by 1 and queries in cache is 1.

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67089584 |
| Qcache_inserts          | 1        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+


Run the same query, but with different capitalization...

mysql&gt; SELECT * FROM junk where id = 2;


This query was cached separately.  Total blocks only increased by 2 because we already had a block allocated for the table.

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67088560 |
| Qcache_inserts          | 2        |
| Qcache_queries_in_cache | 2        |
| Qcache_total_blocks     | 6        |
+-------------------------+----------+


Now, we change a different row in the table.

mysql&gt; update junk set things = 'items' where id = 1;


Both queries and the table reference are invalidated from the cache, leaving us with 1 contiguous free block, all of the cache memory freed, and all of the free space consolidated in one block.

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 67091120 |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+


MySQL will not store a query in the cache that isn't deterministic -- such as SELECT NOW(); or any query that you tell it specifically not to cache.  SELECT SQL_NO_CACHE ... is the directive to tell the server not to store the results in the cache.  It's useful for benchmarking the true execution time of a query when the cache is giving you a deceptively fast response on subsequent executions.