MySQL: What is the key buffer

Question

Hello... I'm trying to tweak my MySQL server to fit my needs... and I have a basic question: What is the key buffer?

Through try and error I've found that a bigger key buffer makes my inserts faster... but I don't quite understand what it is. So... before I make something I might regret, I'd like to know what it is, and how it works.

Just for background information: I am running MySQL Server 5.0 on an Intel i7, 8GB RAM, and I am using CentOS 5.5. I am using MyISAM tables. The script I'm running is making about 2000 inserts per second.

Thank you for your answers.

Answer 1

What is the Key Buffer?

The key buffer is MyISAM specific, a structure for index blocks that contains a number of block buffers where the most-used index blocks are placed. It's mean for minimizing disk I/O, because memory is still faster than hard drives [currently]. The MyISAM key buffer is described in more detail in the documentation.

Guidelines for Tuning the Key Buffer

Size depends on amount of indexes, data size and workload.

• Set up to 30-40% of available memory if you use MyISAM tables exclusively. 2-4 MB minimum; dedicating GBs can be a waste.

For more info, see this article on MySQL tuning.