key_buffer_size tuning

[mkruse]

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.

[vgatto]

I’m not sure about exactly what algorithm MySQL uses to manage the key buffer, but there are two things you should consider. First, the fan-out of a B-Tree index can be fairly large. If you’ve got a 1K index page and a 16-byte index record, you’ve got ~64 child pages per parent. So there can be a lot more leaf pages than non-leaf pages. The second is that if you are range scanning an index, you will travel to the leaf page which matches the beginning of your range and then traverse the leaf pages sequentially in a linked-list fashion. This can obviously lead to more leaf pages being in the key buffer.

Regarding the OS file system cache, this is totally normal. Since the key buffer only caches index pages and not data pages, MyISAM relies on the OS file system cache to cache the data. Depending on your queries, the number of data pages required can be considerably larger than the number of index pages needed. This can explain why you’re better off leaving most of your memory to the OS.

As far as reading goes, here are two books that I highly recommend:

http://www.amazon.com/High-Performance-MySQL-Optimization-Re plication/dp/0596101716/ref=pd_bbs_sr_1?ie=UTF8&s=books& amp;qid=1237608141&sr=8-1

http://www.amazon.com/MySQL-Experts-Voice-Open-Source/dp/159 059505X/ref=sr_1_1?ie=UTF8&s=books&qid=1237608196&am p;sr=1-1

Hope that helps.

[MarkRose]

Don’t forget the OS will also cache the index files, so having too small of key_buffer isn’t really a huge performance concern (the penalty comes from copying in the pages from the OS’s file system buffer, and RAM is pretty quick). If you’re getting a 96% percent hit rate, that’s quite reasonable.

This is a case where throwing more memory system is probably the easiest way to boost performance.