There are times when you may have to tweak the MySQL query cache to ensure optimal performance, so let’s review some of the more important query cache-related status counters and configuration variables. To begin, the status counter Qcache_free_blocks indicates the contiguous nature of the memory assigned to the cache. High numbers can indicate fragmentation issues, which may be solved by issuing a FLUSH QUERY CACHE statement. Note that this command does not remove queries from the cache, but coalesces memory free space chunks.
The Qcache_free_memory counter provides insight into the cache’s free memory. Low amounts observed vs. total allocated for the cache may indicate an undersized cache, which can be remedied by altering the global variable query_cache_size.
Qcache_hits and Qcache_inserts shows the number of times a query was serviced from the cache and how many queries have been inserted into the cache. Low ratios of hits to inserts indicate little query reuse or a too-low setting of the query_cache_limit, which serves to govern the RAM devoted to each individual query cache entry. Large query result sets will require larger settings of this variable.
Another indicator of poor query reuse is an increasing Qcache_lowmem_prunes value. This indicates how often MySQL had to remove queries from the cache to make use for incoming statements. Other reasons for an increasing number of Qcache_lowmem_prunes are an undersized cache, which can’t hold the needed amount of SQL statements and result sets, and memory fragmentation in the cache which may be alleviated by issuing a FLUSH QUERY CACHE statement. You can remove all queries from the cache with the RESET QUERY CACHE command.
The Qcache_not_cached counter provides insight into the number of statements executed against MySQL that were not cacheable, due to either being a non-SELECT statement or being explicitly barred from entry with a SQL_NO_CACHE hint.
Other server variables that you can use to tweak the query cache are:
query_alloc_block_size- the allocation size of the RAM blocks that are allocated for objects in cache.query_cache_min_res_unit- the minimum size for blocks allocated by the cache.query_cache_wlock_invalidate- causes the query cache to invalidate any query in the cache if an object it uses has a write lock executed against it.query_prealloc_size- the size of the persistent buffer used by the cache for parsing and execution. Complex queries necessitate larger settings.
Finally, in addition to setting query cache variables that globally govern its use, note that the query cache can be individually managed at the client level. For example, a client can turn off the query cache for their own queries by issuing the statement:
mysql> set session query_cache_type=0;