What Limits MySQL’s Performance?

Many different hardware components can affect MySQL’s performance, but the two most frequent bottlenecks we see are CPU and I/O saturation. CPU saturation happens when MySQL works with data that either fits in memory or can be read from disk as fast as needed. A lot of datasets fit completely in memory with the large amounts of RAM available these days.

I/O saturation, on the other hand, generally happens when you need to work with much more data than you can fit in memory.

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.

Broadly speaking, you likely 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.

If your workload doesn’t utilize all of your CPUs, 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.

Balancing Memory and Disk Resources

The biggest reason to have a lot of memory isn’t so you can hold a lot of data in memory: it’s ultimately so you can avoid disk I/O, which is orders of magnitude slower than accessing data in memory. The trick is to balance the memory and disk size, speed, cost, and other qualities so you get good performance for your workload.

Solid-State Storage

Solid-state (flash) storage is the standard for most database systems, especially OLTP. Only on very large data warehouses or very legacy systems would you typically find HDDs. This shift came as the price of SSDs dropped significantly around 2015.

Solid-state storage devices use nonvolatile flash memory chips composed of cells, instead of magnetic platters. They’re also called NVRAM, or nonvolatile random access memory. They have no moving parts, which makes them behave very differently from hard drives.

Here’s a quick summary of flash performance. High-quality flash devices have:

• Much better random read and write performance compared to hard drives. Flash devices are usually slightly better at reads than writes.
• Better sequential read and write performance than hard drives. However, it’s not as dramatic an improvement as that of random I/O, because hard drives are much slower at random I/O than they are at sequential I/O.
• Much better support for concurrency than hard drives. Flash devices can support many more concurrent operations, and in fact, they don’t really achieve their top throughput until you have lots of concurrency.

The most important things are improvements in random I/O and concurrency. Flash memory gives you very good random I/O performance at high concurrency.

RAID Performance Optimization

Storage engines often keep their data and/or indexes in single large files, which means RAID (Redundant Array of Inexpensive Disks) is usually the most feasible option for storing a lot of data. RAID can help with redundancy, storage size, caching, and speed. But as with the other optimizations we’ve been looking at, there are many variations on RAID configurations, and it’s important to choose one that’s appropriate for your needs.

We won’t cover every RAID level here, or go into the specifics of exactly how the different RAID levels store data. Instead, we focus on how RAID configurations satisfy a database server’s needs. The most important RAID levels are:

RAID 0

RAID 0 is the cheapest and highest-performance RAID configuration, at least when you measure cost and performance simplistically (if you include data recovery, for example, it starts to look more expensive). Because it offers no redundancy, we recommend RAID 0 only for servers you don’t care about, such as replicas or servers that are “disposable” for some reason.

Again, note that RAID 0 does not provide any redundancy, even though “redundant” is the R in the RAID acronym. In fact, the probability of a RAID 0 array failing is actually higher than the probability of any single disk failing, not lower!

RAID 1

RAID 1 offers good read performance for many scenarios, and it duplicates your data across disks, so there’s good redundancy. RAID 1 is a little bit faster than RAID 0 for reads. It’s good for servers that handle logging and similar workloads because sequential writes rarely need many underlying disks to perform well (as opposed to random writes, which can benefit from parallelization). It is also a typical choice for low-end servers that need redundancy but have only two hard drives.

RAID 0 and RAID 1 are very simple, and they can often be implemented well in software. Most operating systems will let you create software RAID 0 and RAID 1 volumes easily.

RAID 5

RAID 5 is a little scary, but it’s the inevitable choice for some applications because of price constraints and/or constraints on the number of disks that can physically fit in the server. It spreads the data across many disks, with distributed parity blocks so that if any one disk fails the data can be rebuilt from the parity blocks. If two disks fail, the entire volume fails unrecoverably. In terms of cost per unit of storage, it’s the most economical redundant configuration, because you lose only one disk’s worth of storage space across the entire array.

Random writes are expensive in RAID 5 because each write to the volume requires two reads and two writes to the underlying disks to compute and store the parity bits. Writes can perform a little better if they are sequential, or if there are many physical disks. On the other hand, both random and sequential reads perform decently. RAID 5 is an acceptable choice for data volumes, or data and logs, for many read-mostly workloads, where the cost of the extra I/O operations for writes isn’t a big deal.

The biggest performance cost with RAID 5 occurs if a disk fails because the data has to be reconstructed by reading all the other disks. This affects performance severely, and it’s even worse if you have lots of disks. If you’re trying to keep the server online during the rebuild, don’t expect either the rebuild or the array’s performance to be good. If you use RAID 5, it’s best to have some mechanism to fail over and take a machine out of service when there’s a problem. Either way, it’s a good idea to benchmark your system with a failed drive and during recovery, so you know what to expect. The disk performance might degrade by a factor of two or more with a failed drive and by a factor of five or more when rebuilding is in progress, and a server with storage that’s two to five times slower might be disproportionately affected overall.

Other performance costs include limited scalability because of the parity blocks--RAID 5 doesn’t scale well past 10 disks or so--and caching issues. Good RAID 5 performance depends heavily on the RAID controller’s cache, which can conflict with the database server’s needs. We discuss caching a bit later.

One of the mitigating factors for RAID 5 is that it’s so popular. As a result, RAID controllers are often highly optimized for RAID 5, and despite the theoretical limits, smart controllers that use caches well can sometimes perform nearly as well as RAID 10 controllers for some workloads. This might actually reflect that the RAID 10 controllers are less highly optimized, but regardless of the reason, this is what we’ve seen.

RAID 6

The largest issue with RAID 5 was the loss of two disks was catastrophic. The more disks that you have in your array, the higher probability you have of disk failure. RAID 6 helps to curb the failure possibility by adding a second parity disk. This allows you to sustain 2 disk failures and still rebuild the array. The downside is that calculating the additional parity will make writes slower than RAID 5.

RAID 10

RAID 10 is a very good choice for data storage. It consists of mirrored pairs that are striped, so it scales both reads and writes well. It is fast and easy to rebuild, in comparison to RAID 5. It can also be implemented in software fairly well.

The performance loss when one hard drive goes out can still be significant because that stripe can become a bottleneck. Performance can degrade by up to 50%, depending on the workload. One thing to watch out for is RAID controllers that use a “concatenated mirror” implementation for RAID 10. This is suboptimal because of the absence of striping: your most frequently accessed data might be placed on only one pair of disks, instead of being spread across many, so you’ll get poor performance.

RAID 50

RAID 50 consists of RAID 5 arrays that are striped, and it can be a good compromise between the economy of RAID 5 and the performance of RAID 10 if you have many disks. This is mainly useful for very large datasets, such as data warehouses or extremely large OLTP systems.

The following table summarizes various RAID configurations.

Network Configuration

Just as latency and throughput are limiting factors for a hard drive, latency and bandwidth are limiting factors for a network connection. The biggest problem for most applications is latency; a typical application does a lot of small network transfers, and the slight delay for each transfer adds up.

A network that’s not operating correctly is a major performance bottleneck, too. Packet loss is a common problem. Even 1% loss is enough to cause significant performance degradation because various layers in the protocol stack will try to fix the problems with strategies such as waiting a while and then resending packets, which adds extra time. Another common problem is broken or slow Domain Name System (DNS) resolution.

DNS is enough of an Achilles heel that enabling skip_name_resolve is a good idea for production servers. Broken or slow DNS resolution is a problem for lots of applications, but it’s particularly severe for MySQL. When MySQL receives a connection request, it does both a forward and a reverse DNS lookup. There are lots of reasons that this could go wrong. When it does, it will cause connections to be denied, slow down the process of connecting to the server, and generally wreak havoc, up to and including denial-of-service attacks. If you enable the skip_name_resolve option, MySQL won’t do any DNS lookups at all. However, this also means that your user accounts must have only IP addresses, “localhost,” or IP address wildcards in the host column. Any user account that has a hostname in the host column will not be able to log in.

It’s usually more important, though, to adjust your settings to deal efficiently with a lot of connections and small queries. One of the more common tweaks is to change your local port range. Linux systems have a range of local ports that can be used. When the connection is made back to a caller, it uses a local port. If you have many simultaneous connections, you can run out of local ports.

Here’s a system that is configured to default values:

[root@server ~]# cat /proc/sys/net/ipv4/ip_local_port_range
32768 61000

Sometimes you might need to change these values to a larger range. For example:

[root@server ~]# echo 1024 65535 >
/proc/sys/net/ipv4/ip_local_port_range

The TCP protocol allows a system to queue up incoming connections, like a bucket. If the bucket fills up, clients won’t be able to connect. You can allow more connections to queue up as follows:

[root@server ~]# echo 4096 > /proc/sys/net/ipv4/tcp_max_syn_backlog

For database servers that are used only locally, you can shorten the timeout that comes after closing a socket in the event that the peer is broken and doesn’t close its side of the connection. The default is one minute on most systems, which is rather long:

[root@server ~]# echo <value> > /proc/sys/net/ipv4/tcp_fin_timeout

Most of the time these settings can be left at their defaults. You’ll typically need to change them only when something unusual is happening, such as extremely poor network performance or very large numbers of connections. An Internet search for “TCP variables” will turn up lots of good reading about these and many more variables.

Choosing a Filesystem

Your filesystem choices are pretty dependent on your operating system. In many systems, such as Windows, you really have only one or two choices, and only one (NTFS) is really viable. GNU/Linux, on the other hand, supports many filesystems.

Many people want to know which filesystems will give the best performance for MySQL on GNU/Linux, or, even more specifically, which of the choices is best for InnoDB. The benchmarks actually show that most of them are very close in most respects, but looking to the filesystem for performance is really a distraction. The filesystem’s performance is very workload-specific, and no filesystem is a magic bullet. Most of the time, a given filesystem won’t perform significantly better or worse than any other filesystem. The exception is if you run into some filesystem limit, such as how it deals with concurrency, working with many files, fragmentation, and so on.

It’s more important to consider crash recovery time and whether you’ll run into specific limits, such as slow performance on directories with many files (a notorious problem with ext2 and older versions of ext3, but solved in modern versions of ext3 and ext4 with the dir_index option). The filesystem you choose is very important in ensuring your data’s safety, so we strongly recommend you don’t experiment on production systems.

When possible, it’s best to use a journaling filesystem, such as ext3, ext4, XFS, ZFS, or JFS. If you don’t, a filesystem check after a crash can take a long time. If the system is not very important, non-journaling filesystems might perform better than transactional ones. For example, ext2 might perform better than ext3, or you can use tunefs to disable the journaling feature on ext3. Mount time is also a factor for some filesystems. ReiserFS, for instance, can take a long time to mount and perform journal recovery on large partitions.

If you use ext3 or its successor ext4, you have three options for how the data is journaled, which you can place in the /etc/fstab mount options:

data=writeback

This option means only metadata writes are journaled. Writes to the metadata are not synchronized with the data writes. This is the fastest configuration, and it’s usually safe to use with InnoDB because it has its own transaction log. The exception is that a crash at just the right time could cause corruption in a .frm file.

Here’s an example of how this configuration could cause problems. Say a program decides to extend a file to make it larger. The metadata (the file’s size) will be logged and written before the data is actually written to the (now larger) file. The result is that the file’s tail--the newly extended area - contains garbage.

data=ordered

This option also journals only the metadata, but it provides some consistency by writing the data before the metadata so that they stay consistent. It’s only slightly slower than the writeback option, and it’s much safer when there’s a crash.

In this configuration, if we suppose again that a program wants to extend a file, the file’s metadata won’t reflect the file’s new size until the data that resides in the newly extended area has been written.

data=journal

This option provides atomic journaled behavior, writing the data to the journal before it’s written to the final location. It is usually unnecessary and has much higher overhead than the other two options. However, in some cases, it can improve performance because the journaling lets the filesystem delay the writes to the data’s final location.

Regardless of the filesystem, there are some specific options that it’s best to disable because they don’t provide any benefit and can add quite a bit of overhead. The most famous is recording access time, which requires a write even when you’re reading a file or directory. To disable this option, add the noatime,nodiratime mount options to your /etc/fstab; this can sometimes boost performance by as much as 5–10%, depending on the workload and the filesystem (although it might not make much difference in other cases). Here’s a sample /etc/fstab line for the ext3 options we mentioned: /dev/sda2 /usr/lib/mysql ext3 noatime,nodiratime,data=writeback 0 1.

You can also tune the filesystem’s read-ahead behavior because it might be redundant. For example, InnoDB does its own read-ahead prediction. Disabling or limiting read-ahead is especially beneficial on Solaris’s UFS. Using innodb_flush_method=O_DIRECT automatically disables read-ahead.

Some filesystems don’t support features you might need. For example, support for direct I/O might be important if you’re using the O_DIRECT flush method for InnoDB. Also, some filesystems handle a large number of underlying drives better than others; XFS is often much better at this than ext3, for instance. Finally, if you plan to use LVM snapshots for initializing replicas or taking backups, you should verify that your chosen filesystem and LVM version work well together.

The next table summarizes the characteristics of some common filesystems.

We usually recommend using the XFS filesystem. The ext3 filesystem just has too many serious limitations, such as its single mutex per inode, and bad behavior such as flushing all dirty blocks in the whole filesystem on fsync() instead of just one file’s dirty blocks. The ext4 filesystem is an acceptable choice, although there have been performance bottlenecks in specific kernel versions that you should investigate before committing to it.

When considering any filesystem for a database, it’s good to consider how long it has been available, how mature it is and how proven it has been in production environments. The filesystem bits are the very lowest level of data integrity you have in a database.

$ cat /sys/block/sda/queue/scheduler
noop deadline [cfq]

$ cat /proc/sys/vm/swappiness
60

$ echo 0 > /proc/sys/vm/swappiness

$ vmstat 5
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 2632 25728 23176 740244 0 0 527 521 11 3 10 1 86 3
0 0 2632 27808 23180 738248 0 0 2 430 222 66 2 0 97 0

$ iostat -dx 5
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 1.6 2.8 2.5 1.8 138.8 36.9 40.7 0.1 23.2 6.0 2.6

concurrency = (r/s + w/s) * (svctm/1000)

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 105 311 298 820 3236 9052 10 127 113 9 96

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sdc 81 0 280 0 3164 0 11 2 7 3 99

Summary

Choosing and configuring hardware for MySQL, and configuring MySQL for the hardware, is not a mystical art. In general, you need the same skills and knowledge that you need for most other purposes. However, there are some MySQL-specific things you should know.

What we commonly suggest for most people is to find a good balance between performance and cost. First, we like to use commodity servers, for many reasons. For example, if you’re having trouble with a server and you need to take it out of service while you try to diagnose it, or if you simply want to try swapping it with another server as a form of diagnosis, this is a lot easier to do with a $5,000 server than one that costs $50,000 or more. MySQL is also typically a better fit--both in terms of the software itself and in terms of the typical workloads it runs--for commodity hardware.

The four fundamental resources MySQL needs are CPU, memory, disk, and network resources. The network doesn’t tend to show up as a serious bottleneck very often, but CPUs, memory, and disks certainly do. The balance of speed and quantity really depends on the workload and you should strive for a balance of fast and many as your budget allows. The more concurrency you expect, the more you should lean on more CPUs to accommodate your workload.

The relationship between CPUs, memory, and disks is intricate, with problems in one area often showing up elsewhere. Before you throw resources at a problem, ask yourself whether you should be throwing resources at a different problem instead. If you’re I/O bound, do you need more I/O capacity, or just more memory? The answer hinges on the working set size, which is the set of data that’s needed most frequently over a given duration.

Solid-state devices are great for improving server performance overall, and should generally be the standard for databases now, especially OLTP workloads. The only reason to continue using HDDs is in extremely budget constrained systems or ones where you need a staggeringly high amount of disk space - on the order of petabytes in a data warehousing situation.

In terms of the operating system, there are just a few Big Things that you need to get right, mostly related to storage, networking, and virtual memory management. If you use GNU/Linux, as most MySQL users do, we suggest using the XFS filesystem and setting the swappiness and disk queue scheduler to values that are appropriate for a server. There are some network parameters that you might need to change, and you might wish to tweak a number of other things (such as disabling SELinux), but those changes are a matter of preference.