Databases Reference
In-Depth Information
• The query's result was previously cached, but the server removed it. This can hap-
pen because there wasn't enough memory to keep it, because someone instructed
the server to remove it, or because it was invalidated (more on invalidations in a
moment).
If your server has a lot of cache misses but very few uncacheable queries, one of the
following must be true:
• The query cache is not warmed up yet. That is, the server hasn't had a chance to
fill the cache with result sets.
• The server is seeing queries it hasn't seen before. If you don't have a lot of repeated
queries, this can happen even after the cache is warmed up.
• There are a lot of cache invalidations.
Cache invalidations can happen because of fragmentation, insufficient memory, or
data modifications. If you have allocated enough memory to the cache and configured
the
query_cache_min_res_unit
value properly, most cache invalidations should be due
to data modifications. You can see how many queries have modified data by examining
the
Com_*
status variables (
Com_update
,
Com_delete
, and so forth), and you can check
the
Qcache_lowmem_prunes
variable to see how many queries have been invalidated due
to low memory.
It's a good idea to consider the overhead of invalidation separately from the hit rate.
As an extreme example, suppose you have one table that gets all the reads and has a
100% query cache hit rate, and another table that gets only updates. If you simply
calculate the hit rate from the status variables, you will see a 100% hit rate. However,
the query cache can still be inefficient, because it will slow down the update queries.
All update queries will have to check whether any of the queries in the query cache
need to be invalidated as a result of their modifications, but since the answer will always
be “no,” this is wasted work. You might not spot a problem such as this unless you
check the number of uncacheable queries as well as the hit rate.
A server that handles a balanced blend of writes and cacheable reads on the same tables
also might not benefit much from the query cache. The writes will constantly invalidate
cached results, while at the same time the cacheable reads will constantly insert new
results into the cache. These will be beneficial only if they are subsequently served from
the cache.
If a cached result is invalidated before the server receives the same
SELECT
statement
again, storing it was a waste of time and memory. Examine the relative sizes of
Com_select
and
Qcache_inserts
to see whether this is happening. If nearly every
SELECT
is a cache miss (thus incrementing
Com_select
) and subsequently stores its result
into the cache,
Qcache_inserts
will be nearly as large as
Com_select
. Thus, you'd like
Qcache_inserts
to be much smaller than
Com_select
, at least when the cache is properly
warmed up. However, this is still a hard-to-interpret ratio because of the subtleties of
what's happening inside the cache and the server.
