[MySQL]: Buffering and Caching 

          MySQL uses several strategies that cache information in memory buffers to increase performance. Depending on your database architecture, you balance the size and layout of these areas, to provide the most performance benefit without wasting memory or exceeding available memory.

  The MyISAM Key Cache 

          To minimize disk I/O, the MyISAM storage engine exploits a strategy that is used by many database management systems. It employs 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 file system cache.

          – Multiple sessions 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 file system 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.) 

          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. 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 all used blocks in a special list (LRU chain) ordered by time of use. When a block is accessed, it is the most recently used and is placed at the end of the list. When blocks need to be replaced, blocks at the beginning of the list are the least recently used and become the first candidates for eviction. 

Shared Key Cache Access: 

          Threads can access key cache buffers simultaneously, subject to the following conditions: 

          – A buffer that is not being updated can be accessed by multiple sessions.

          – A buffer that is being updated causes sessions that need to use it to wait until the update is complete.

          – Multiple sessions can initiate requests that result in cache block replacements, as long as they do not interfere with each other (that is, as long as they need different index blocks, and thus cause different cache blocks to be replaced). 

          Shared access to the key cache enables the server to improve throughput significantly 

Multiple Key Caches: 

          Shared access to the key cache improves performance but does not eliminate contention among sessions entirely. They still compete for control structures that manage access to the key cache buffers. To reduce key cache access contention further, MySQL also provides multiple key caches. This feature enables you to assign different table indexes to different key caches.

          The separate key cache can be created by setting its size with a SET GLOBAL parameter setting statement or by using server startup options. For example: 

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024; 

The following statement assigns indexes from the tables t1, t2, and t3 to the key cache named keycache1: 

mysql> CACHE INDEX t1, t2, t3 IN keycache1; 

The key cache referred to in a CACHE INDEX statement can be created by setting its size with a SET GLOBAL parameter setting statement or by using server startup options. For example: 

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024; 

For a busy server, you can use a strategy that involves three key caches: 

          – A “hot” key cache that takes up 20% of the space allocated for all key caches. Use this for tables that are heavily used for searches but that are not updated.

          – A “cold” key cache that takes up 20% of the space allocated for all key caches. Use this cache for medium-sized, intensively modified tables, such as temporary tables.

          – A “warm” key cache that takes up 60% of the key cache space. Employ this as the default key cache, to be used by default for all other tables.