- Key Buffer Size May Not Be Optimal For Key Cache
To minimize disk I/O, the MyISAM storage engine uses a cache mechanism to keep the most frequently accessed index blocks in memory. Otherwise, the storage engine will perform physical reads of a key block from disk, which is slower than reading from memory. If you find that the server is accessing physical memory too much, you could possible increase the key cache size, the buffer. The key_buffer_size variable represents the size of this buffer.
On a dedicated server that runs the MySQL server, the key_buffer_size should usually be about 25% of total RAM. Depending on the the operating system, you may be able to increase it beyond this value, but anything above 50% of RAM is liable to be counter-productive. The ratio of Key_reads to Key_read_requests represents the proportion of keys that are being read from disk instead of the key cache. This should normally be less than 0.1 for optimum efficiency. Similar to reads, the ratio of Key_writes to Key_write_requests represents the proportion of keys that are being written to disk. This should be as close to 1 as possible to ensure the data on the disk is up-to-date.
There are a few steps to take to improve your key cache: Update your MySQL configuration file to include the new setting. For example, if you have 2GB of total RAM, set it to 500MB like so:
After making the changes to the configuration file, restart your MySQL server.
MyISAM is the only storage engine that uses the key buffer. When using other 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. Nevertheless, the key_buffer_size can not be bigger than 4GB.