Databases Reference
In-Depth Information
(operations per second) or bandwidth (bytes per second), MySQL can hum along
quite nicely.
The preceding points really follow from the weaknesses of CPU and I/O resources in
the cloud. What can you do about them? There's not much you can do about CPU
limitations. If you don't have enough, you don't have enough. However, I/O is differ-
ent. I/O is really the interchange between two kinds of memory: volatile memory (RAM)
and persistent memory (disk, EBS, or what have you). As a result, MySQL's I/O de-
mands can be influenced by how much memory the system has. With enough memory,
reads can be served from caches, reducing the I/O needed for both reads and writes.
Writes can generally be buffered in memory too, and multiple writes to the same bits
of memory can be combined and then persisted with a single I/O operation.
That's where the limitations on memory come into the picture. With enough memory
to hold the working set of data,
9
I/O demands can be reduced significantly for certain
workloads. Larger EC2 instance sizes also offer better network performance, which
further helps I/O to EBS volumes. But if your working set is too big to fit into the largest
instances available, I/O demands escalate and things start to block and stall, as dis-
cussed earlier. The largest high-memory instance sizes in EC2 have enough memory to
serve many workloads quite nicely. However, you should be aware that warmup time
can be very long; more on that topic later in this section.
What types of workloads can't be fixed by adding more memory? Regardless of buffer-
ing, some write-heavy workloads simply require more I/O than you can expect from
many cloud computing platforms. If you're executing many transactions per second,
for example, that will demand a lot of I/O operations per second to ensure durability,
and you can only get so much throughput from systems such as EBS. Likewise, if you're
pushing a lot of data into the database, you might exceed the available bandwidth.
You might think that you can improve your I/O performance by striping and mirroring
EBS volumes with RAID. That does help, up to a point. The problem is that as you add
more EBS volumes, you actually increase the likelihood that one of them is going to be
performing badly at any given point in time, and due to the way I/O works inside of
InnoDB, the weakest link is often the bottleneck for the whole system. In practice, we've
tried RAID 10 sets of 10 and 20 EBS volumes, and the 20-volume RAID had more
problems with stalls than the 10-volume one did. When we measured the I/O perfor-
mance of the underlying block devices, it was clear that only one or two of the EBS
volumes was performing slowly, and yet the whole server suffered.
You can change the application and server to reduce the I/O demands, too. Careful
logical and physical database design (schema and indexing) goes a long way toward
reducing I/O needs, as does application and query optimization. These are the most
powerful levers you can apply to reducing I/O. Some workloads, such as insert-heavy
workloads, can be helped by judicious use of partitioning to concentrate the I/O on a
9. See
Chapter 9
for a definition of the working set and a discussion of how it influences I/O demands.
