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.
The script I’m running (MyISAM tables) is making about 2000 inserts per second.
(My server setup is Intel i7, 8GB RAM, CentOS 5.5, MySQL Server 5.0.)
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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.