In our first installment, we made a big deal about splitting off a database server from a web server for performance reasons. Our last post in the series talked about tuning Apache for PHP applications. Today, we're going to go over some basics for tuning MySQL

The Worst Default Configuration Ever

It's hard to believe, but MySQL ships with what is possibly the worst configuration ever. All of the features that can make MySQL actually perform well are either turned off by default or set to unreasonable values. This article will cover a few of the "big hitters" that you can change today and see immediate improvement in the performance of your MySQL server.

Enable the Cache!

The most important thing you can do if you are struggling with the performance of your MySQL database is to enable and tune the in-memory caches that are available. Depending on which storage engine you use, there are different caches to set up. The InnoDB Buffer Pool is completely irrelevant (and a waste of precious RAM) if you are only using MyISAM tables. Whether you use InnoDB or MyISAM (or even if you're not sure), you can definitely benefit from setting up the Query Cache.

Sizing the Query Cache

As with all applications, it is critical to ensure that MySQL does not overrun the physical memory and start swapping to disk. This has important implications for sizing the Query Cache. Ideally, you want the query cache to be large enough to hold all cacheable items without running out of RAM. To properly size the Query Cache takes some trial-and-error testing. If you can, start out with a very large Query Cache (1-2gb) and then monitor utilization over time. Several MySQL variables will tell you about the query cache activity as the server is running. In particular, the value of Qcache_lowmem_prunes should ideally be zero or very small. Looking at your Qcache_free_blocks and Qcache_free_memory will give you a good indication of how much "empty" RAM is being held open for more data. At some point, all of the data that is cacheable will be stored in the Query Cache, and you can hopefully downsize the cache to reclaim some of that unused memory for other things.

All variable readings should be taken after the MySQL has been actively running for a minimum of 48 hours. Preferably longer. The idea is that you want to have an accurate picture of typical Query Cache activity before making major adjustments.

Special InnoDB Considerations

In MySQL 5.5, the default storage engine was changed from MyISAM to InnoDB. More and more applications will be using this storage engine whether intentional or not. With this change comes some additional complexity for those of us working to get the most out of our MySQL servers.

One of the first things you should do if you are running MySQL 5.5+, or if think you may have InnoDB tables, is to enable the innodb_file_per_table option. This causes MySQL to put data and indexes for individual tables into separate files, instead of one single giant file. The single-file InnoDB setup is one of the worst parts of the default MySQL configuration. The single InnoDB file grows indefinitely, but can never shrink, even when tables or databases are dropped. This configuration also disables some of the maintenance functions available on InnoDB tables (table compression and fast truncation).

The second thing that is unique to the InnoDB storage engine is the InnoDB Buffer Pool. Similar to the Query Cache, the Buffer Pool stores data in RAM for much faster access. The operation and tuning of the InnoDB Buffer Pool is similar to tuning the Query Cache. In general, for applications making heavy use of InnoDB tables, the InnoDB Buffer Pool should be as large as possible without overrunning RAM. It is conceivable that the InnoDB Buffer Pool can be large enough to hold all of the data in the InnoDB tables, which is the best possible scenario. If all of the data can fit into the Buffer Pool, the only time MySQL will access the disk (slow) is to change data and refresh the Buffer Pool.

Cache & Pool for Maximum Performance

If you run a MySQL server, you owe it to yourself (and your customers) to learn about and implement Query Caching and the InnoDB Buffer Pool. These 2 steps can make a world of difference in the performance of any MySQL application. There are tons of other optimizations that can be applied to MySQL server, but these two are by far the most effective at improving performance.

Learn More

To learn more about the Query Cache and InnoDB Buffer Pool, follow these links to the official MySQL developer website:

MySQL Query Cache Reference

InnoDB Buffer Pool Reference