Databases Reference
In-Depth Information
CHAPTER 8
Optimizing Server Settings
In this chapter, we'll explain a process by which you can create a good configuration
file for your MySQL server. It is a roundabout trip, with many points of interest and
side trips to scenic overlooks. These are necessary, because determining the shortest
path to a good configuration doesn't start with studying configuration options and
asking which ones you should set or how you should change them, nor does it start
with examining server behavior and asking whether any configuration options can im-
prove it. It's best to begin with an understanding of MySQL's internals and behavior.
You can then use that knowledge as a guide for how MySQL should be configured.
Finally, you can compare the desired configuration to the current configuration and
correct any differences that are important and worthwhile.
People often ask, “What's the optimal configuration file for my server with 32 GB of
RAM and 12 CPU cores?” Unfortunately, it's not that simple. The server should be
configured for the workload, data, and application requirements, not just the hardware.
MySQL has scores of settings that you can change—but you shouldn't. It's usually
better to configure the basic settings correctly (and there are only a few that really matter
in most cases) and spend more time on schema optimization, indexes, and query design.
After you've set MySQL's basic configuration options correctly, the potential gains
from further changes are usually small.
On the other hand, the potential downside of fiddling with the configuration can be
great. We've seen more than one “highly tuned” server that was crashing constantly,
stalling, or performing slowly due to unwise settings. We'll spend a bit of time on why
that can happen and what not to do.
So what
should
you do? Make sure the basics such as the InnoDB buffer pool and log
file size are appropriate, set a few safety and sanity options if you wish to prevent bad
behavior (but note that these usually won't improve performance—they'll only avoid
problems), and then leave the rest of the settings alone. If you begin to experience a
problem, diagnose it carefully with the techniques shown in
Chapter 3
. If the problem
is caused by a part of the server whose behavior can be corrected with a configuration
option, then you might need to change it.
