|Server Startup Option||--key_buffer_size=<value>|
|Possible Values||Integer |
Range: 8 - 4294967295 (4 GB)
|Default Value||131072 (128 KB)|
This is a global buffer where MySQL caches frequently used blocks of index data for MyISAM data. Maximum allowed size is 4GB on a 32 bit platform. Greater values are permitted for 64-bit platforms beyond MySQL 5.0.52.
Keeping this buffer to an optimal value (neither too low nor too high) contributes heavily to the performance of your MySQL server. As given in the MySQL Documentation, "using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common". As suggested by Peter, "Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload".
Starting with 25%, one can keep monitoring key cache hit ratio and increase it over time. A key cache hit ratio of more than 95% should be good enough. Also, for preventing oneself from allocating too much memory to this buffer, monitoring key buffer usage should be helpful. I feel one should target towards more than 80% of buffer usage.
If you are not using very few MyISAM tables, still key_buffer can be useful for MySQL's tables and temporary tables as well.
- key_cache_hit_ratio = (1 - Key_reads/Key_read_requests) * 100
- key_buffer_usage = (1 - (Key_blocks_unused * key_cache_block_size)/key_buffer_size) * 100
Hope this post was helpful to you.