MySQL performance tips

MySQL has a default configuration too small for most uses. Change the configuration. Here is the quick and simple way.

1. my.cnf
2. Drupal
3. Linux

my.cnf

MySQL is configured by a file named my.cnf. The file might be named my.ini in some operating systems. MySQL offers sample alternative configurations named my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf. The choices during the installation on Windows are Development use, Server, and Dedicated server. The choices appear to match small, medium, and large. Choose the next larger size for better performance.

my-small.cnf allocates a total of 64 MB to MySQL and has a key_buffer_size of 16 KB. This is unusable for everything bigger than a brochure site. You might use the small setting in a VPS with only 256 MegaBytes of memory. 256 MB is too small for any Web site where people log in or shop or perform interactive activity using the database. That key_buffer_size of 16 KB will flood when the first shopper starts browsing products.

my-medium.cnf allocates 124 MB to MySQL and has a key_buffer_size of 16 MB. Yes MB, not KB. Medium is the minimum for a modern computer and a VPS with at least 512 MB of memory. A hosting company recently swapped one VPS of my VPSs from 384 MB to 1 GB because memory is so cheap. Small might be all you can do on 384 MB. Medium works easily on 1 GB.

my-large.cnf allocates 512 MB and has the key buffer set to 512 MB. My largest MySQL web site might have 512 MB of keys in total and many are not accessed during regular use. Large would be overkill for 80% of my sites. Large would require a VPS of at least 2 GB.

my-huge.cnf is for 1 - 2 GB. The various internal allocations do not add up to enough to use 2 GB and look like they would use 1 GB. My development machine has 6 GB spare during typical use, making huge a valid choice for my Web development. You want a VPS about four times as big as the MySQL allocation, suggesting a VPS of at least 4 GB and up to 8 GB.

key_buffer_size

The key_buffer_size is critical and the tiny allocation in the default small configuration is unusable. If you do not have enough memory to jump up to the medium configuration then change key_buffer_size. Make key_buffer_size ten times bigger and test the results.

sort_buffer_size

The next item of interest is sort_buffer_size. The huge configuration allocates only 2 MB for the sort buffer. You need more than 2 MB. MySQL will try to extract data in the right sort order by using indexes. If MySQL has to perform additional sorting, the sort buffer is flooded. Think of your top ten style reports. MySQL has to first sort data into a sequence then select the largest entries then, perhaps, sort the results into another order for presentation. Looking at one of my medium size sites, the sort buffer should be expanded to at least 10 MB.

thread_concurrency

Set thread_concurrency to the number of processors multiplied by two. My two core development machine could be set to 4. The common VPS gets to use only one processor most of the time so leave thread_concurrency at the default by leaving thread_concurrency out of the configuration. Ask your Web hosting company for their recommendations for your hosting account.

Drupal

See Drupal performance tips for the inside secrets to quickly and easily boosting Drupal performance. The tips include the changes to MySQL recommended on this page.

max_allowed_packet

One thing you often have to change for Drupal is the max_allowed_packet = 1M setting in the configuration file. Change it to at least 4M. Drupal can send very long blocks to MySQL and one MegaByte is often to small.

Linux

My smallest computer is my netbook with only 1 GB of memory. I run Ubuntu Linux, Apache, MySQL, and PHP on the the netbook for Web development, making the machine an UbAMP stack. (As in LAMP, WAMP, and XAMP.) According to Webmin (the easiest way to manage a Web machine), Ubuntu installed MySQL using my-medium.cnf instead of my-small.cnf. Good. Saves me the task of changing it.

Your Linux might not use the same setting. Look in /etc/mysql/my.cnf.