Databases Reference
In-Depth Information
In addition, MySQL provides no good way to determine how beneficial the query cache
is for individual queries,
17
because the counters in
SHOW STATUS
are aggregated over the
whole workload. But the average behavior usually isn't really interesting. For example,
you might have one slow query that becomes much faster with the help of the query
cache, even though it makes everything else a little bit slower or even makes the server
slower on average. Is this what you want? It might actually be the right thing to do, if
the queries that get faster are ones to which users are very sensitive and the others aren't
so important. This would be a good candidate for selective use of the cache with the
SQL_CACHE
directive.
The type of query that benefits most from caching is one whose result is expensive to
generate but doesn't take up much space in the cache, so it's cheap to store, return to
the client, and invalidate. Aggregate queries, such as small
COUNT()
results from large
tables, fit into this category. However, some other types of queries might be worth
caching, too. As a rule of thumb, you can consider the query cache if your workload is
dominated by complex
SELECT
queries, such as multitable joins with
ORDER BY
and
LIMIT
clauses, which produce small result sets. You should have very few
UPDATE
,
DELETE
, and
INSERT
queries in comparison to these complex
SELECT
queries.
One of the ways to tell if you are benefiting from the query cache is to examine the
query cache hit rate. This is the number of queries that are served from the cache instead
of being executed by the server. When the server receives a
SELECT
statement, it incre-
ments either the
Qcache_hits
or the
Com_select
status variable, depending on whether
the query was cached. Thus, the query cache hit rate is given by the formula
Qcache_hits / (Qcache_hits+Com_select)
.
Unfortunately, the cache hit rate isn't easy to interpret. What's a good cache hit rate?
It depends. Even a 30% hit rate can be very helpful, because the work saved by not
executing queries could be much more (per query) than the overhead of invalidating
entries and storing results in the cache. It is also important to know which queries are
cached. If the cache hits represent the most expensive queries, even a low hit rate could
save work for the server. So there is no simple rule that tells you whether the query
cache hit rate is good or not.
Any
SELECT
query that MySQL doesn't serve from the cache is a
cache miss
. A cache
miss can occur for any of the following reasons:
• The query is not cacheable, either because it contains a nondeterministic construct
(such as
CURRENT_DATE
) or because its result set is too large to store. Both types of
uncacheable queries increment the
Qcache_not_cached
status variable.
• The server has never seen the query before, so it never had a chance to cache its
result.
17. The enhanced “slow query log” in Percona Server and MariaDB reveals whether individual queries were
cache hits.
