7.4.6. The MyISAM Key Cache
To minimize disk I/O, the MyISAM storage
engine employs a strategy that is used by many database
management systems. It exploits a cache mechanism to keep the
most frequently accessed table blocks in memory:
For index blocks, a special structure called the
key cache (or key
buffer) is maintained. The structure contains a
number of block buffers where the most-used index blocks are
placed.
For data blocks, MySQL uses no special cache. Instead it
relies on the native operating system filesystem cache.
This section first describes the basic operation of the
MyISAM key cache. Then it discusses features
that improve key cache performance and that enable you to better
control cache operation:
To control the size of the key cache, use the
key_buffer_size system variable. If this
variable is set equal to zero, no key cache is used. The key
cache also is not used if the key_buffer_size
value is too small to allocate the minimal number of block
buffers (8).
When the key cache is not operational, index files are accessed
using only the native filesystem buffering provided by the
operating system. (In other words, table index blocks are
accessed using the same strategy as that employed for table data
blocks.)
An index block is a contiguous unit of access to the
MyISAM index files. Usually the size of an
index block is equal to the size of nodes of the index B-tree.
(Indexes are represented on disk using a B-tree data structure.
Nodes at the bottom of the tree are leaf nodes. Nodes above the
leaf nodes are non-leaf nodes.)
All block buffers in a key cache structure are the same size.
This size can be equal to, greater than, or less than the size
of a table index block. Usually one these two values is a
multiple of the other.
When data from any table index block must be accessed, the
server first checks whether it is available in some block buffer
of the key cache. If it is, the server accesses data in the key
cache rather than on disk. That is, it reads from the cache or
writes into it rather than reading from or writing to disk.
Otherwise, the server chooses a cache block buffer containing a
different table index block (or blocks) and replaces the data
there by a copy of required table index block. As soon as the
new index block is in the cache, the index data can be accessed.
If it happens that a block selected for replacement has been
modified, the block is considered “dirty.” In this
case, prior to being replaced, its contents are flushed to the
table index from which it came.
Usually the server follows an LRU (Least Recently
Used) strategy: When choosing a block for
replacement, it selects the least recently used index block. To
make this choice easier, the key cache module maintains a
special queue (LRU chain) of all used
blocks. When a block is accessed, it is placed at the end of the
queue. When blocks need to be replaced, blocks at the beginning
of the queue are the least recently used and become the first
candidates for eviction.
