Databases Reference
In-Depth Information
application that uses MySQL. The CPU architecture (RISC, CISC, depth of pipeline,
etc.), CPU model, and operating system all affect MySQL's scaling pattern. This is why
benchmarking is so important: some systems might continue to perform very well un-
der increasing concurrency, while others perform much worse.
Some systems can even give lower total performance with more processors. This used
to be quite common; we know of many people who tried to upgrade to systems with
more CPUs, only to be forced to revert to the older systems (or bind the MySQL process
to only some of the cores) because of lower performance. In the MySQL 5.0 days, before
the advent of the Google patches and then Percona Server, the magic number was 4
cores, but these days we're seeing people running on servers with up to 80 “CPUs”
reported to the operating system. If you're planning a big upgrade, you'll have to con-
sider your hardware, server version, and workload.
Some MySQL scalability bottlenecks are in the server, whereas others are in the storage
engine layer. How the storage engine is designed is crucial, and you can sometimes
switch to a different storage engine and get more from multiple CPUs.
The processor speed wars we saw around the turn of the century have subsided to some
extent, and CPU vendors are now focusing more on multicore CPUs and variations
such as multithreading. The future of CPU design might well be hundreds of processor
cores; quad-core and hex-core CPUs are common today. Internal architectures vary so
widely across vendors that it's impossible to generalize about the interaction between
threads, CPUs, and cores. How the memory and bus are designed is also very important.
In the final analysis, whether it's better to have multiple cores or multiple physical CPUs
is also architecture-specific.
Two other complexities of modern CPUs deserve mention. Frequency scaling is the
first. This is a power management technique that changes the CPU clock speed dy-
namically, depending on the demand placed on the CPU. The problem is that it some-
times doesn't cope well with query traffic that's composed of bursts of short queries,
because the operating system might take a little while to decide that the CPUs should
be clocked back up. As a result, queries might run for a while at a lower speed, and
experience increased response time. Frequency scaling can make performance slow on
intermittent workloads, but perhaps more importantly, it can create inconsistent
performance.
The second is turbo boost technology, which is a paradigm shift in how we think about
CPUs. We are used to thinking that our four-core 2 GHz CPU has four equally powerful
cores, whether some of them are idle or not. A perfectly scalable system could therefore
be expected to get four times as much work done when it uses all four cores. But that's
not really true anymore, because when the system uses only one core, the processor
might run at a higher clock speed, such as 3 GHz. This throws a wrench into a lot of
capacity planning and scalability modeling, because the system doesn't behave linearly.
It also means that an “idle CPU” doesn't represent a wasted resource to the same extent;
if you have a server that just runs replication and you think it's single-threaded and
