Databases Reference
In-Depth Information
query_cache_size
The total memory to allocate to the query cache, in bytes. This must be a multiple
of 1,024 bytes, so MySQL might 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 previously;
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 connection 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, configuring the cache is pretty simple, but understanding the effects of
your changes is more complicated. In the following sections, we'll try to help you make
good decisions.
Reducing fragmentation
There's no way to avoid all fragmentation, but choosing your
query_cache_min
_res_unit
value carefully can help you avoid wasting a lot of memory in the query
cache. The trick is to balance the size of each new block against the number of alloca-
tions the server has to do while storing results. If you make this value too small, the
server will waste less memory, but it will have to allocate blocks more frequently, which
is more work for the server. If you make it too large, you'll get too much fragmentation.
The trade-off is wasting memory versus using more CPU cycles during allocation.
The best setting varies with the size of your typical query result. You can see the average
size of the queries in the cache by dividing the memory used (approximately
query_cache_size — Qcache_free_memory
) by the
Qcache_queries_in_cache
status vari-
able. If you have a mixture of large and small results, you might not be able to choose
a size that avoids fragmentation while also avoiding too many allocations. However,
you might have reason to believe that it's not beneficial to cache the larger results (this
