Go MyISAM Key Cache Detailed and optimized

Original: http://huanghualiang.blog.51cto.com/6782683/1372721

One, MyISAM Key cache Detailed:

In order to minimize disk I/o,myisam the most frequently accessed index block ("Indexblock") is placed in memory, such that the memory buffer we call the key Cache, its size can be controlled by parameter key_buffer_size. In the MyISAM index file (MYI), the contiguous Unit (contiguous unit) consists of a block,index block that is equal to the size of the Btree index node. The Key cache is a block-based unit.

1. MyISAM How to use key Cache

When MySQL requests (reads or writes) MyISAM an indexblock in the index file, it first looks at whether the corresponding block has been cached in the key cache queue. If there is, read and write directly in the key cache queue, no longer need to request the disk. In the case of a write request, the corresponding block in the key cache is marked as dirty (inconsistent with the disk). After the MyISAM has successfully requested (read and write) a block at key cache, the block is placed in the head of the key cache queue.

If there is no pending request (read or write) in key cache, Block,myisam will request the corresponding block to the disk and place it in the Keycache queue header. If the queue is full, the block at the end of the queue will be deleted, and if the block is dirty, it will flush it to disk. We see that MyISAM maintains a key cache queue for LRU (Least recently used). The dirty block in the queue is flush to disk when the block is kicked out of the queue.

2. Concurrent Access

The index block in Key cache can be accessed concurrently (Shared access), and here are some rules:

A. Multiple sessions with no update operation can be concurrent with the same block buffer

B. Multiple sessions access a block buffer at the same time, if a session is an update operation, the first access

C. Multiple sessions if the block replacement is required, it is possible to operate concurrently. (Read block update from index file to key cache, but key cache is full, need to delete some block buffer operation called Block replacement)

Set multiple key buffer

Set global hot.key_buffer_size=xx; #hot, Cold is the name of key buffer and can be taken at random

Set global colkd.key_buffer_size=xx;

CACHE INDEX Example.top_message in Hot_cache

CACHE INDEX example.event in Cold_cache

LOAD INDEX into cacheexample.top_message,example.event IGNORE LEAVES; #预加载, it can be placed in the configuration file, the database key buffer hit rate will be increased after startup.

LOAD INDEX into CACHE example.user ignoreleavers,expamle.groups

Second, MySQL management key buffer algorithm:

MySQL defaults to the LRU algorithm (due to the default key_cache_division_limit= 100, there is only one chain and the midpoint insertion algorithm is not used.)

The LRU algorithm of Key cache, as an improvement to the LRU algorithm, MyISAM also provides another caching algorithm: "midpointinsertion strategy".

Midpoint insertion strategy (midpoint insertion algorithm)-divides the LRU chain into a hot sub-table warm child table.

1. Related Parameters

The parameters involved in this strategy are:key_cache_division_limit, Key_cache_age_threshold

KEY_CACHE_DIVISION_LIMIT=70--%30 's cache makes hot

The block at the top of the key_cache_age_threshold:hotsub-chain is downgraded to warm sub-chain after exceeding a threshold. The specific calculation method is: Set N as the number of blocks in the key cache, if in the most recent (n*key_cache_age_threshold/100) access, The block at the top of the Keycache is still not accessible, so it will be moved to the top of the Warmsub-chain.

2. Introduction of the principle

(1). This strategy divides the previous LRU queue (LRU Chain) into two parts, hot sub-chain and warm sub-chain. And according to the parameter Key_cache_division_limit division, always keep warm sub-chain above this percentage. By default, Key_cache_division_limit is 100, so the default is only Warmsub-chain, which is the LRU chain.

(Note: Multiple key cache case, each Key cache has a corresponding Key_cache_division_limit value)

(2). A block in warm sub-chain if the number of accesses (access) exceeds a certain value, place the block at the bottom of the hot sub-chain.

(3). Blocks in hot sub-chain will adjust position with each hit, the more hits, the closer the bottom. Staying at the top for too long will be downgraded to warm sub-chain, and it is the top of the warm sub-chain (which is likely to be moved out of key cache soon).

(4). The top block dwell time in the hot sub-chain is downgraded to warm sub-chain after exceeding a threshold. This threshold is determined by the parameter key_cache_age_threshold. The specific calculation method is: Set N is the number of blocks in the key cache, if in the most recent (n*key_cache_age_threshold/100) access, Keycache the top of the block is still not accessed, then will be moved to warm The top of the Sub-chain.

(5). Key_cache_division_limit = 100 By default, this policy is not used when there is only one chain. That is, the degraded midpoint insertion strategy is the LRU algorithm.

*key Buffer some parameter indicators:

physical reads : Key_read/key_read_requests generally should be less than 0.01, otherwise the key buffer setting is small.

Key_writes/key_write_requests #看写多不多, if a lot of close to 1

usage:N ((key_block_unused*key_cache_block_size)/key_buffer_size) # generally in the 80% around better, more than 80%, May be keybuffer set too small, less than 80% may be the key buffer is set too large, resulting in memory resources wasted.

Go MyISAM Key Cache Detailed and optimized