MySQL optimization key_buffer_size

KEY_BUFFER_SIZE Specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. By checking the status values key_read_requests and Key_reads, you can know if the key_buffer_size setting is reasonable

Key_buffer_size has a great impact on MyISAM table performance.

1. Enquiry

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 is below 0.01%, the key_buffer_size is allocated too much and 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 |

+------------------------+-------------+

Ey_blocks_unused represents the number of unused cache clusters (blocks), key_blocks_used indicates the maximum number of blocks ever used, such as this server, all caches are used, or key_buffer_size is added, It's either a transition index or a full cache. More Ideal settings:

Key_blocks_used/(key_blocks_unused + key_blocks_used) * 100%≈80%

Mysql> show global status like ' key_writ% ';

+--------------------+-------+

| variable_name | Value |

+--------------------+-------+

| key_write_requests | 10 |

| Key_writes | 1 |

+--------------------+-------+

2 rows in Set (0.01 sec)

MySQL optimization key_buffer_size