Settings for MySQL key_buffer_size parameters

In MySQL database, MySQL key_buffer_size is the most important to the performance of the MyISAM table, the following will be the MySQL key_buffer_size parameter settings in detail, for your reference.

The following is a configuration with MyISAM as the primary storage Engine server:
Mysql> Show variables like ' key_buffer_size ';
+-----------------+------------+
| variable_name | Value |
+-----------------+------------+
| Key_buffer_size | 536870912 |
+-----------------+------------+
Allocating 512MB of memory to MySQL Key_buffer_size, let's look at the usage of key_buffer_size:
Mysql> show global status like ' key_read% ';
+------------------------+-------------+
| variable_name | Value |
+------------------------+-------------+
| key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+------------------------+-------------+
A total of 27,813,678,764 index read requests, with 6,798,830 requests not found in memory directly from the hard disk to read the index, calculate the probability of index misses cache:

Key_cache_miss_rate = key_reads/key_read_requests * 100%

For example, the above data, Key_ Cache_miss_rate is 0.0244%, 4,000 index read requests have a direct read hard disk, already very bt, key_cache_miss_rate under 0.1% is very good (every 1000 requests have a direct read hard disk), if Key_ Cache_miss_rate under 0.01%, the key_buffer_size allocation of too much, can be appropriately reduced. The
MySQL server also provides the key_blocks_* parameter:
mysql> show global status like ' key_blocks_u% ';
+------------------------+ -------------+
| variable_name | Value |
+------------------------+-------------+
| key_blocks_unused | 0 |
| key_blocks_used | 413543 |
+------------------------+-------------+
Key_blocks_unused indicates the number of unused cache clusters (blocks), Key_blocks_ Used represents the largest number of blocks ever used, such as this server, where all caches are used, either by adding key_buffer_size, or by a transition index, which fills up the cache. Preferred settings:
key_blocks_used/(key_blocks_unused + key_blocks_used) * 100%≈80%