Once you understand how the query cache works, it’s easy to tune. It has only a few
“moving parts”:
query_cache_type
Whether the query cache is enabled. Possible values are OFF, ON, or DEMAND, where
the latter means that only queries containing the SQL_CACHE modifier are eligible
for caching. This is both a session-level and a global variable. (See Chapter 6 for
details on session and global variables.)
query_cache_size
The total memory to allocate to the query cache, in bytes. This must be a multi-
ple of 1,024 bytes, so MySQL may use a slightly different value than the one you
specify.
query_cache_min_res_unit
The minimum size when allocating a block. We explained this setting earlier in
“How the Cache Uses Memory” on page 206; it’s discussed further in the next
section.
query_cache_limit
The largest result set that MySQL will cache. Queries whose results are larger
than this setting will not be cached. Remember that the server caches results as it
generates them, so it doesn’t know in advance when a result will be too large to
cache. If the result exceeds the specified limit, MySQL will increment the
Qcache_not_cached status variable and discard the results cached so far. If you
know this happens a lot, you can add the SQL_NO_CACHE hint to queries you don’t
want to incur this overhead.
query_cache_wlock_invalidate
Whether to serve cached results that refer to tables other connections have
locked. The default value is OFF, which makes the query cache change the
server’s semantics because it lets you read cached data from a table another con-
nection has locked, which you wouldn’t normally be able to do. Changing it to
ON will keep you from reading this data, but it might increase lock waits. This
really doesn’t matter for most applications, so the default is generally fine.
In principle, tuning the cache is pretty simple, but understanding the effects of your
changes is more complicated. In the following sections, we show you how to reason
about the query cache, so you can make good decisions.

Comments are closed.