Configuring and Tuning the Server - Learning MySQL - page 379

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 applica-
tions, 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 di-
rectory. 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 a standard location so that it will be read by the server; on a Windows
system, you will have to explicitly tell the server to read in the file. Let's look at the
server options in more detail.
The MySQL Server Daemon
The main MySQL server program, or MySQL server daemon , is called mysqld . Under
Windows, there are two main programs you can use: mysqld-nt.exe —which is opti-
mized for Windows XP, 2000, and NT (and probably soon, for Vista)—and
mysqld.exe , which can work on older versions of Windows.
On Linux or Mac OS X, the recommended way to start the mysqld program is by calling
the mysqld_safe script. This in turn starts mysqld or, if it's available, mysqld-max —a