Mysql Key_buffer_size parameter settings

In the mysql database, mysql key_buffer_size is the most influential parameter on the performance of the MyISAM table. The following describes how to set the mysql Key_buffer_size parameter for your reference.

The following configuration of a storage engine server with MyISAM as the main storage engine:
Mysql> show variables like 'key _ buffer_size ';
+ ----------------- + ------------ +
| Variable_name | Value |
+ ----------------- + ------------ +
| Key_buffer_size | 536870912 |
+ ----------------- + ------------ +
Allocated MB 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 |
+ ------------------------ + ------------- +
There are a total of 27813678764 index read requests, 6798830 of which are not found in the memory to directly read the index from the hard disk, calculate the probability that the index does not hit the cache:

Key_cache_miss_rate = Key_reads/Key_read_requests * 100%

For example, the key_cache_miss_rate of the above data is 0.0244%, and only one direct read hard disk is available for 4000 index read requests, which is already very BT, key_cache_miss_rate is good at lower than 0.1% (each 1000 requests have a direct read hard disk). If key_cache_miss_rate is lower than 0.01%, excessive key_buffer_size allocation can be minimized.
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), and Key_blocks_used indicates the maximum number of blocks used. For example, on this server, all the caches are used, or key_buffer_size is increased, either it is a transitional index, and the cache is full. Ideal settings:
Key_blocks_used/(Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

Two methods for implementing MySQL multi-table join query

How to deal with MySQL function creation Problems

How to modify MySQL character set in Liunx

Take you to understand mysql Variables

MySQL user permission setting method