Databases Reference
In-Depth Information
The techniques shown in
Chapter 3
will help you find your system's limiting factor,
but look beyond the obvious when you think you've found a bottleneck. A weakness
in one area often puts pressure on another subsystem, which then appears to be the
problem. For example, if you don't have enough memory, MySQL might have to flush
caches to make room for data it needs—and then, an instant later, read back the data
it just flushed (this is true for both read and write operations). The memory scarcity
can thus appear to be a lack of I/O capacity. When you find a component that's limiting
the system, ask yourself, “Is the component itself the problem, or is the system placing
unreasonable demands on this component?” We explored this question in our diag-
nostics case study in
Chapter 3
.
Here's another example: a saturated memory bus can appear to be a CPU problem. In
fact, when we say that an application has a “CPU bottleneck” or is “CPU-bound,” what
we really mean is that there is a computational bottleneck. We delve into this issue next.
How to Select CPUs for MySQL
You should consider whether your workload is CPU-bound when upgrading current
hardware or purchasing new hardware.
You can identify a CPU-bound workload by checking the CPU utilization, but instead
of looking only at how heavily your CPUs are loaded overall, look at the balance of
CPU usage and I/O for your most important queries, and notice whether the CPUs are
loaded evenly. You can use the tools discussed later in this chapter to figure out what
limits your server's performance.
Which Is Better: Fast CPUs or Many CPUs?
When you have a CPU-bound workload, MySQL generally benefits most from
faster
CPUs (as opposed to more CPUs).
This isn't always true, because it depends on the workload and the number of CPUs.
Older versions of MySQL had scaling issues with multiple CPUs, and even new versions
cannot run a single query in parallel across many CPUs. As a result, the CPU speed
limits the response time for each individual CPU-bound query.
When we discuss CPUs, we're a bit casual with the terminology, to help keep the text
easy to read. Modern commodity servers usually have multiple sockets, each with sev-
eral CPU cores (which have independent execution units), and each core might have
multiple “hardware threads.” These complex architectures require a bit of patience to
understand, and we won't always draw clear distinctions among them. In general,
though, when we talk about CPU speed we're really talking about the speed of the
execution unit, and when we mention the number of CPUs we're referring to the num-
ber that the operating system sees, even though that might be a multiple of the number
of independent execution units.
