18.2.20     Increase your buffers

This is NOT the latest copy of this book; click here for the latest version.

MySQL has a variety of buffers that hold information about tables and indexes, and by increasing the size of these buffers you can often gain substantial boosts. Naturally the biggest boosts occur when changing from very small amounts, but if you find that one buffer has used all its memory, the chances are you will get at least some performance boost by giving it some more. In the majority of cases, increasing the key buffer size yields the greatest gain - many people use a key buffer size of a quarter of their total RAM, with a few even setting it up to 50% of their RAM. 50% is the absolute limit, however, as beyond that it becomes possible (and indeed likely) that your OS will try to page some of the buffer to hard disk, which defeats the purpose and will slow things down massively. Be sure to also consider increasing the size of your query cache, particularly if your data does not change all that often.

To increase the size of your buffers, you need to edit your my.cnf file. Under the [mysqld] section, you should see a list of set-variable entries that define the basic configuration of MySQL. Here you can add or change entries for your various buffer sizes, for example:

Once you have made changes to your my.cnf file, you will need to restart the MySQL server. You can achieve the same effect without the requirement for a reboot by using the MySQL monitor like this:

Many options are global, that is they effect everyone as opposed to just you, so you need to use SET GLOBAL, like this:

Furthermore, global variables need to be set by an administrator, which is usually your MySQL root account. Key areas to increase are your key buffer, sort buffer, and query cache. Give both your key cache and your query cache as much space as they need as they are both big space eaters.