Learning MySQL

Chapter 12. Configuring and Tuning the Server

The MySQL server has many features that can be configured to best fit the needs of your system hardware and application. The default settings are fine for most applications, but there are a few that you should be aware of. In this chapter, we look at how you can modify server configuration to suit your setup and for improved overall performance.

There are two types of MySQL program settings: options, which dictate what a program should do, and variables, which dictate the amount of resources that should be set aside for different tasks. Options and variables can be server-wide (global) or limited to a single client session. Some variables apply to the server, while others apply to individual processes, or threads, that handle queries. Resources are generally allocated to a thread only if it requires them. Options and variables can be specified in an options file, or from the command line when starting a program.

You don’t have to come up with optimal settings for each setting on your server. There are ready-to-use configuration files in the support-files directory under the MySQL directory. The my-medium.conf file includes recommended settings for most applications and server configurations; other distributed configuration file variants are tailored for “small,” “large,” and “huge” installations. You can use one of these files as a starting point for your customizations. On a Linux or Mac OS X system, you can copy the file you want to ...