Databases Reference
In-Depth Information
that you might not be able to make it run as fast as you can on similarly sized physical
hardware. For example, if you have a server with 8 CPU cores, 16 GB of memory, and
a midlevel RAID array, you might assume that you can get about the same performance
from an EC2 instance with 8 EC2 compute units, 15 GB of memory, and a handful of
EBS volumes. That's not guaranteed, however. The EC2 instance's performance is
likely to be more variable than that of your physical hardware, especially because it's
not one of the super-large instances and is therefore presumably sharing resources with
other instances on the same physical hardware.
Variability is a really big deal. MySQL, and InnoDB in particular, doesn't like variable
performance—especially not variable I/O performance. I/O operations can acquire
mutex locks inside the server, and when these last too long they can cause pileups that
manifest as many “stuck” processes, inexplicably long-running queries, and spikes in
status variables such as
Threads_running
or
Threads_connected
.
The practical result of inconsistent or unpredictable performance is that queueing be-
comes more severe. Queueing is a natural consequence of variability in response times
and inter-arrival times, and there is an entire branch of mathematics devoted to the
study of queueing. All computers are networks of queueing systems, and requests for
resources must wait if the desired resource (CPU, I/O, network, etc.) is busy. When
resource performance is more variable, requests overlap more often, and they experi-
ence more queueing. As a result, it's a bit harder to achieve high concurrency or con-
sistently low response times in most cloud computing platforms. We have a lot of
experience observing these limitations on the EC2 platform. In our experience, the most
concurrency you can expect from MySQL on the largest instance sizes is a
Threads
_running
count of 8 to 12 on typical web OLTP workloads. Anything beyond that, and
performance tends to become unacceptable, as a rule of thumb.
Note that we said “typical web OLTP workloads.” Not all workloads respond in the
same way to the limitations of cloud platforms. It turns out that there are actually some
workloads that perform just fine in the cloud, and some that suffer especially badly.
Let's take a look at what those are:
• Workloads that need high concurrency, as we just discussed, don't tend to be as
well suited to cloud computing. The same is true of applications that demand
extremely fast response times. The reason boils down to the limited number and
speed of the virtual CPUs. Every query runs on a single CPU inside MySQL, so
query response time is limited by the raw speed of the CPU. If you want fast re-
sponse times, you need fast CPUs. To support higher concurrency, you need more
of them. It's true that MySQL and InnoDB don't provide great bang for the buck
on many dozens of CPU cores, but they generally scale well out to at least 24 cores
these days, and that's more than you can usually get in the cloud.
• Workloads that require a lot of I/O don't tend to perform all that well in the cloud.
When I/O is slow and variable, things grind to a halt fairly quickly. On the other
hand, if your workload doesn't demand a lot of I/O, either in throughput
