I’ve been trying to benchmark key_buffer_size. Mysql buffers key_blocks uses a LRU algorithm to determine what to buffer. This would lend to the idea that the portions at the top of the B-Tree are going to remain in the cache, while the leaves of the B-Tree more then likely won’t be buffered if our total index size > key_buffer size. Is my understanding here correct?
Benchmarking has also shown that os file system cache is better then larger values of key_buffer_size. I’ll have 96% key cache hit rates with 128MB key buffer size beat out 99.9999% key cache hit rates at 1024MB key_buffer_size. MySQL’s internal structure that manages the key_cache seems to be be out performed by the operating system’s file cache.
Anyone have additional experience seeing this? Is there better documentation I should be reading, the stuff on mysql’s site is pretty sparse other then to say allocate 25% of the ram on your dedicated db server.