Databases Reference
In-Depth Information
whether inside or outside the transaction—can be cached until the transaction com-
mits. Long-running transactions can, therefore, increase the number of query cache
misses.
Invalidation can become a very serious problem with a large query cache. If there are
many queries in the cache, the invalidation can take a long time and cause the entire
system to stall while it works. This is because there's a single global lock on the query
cache, which will block all queries that need to access it. Accessing happens both when
checking for a hit and when checking whether there are any queries to invalidate.
Chapter 3
includes a real case study that shows excessive query cache invalidation
overhead.
How the Cache Uses Memory
MySQL stores the query cache completely in memory, so you need to understand how
it uses memory before you can configure it correctly. The cache stores more than just
query results in its memory. It's a lot like a filesystem in some ways: it keeps structures
that help it figure out which memory in its pool is free, mappings between tables and
query results, query text, and the query results.
Aside from some basic housekeeping structures, which require about 40 KB, the query
cache's memory pool is available to be used in variable-sized
blocks
. Every block knows
what type it is, how large it is, and how much data it contains, and it holds pointers to
the next and previous logical and physical blocks. Blocks can be of several types: they
can store cache results, lists of tables used by a query, query text, and so on. However,
the different types of blocks are treated in much the same way, so there's no need to
distinguish among them for purposes of configuring the query cache.
When the server starts, it initializes the memory for the query cache. The memory pool
is initially a single free block. This block is as large as the entire amount of memory the
cache is configured to use, minus the housekeeping structures.
When the server caches a query's results, it reserves a block from its memory pool to
store those results. This block must be a minimum of
query_cache_min_res_unit
bytes,
though it might be larger if the server knows it is storing a larger result. Unfortunately,
the server cannot choose a block of precisely the right size, because it makes its initial
choice before the result set is complete. The server does not build the entire result set
in memory and then send it—it's much more efficient to send each row as it's generated.
Consequently, when it begins caching the result set, the server has no way of knowing
how large it will eventually be.
Assigning blocks is a relatively slow process, because it requires the server to look at
its lists of free blocks to find one that's big enough. Therefore, the server tries to min-
imize the number of times it performs this task. When it needs to cache a result set, it
chooses a block of at least the minimum size (possibly larger, for reasons too complex
to explain) and begins placing the results in that block. If the block becomes full while
