High Performance MySQL, 2nd Edition

Chapter 6. Optimizing Server Settings

People often ask, “What’s the optimal configuration file for my server with 16 GB of RAM and 100 GB of data?” The truth is, there’s no such file. Servers need very different configurations depending on hardware, data size, the types of queries they will run, and the system’s requirements—response time, transactional durability and consistency, and so on.

The default configuration is designed not to use a lot of resources, because MySQL is intended to be very versatile, and it does not assume it is the only thing running on the server on which it is installed. By default, this configuration uses just enough resources to start MySQL and run simple queries with a little bit of data. You’ll certainly need to customize it if you have more than a few megabytes of data. You can start with one of the sample configuration files included with the MySQL server distribution and tweak it as needed.

You shouldn’t expect large performance gains from every configuration change. Depending on your workload, you can usually improve performance two- or threefold by choosing appropriate values for a handful of configuration settings (exactly which options make this difference depends on a variety of factors). After that, the improvements are incremental. You might notice a particular query that runs slowly and make it better by tweaking a setting or two, but you won’t usually make your server perform an order of magnitude better. To get that kind of benefit, you’ll ...