(mysql.info) myisam-key-cache

Info Catalog

(mysql.info) mysql-indexes

(mysql.info) optimizing-database-structure

(mysql.info) myisam-index-statistics

 
 7.4.6 The `MyISAM' Key Cache
 ----------------------------
 

Menu

 
* shared-key-cache             Shared Key Cache Access
* multiple-key-caches          Multiple Key Caches
* midpoint-insertion           Midpoint Insertion Strategy
* index-preloading             Index Preloading
* key-cache-block-size         Key Cache Block Size
* key-cache-restructuring      Restructuring a 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:
 
    * Access to the key cache no longer is serialized among threads.
      Multiple threads can access the cache concurrently.
 
    * You can set up multiple key caches and assign table indexes to
      specific caches.
 
 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.
 

Info Catalog

(mysql.info) mysql-indexes

(mysql.info) optimizing-database-structure

(mysql.info) myisam-index-statistics