Increase your buffers

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:

set-variable = key_buffer_size=128M
set-variable = sort_buffer_size=8M

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:

SET <option> = <val>

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

SET GLOBAL key_buffer_size = 128M

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.


Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Keep tabs on your server >>

Previous chapter: Upgrade MySQL

Jump to:


Home: Table of Contents

Copyright ©2015 Paul Hudson. Follow me: @twostraws.