Databases Reference
In-Depth Information
Modern CPUs are much improved over those available a few years ago. For example,
today's Intel CPUs are much faster than previous generations, due to advances such as
directly attached memory and improved interconnects to devices such as PCIe cards.
This is especially good for very fast storage devices, such as Fusion-io and Virident PCIe
flash drives.
Hyperthreading also works much better now than it used to, and operating systems
know how to use hyperthreading quite well these days. It used to be that operating
systems weren't aware when two virtual CPUs really resided on the same die and would
schedule tasks on two virtual processors on the same physical execution unit, believing
them to be independent. Of course, a single execution unit can't really run two pro-
cesses at the same time, so they'd conflict and fight over resources. Meanwhile, the
operating system would leave other CPUs idle, thus wasting power. The operating
system needs to be hyperthreading-aware because it has to know when the execution
unit is actually idle, and switch tasks accordingly. A common cause of such problems
used to be waits on the memory bus, which can take up to a hundred CPU cycles and
is analogous to an I/O wait at a very small scale. That's all much improved in newer
operating systems. Hyperthreading now works fine; we used to advise people to disable
it sometimes, but we don't do that anymore.
All this is to say that you can get lots of fast CPUs now—many more than you could
when we published the second edition of this topic. So which is best, many or fast?
Usually, you want both. Broadly speaking, you might have two goals for your server:
Low latency (fast response time)
To achieve this you need fast CPUs, because each query will use only a single CPU.
High throughput
If you can run many queries at the same time, you might benefit from multiple
CPUs to service the queries. However, whether this works in practice depends on
your situation. Because MySQL doesn't scale perfectly on multiple CPUs, there is
a limit to how many CPUs you can use anyway. In older versions of the server (up
to late releases of MySQL 5.1, give or take) that was a serious limitation. In newer
versions, you can confidently scale to 16 or 24 CPUs and perhaps beyond, de-
pending on which version you're using (Percona Server tends to have a slight edge
here).
If you have multiple CPUs and you're not running queries concurrently, MySQL can
still use the extra CPUs for background tasks such as purging InnoDB buffers, network
operations, and so on. However, these jobs are usually minor compared to executing
queries.
MySQL replication (discussed in the next chapter) also works best with fast CPUs, not
many CPUs. If your workload is CPU-bound, a parallel workload on the master can
easily serialize into a workload the replica can't keep up with, even if the replica is more
powerful than the master. That said, the I/O subsystem, not the CPU, is usually the
bottleneck on a replica.
