- Key Buffer Size May Not Be Optimal For System RAM
If the MySQL server has a key_buffer_size of 20% of RAM or less, it is probably not be optimal. The key_buffer_size variable represents the size of the buffer used for index blocks. Usually this should be set to 25% of total RAM on dedicated servers. To minimize disk I/O, the MyISAM storage engine uses a cache mechanism to keep the most frequently accessed index blocks in memory. The key_buffer_size variable represents the size of this buffer. Depending on the the operating system, it may be possible to set it higher than 25%. However, anything above 50% of RAM is liable to be counter productive.
To modify your system for this situation described above, update the MySQL otions file (i.e., my.cnf or my.ini, depending on your system) to include the new setting. For example, if the server has 2GB of total RAM, you might set it to 500MB like so:
[mysqld] key_buffer_size=500M ...After making the changes to the configuration file, restart the MySQL server.
MyISAM is the only storage engine that uses the key buffer. When using additional storage engines, you'll need to consider that these will also consume memory. On 32-bit systems, each process cannot use more than approximately 2.5GB of memory, no matter how much total RAM you have. So setting this higher than 1GB might not be possible. To solve this, you'll need to use a 64-bit system and operating system. The key_buffer_size can not be bigger than 4GB.