Advanced MySQL Features - High Performance MySQL - page 325

is frequently true). You can keep large results from being cached by lowering the value
of the query_cache_limit variable, which can sometimes help achieve a better balance
between fragmentation and the overhead of storing results in the cache.
You can detect query cache fragmentation by examining the Qcache_free_blocks status
variable, which shows you how many blocks in the query cache are of type FREE . In the
final configuration shown in Figure 7-4 , there are two free blocks. The worst possible
fragmentation is when there's a slightly-too-small free block between every pair of
blocks used to store data, so every other block is a free block. Thus, if Qcache
_free_blocks approaches Qcache_total_blocks / 2 , your query cache is severely frag-
mented. If the Qcache_lowmem_prunes status variable is increasing and you have a lot of
free blocks, fragmentation is causing queries to be deleted from the cache
prematurely.
You can defragment the query cache with FLUSH QUERY CACHE . This command compacts
the query cache by moving all blocks “upward” and removing the free space between
them, leaving a single free block at the bottom. Contrary to its name, it does not remove
queries from the cache; that's what RESET QUERY CACHE does. FLUSH QUERY CACHE blocks
access to the query cache while it runs, which effectively locks the whole server, so be
very careful with it. One rule of thumb for query cache sizing is to keep it small enough
that the stalls caused by FLUSH QUERY CACHE are acceptably short.
Improving query cache usage
If your query cache isn't fragmented but you're still not getting a good hit rate, you
might have given it too little memory. If the server can't find any free blocks that are
large enough to use for a new block, it must “prune” some queries from the cache.
When the server prunes cache entries, it increments the Qcache_lowmem_prunes status
variable. If this value increases rapidly, there are two possible causes:
• If there are many free blocks, fragmentation is the likely culprit (see the previous
section).
• If there are few free blocks, it might mean that your workload can use a larger cache
size than you're giving it. You can see the amount of unused memory in the cache
by examining Qcache_free_memory .
If there are many free blocks, fragmentation is low, there are few prunes due to low
memory, and the hit rate is still low, your workload probably won't benefit much from
the query cache. Something is keeping it from being used. If you have a lot of updates,
that's probably the culprit; it's also possible that your queries are not cacheable.
If you've measured the cache hit ratio and you're still not sure whether the server is
benefiting from the query cache, you can disable it and monitor performance, then
reenable it and see how performance changes. To disable the query cache, set query
_cache_size to 0 . (Changing query_cache_type globally won't affect connections that
are already open, and it won't return the memory to the server.) You can also