By default, the key cache management system of MySQL 4.1 uses a simple LRU strategy for choosing key cache blocks to be evicted, but it also supports a more sophisticated method called the midpoint insertion strategy.
When using the midpoint insertion strategy, the LRU chain is
divided into two parts: a hot sublist and a warm sublist. The
division point between two parts is not fixed, but the key
cache management system takes care that the warm part is not
“too short,” always containing at least
percent of the key cache blocks.
key_cache_division_limit is a
component of structured key cache variables, so its value is a
parameter that can be set per cache.
When an index block is read from a table into the key cache, it is placed at the end of the warm sublist. After a certain number of hits (accesses of the block), it is promoted to the hot sublist. At present, the number of hits required to promote a block (3) is the same for all index blocks.
A block promoted into the hot sublist is placed at the end of
the list. The block then circulates within this sublist. If
the block stays at the beginning of the sublist for a long
enough time, it is demoted to the warm sublist. This time is
determined by the value of the
component of the key cache.
The threshold value prescribes that, for a key cache
N blocks, the block at
the beginning of the hot sublist not accessed within the last
hits is to be moved to
the beginning of the warm sublist. It then becomes the first
candidate for eviction, because blocks for replacement always
are taken from the beginning of the warm sublist.
key_cache_age_threshold / 100
The midpoint insertion strategy enables you to keep
more-valued blocks always in the cache. If you prefer to use
the plain LRU strategy, leave the
value set to its default of 100.
The midpoint insertion strategy helps to improve performance
when execution of a query that requires an index scan
effectively pushes out of the cache all the index blocks
corresponding to valuable high-level B-tree nodes. To avoid
this, you must use a midpoint insertion strategy with the
to much less than 100. Then valuable frequently hit nodes are
preserved in the hot sublist during an index scan operation as