Properties:
| Engine(s) | MyISAM |
| Server Startup Option | --key_buffer_size=<value> |
| Scope | Global |
| Dynamic | Yes |
| Possible Values | Integer Range: 8 - 4294967295 (4 GB) |
| Default Value | 131072 (128 KB) |
| Category | Performance |
Description:
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.
Calculations:
- 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
Read More:
MySQL manual entry on key_buffer_size
What to tune in MySQL server after installation
MyISAM Key Cache - MySQL Manual
Hope this post was helpful to you.
3 comments:
Hi there,
Looking at the MySQL doc: "The Key_reads/Key_read_requests ratio should normally be less than 0.01."
Applied to your key_cache_hit_ratio calculation, it seems that MySQL recommend having a key_cache_hit_ratio > 99%.
Who's not right?
Cheers,
Kévin
@Kévin
It's nice if one can have >99% (which is also >95% :) ) ratio and in most cases with stale data its possible. But with too much of dynamic data, which is also true in most cases including mine, achieving something like >95% can be challenging. Also, mixed storage engine schemas (leading to memory being divided between engines) make it more difficult to achieve such rates. Though mixing storage engines is normally not recommended, sometimes its forced.
Thanks for your quick answer :)
I've checked I have a ratio of 97%, that's cool then!
Great articles by the way
Kevin
Post a Comment