GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

key_buffer_size tuning

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • key_buffer_size tuning

    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.

  • #2
    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.

    Comment


    • #3
      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.

      Comment


      • #4
        Is seeing slower performance with a higher key_buffer_size reasonable. 128MB > 256 > 384 > 512 > 1024 in all my tests on a box with 2 gigs of ram. I've tried benchmarks where the entire data set couldn't fit into ram and data sets where the total size of the indexes would only fit into ram at 1024 MB. I've tried cold key caches and hot key caches and 128MB wins in everything I've tried.

        I've read/heard anecdotal statements that say mysql does a very poor job of handling concurreny with managing the key buffer. That would explain the 10-20% drop in performance I see as I try to raise the key buffer size to 25% of the ram on my db server.

        Comment


        • #5
          That wouldn't surprise me. MyISAM certainly wasn't designed with concurrency in mind.

          You might have better luck with InnoDB, but it has some (different) issues with concurency, too.

          Comment

          Working...
          X