Re: Key_Buffer_Size

[Rick James]

Note: This thread applies only to MyISAM usage.

Depends on many things.
* A query that is "Using index", then the key_buffer is used, and the OS cache is not.
* A query that decides to do a table scan won't use the key_buffer, only the OS cache.
* An index-scan, where the index is bigger than key_buffer will probably blow out all other cached pages.
* You don't need a key_buffer bigger than the total of all the .MYI files.
* The key_buffer grows as needed, but stops at the setting of key_buffer_size. After a Slave has been shut down (for maintenance) and restarted, I can watch the memory usage climb. Sometimes, even with 150 queries/sec, it can take a day to grow back to key_buffer_size. Probably it is too big.

Suggested guideline:

1. Set key_buffer_size to 20-25% of _available_memory.

2. Watch hit/miss rate in key buffer.

2a. If miss rate more than a few percent, try increasing the cache.
2b. If miss rate < 1%, consider lowering key_buffer_size.

It's not a good idea to strive for lots of nines (99.999...%), you will be starving the _data_ block caching.