Making MySQL Use More Memory: Part 2

In a previous article, I discussed how to get the most out of your memory usage in MySQL systems using the InnoDB storage engine. To review, MySQL has overly conservative memory usage by default. Your MySQL instance may be using only a small fraction of the available memory, leading to unnecessarily poor application performance. In this article, I’ll discuss how to accomplish the same feat in a MyISAM storage engine.

One of the main variables for increasing the memory usage in MyISAM is the “key_buffer_size”, which is the key cache for MyISAM systems. Set “key_buffer_size” to be up to 25% of RAM, or at least a few hundred megabytes. For example, if you have 2GB of RAM, you may want to set it as follows:

key_buffer_size=500M

You can set this value up to 50% of available memory, although be careful to keep (key_buffer_size + innodb_buffer_pool_size) < 80% of available memory. Assuming your system is primarily an InnoDB xor a MyISAM system, only one of these values should be set large. Next, increase the size of the MyISAM sort buffer size. I don't really have a recommended size here, but a few hundred megabytes should suffice such as: myisam_sort_buffer_size=256M Some other less used parameters with suggested values: read_buffer_size=4M sort_buffer_size=4M myisam_max_sort_file_size=20G Note that the last parameter increases the temporary file space, not memory, but may lead to improved performance.

2 thoughts on “Making MySQL Use More Memory: Part 2

  1. Great tips, bear in mind that if you’re putting in bit values you need to do the following:

    1024*1024 = 1048576 = 1M

    and

    1024*1024*1024 = 1073741824 = 1G

Leave a Reply

Your email address will not be published. Required fields are marked *