The problem
A typical case is a PHP application (a Magento shop or a Drupal site, for example) on a single MySQL 5.5 server. Under traffic spikes the web tier starts returning 502 or 504 errors, and the load average climbs far above the core count. top shows the CPUs mostly idle, but the wa (iowait) figure is high and the mysqld process sits in uninterruptible sleep (state D). Queries that normally take milliseconds take seconds, and commits and session writes are the slowest of all.
The load average on Linux counts processes waiting for disk as well as processes waiting for a CPU. A high load with idle CPUs therefore points at storage, not at application code.
How to confirm it
Run iostat -x 1 from the sysstat package and watch these columns:
%iowaitin the CPU line: the share of time CPUs are idle while I/O is outstanding.await: the average time in milliseconds a request spends queued plus being serviced. Values far above a few milliseconds on a rotating disk mean requests are queueing.avgqu-sz: the average queue length.%util: the share of time the device had requests in flight. On a single spindle, a value near 100 means saturation. On SSDs, RAID sets and virtual block devices that serve requests in parallel, 100 does not by itself prove saturation, so read it together withawaitand the queue length. Treatsvctmas unreliable; the sysstat documentation itself warns against relying on it.
Why it happens
A database that does not fit in memory performs random reads and writes. A rotating disk serves random requests at a rate limited by seek time and rotational latency, which gives a low ceiling on operations per second however many CPU cores exist. Sequential throughput figures do not help here. Once the request rate exceeds that ceiling, the queue grows and await grows with it, since every request waits for all the requests ahead of it.
Several factors make this worse:
- Shared storage. On a virtualised host, the disks serve every guest on the node. A neighbour's heavy workload appears as higher latency in the guest. Container virtualisation such as OpenVZ also shares one kernel and one I/O scheduler across all containers, so the guest cannot change the scheduler.
- A small buffer pool. The default
innodb_buffer_pool_sizein MySQL 5.5 is small, 128 MB. Most reads therefore go to disk. - Durability settings. With
innodb_flush_log_at_trx_commit = 1, every commit forces a flush of the log. On slow storage, commit latency becomes disk flush latency. - Defaults tuned for spinning disks. The default
innodb_io_capacityof 200 assumes a single rotating disk and limits how aggressively InnoDB flushes dirty pages in the background. The default CFQ scheduler on the 2.6.32 kernels in CentOS 6 and Debian Squeeze reorders requests to reduce head movement, which has no benefit on flash and little on a virtual disk whose real placement the guest cannot see. - Extra writes. Atime updates turn some reads into writes. This is less common than before, because kernels from 2.6.30 onward mount with
relatimeby default.
Source of truth
The settings in this article are taken from the following references:
- MySQL 5.5 Reference Manual, InnoDB startup options and system variables. It defines
innodb_io_capacity(default 200, a dynamic variable),innodb_flush_method(includingO_DIRECT),innodb_read_io_threads,innodb_write_io_threads,innodb_flush_log_at_trx_commitandinnodb_log_file_size. It also states what each durability setting gives up. - Linux kernel documentation, switching I/O schedulers. It confirms that the scheduler is selected per device at runtime through
/sys/block/<device>/queue/schedulerand as a default with theelevator=kernel boot parameter. - Linux kernel documentation, ext4. It documents the
barrierandnobarriermount options and notes that barriers protect data integrity when a volatile write cache is present. It also documentsnoatimeandrelatime.
Fix options
| Method | What it changes | Cost and risk |
|---|---|---|
| Larger InnoDB buffer pool | Fewer reads reach the disk | Needs free RAM. Setting it too high causes swapping. |
| Fewer I/Os from the application | Missing indexes, full scans, temporary tables on disk, unnecessary session writes | Needs application and schema work, but gives the largest gains when queries are inefficient. |
| Faster storage (SSD, or RAID with a battery-backed write cache) | Lower latency and many more random operations per second | Hardware or plan change. Check that the volume really sits on that hardware. |
I/O scheduler noop or deadline | Removes seek-oriented reordering that does not help flash or virtual disks | Low risk, easy to revert. Gains are modest. |
Mount options (noatime) | Avoids metadata writes on reads | Very low risk. Rarely a problem, though some old mail tools check atime. |
Disabling barriers (nobarrier) | Skips cache flush requests | Can corrupt data on power loss unless the entire write path is protected. See the next section. |
| InnoDB I/O settings | innodb_io_capacity, I/O threads, O_DIRECT, log size | Low risk when the storage can sustain the values. A wrong value in either direction wastes the hardware. |
Relaxed durability (innodb_flush_log_at_trx_commit = 2) | Log is flushed once per second rather than at each commit | An operating system crash or power loss can lose about the last second of committed transactions. |
Why this fix
The recommended order is measure first, then reduce I/O, then tune for the storage that is actually present. In concrete terms:
- Confirm the bottleneck with
iostatand the MySQL status counters. Without that, the settings below are guesses. - Give InnoDB a buffer pool that is large but leaves room for the operating system and per-connection memory. On a 4 GB machine that is dedicated to MySQL, 2 GB is a cautious starting point.
- Use
O_DIRECTto avoid caching the same pages twice, once in the buffer pool and once in the page cache. - Switch the scheduler to
noop(ordeadline) only when the device is flash or a virtual disk whose host does its own scheduling. Keep CFQ or usedeadlineon a local rotating disk. - Raise
innodb_io_capacityonly if the storage can sustain the higher rate, which is the case for SSDs and not for a single SATA disk. - Keep barriers enabled. A battery-backed RAID controller can justify
nobarrier, because its cache survives a power loss. A data centre with UPS and generators does not justify it, because the drive's own volatile write cache or a host crash can still lose in-flight writes. Many SSDs have no power-loss protection, so flash alone is not a reason either. - Choose the durability level deliberately. Level 1 keeps full ACID durability. Level 2 is acceptable for data that can be lost for a second, such as sessions or caches, but not for orders or payments.
Step by step
The commands below target CentOS 6 with the 2.6.32 kernel, ext4 and MySQL 5.5. They were not executed while this article was prepared, so no output is reproduced. Adjust device names: a KVM guest with virtio disks uses vda instead of sda.
1. Measure
yum install sysstat
iostat -x 1 10
vmstat 1 10
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_os_log_fsyncs'"
Compare Innodb_buffer_pool_reads (reads that had to go to disk) with Innodb_buffer_pool_read_requests (all logical reads). A high ratio of the first to the second means the buffer pool is too small for the working set.
2. Check and change the scheduler at runtime
cat /sys/block/sda/queue/scheduler
echo noop > /sys/block/sda/queue/scheduler
cat /sys/block/sda/queue/scheduler
The active scheduler is shown in square brackets. Re-run iostat -x 1 under the same load and compare await with the earlier reading. If nothing improves, revert with echo cfq > /sys/block/sda/queue/scheduler.
3. Make the scheduler permanent
CentOS 6 uses GRUB legacy, so the file to edit is /boot/grub/grub.conf (/boot/grub/menu.lst is a symlink to it). The file /etc/default/grub belongs to GRUB 2 and is not used on this release. Append the parameter to the existing kernel line:
kernel /vmlinuz-2.6.32-220.el6.x86_64 ro root=/dev/mapper/VolGroup-lv_root rd_NO_LUKS LANG=en_US.UTF-8 rd_NO_MD quiet rhgb crashkernel=auto KEYBOARDTYPE=pc KEYTABLE=no rd_NO_LVM rd_NO_DM elevator=noop
To apply the scheduler to a single device without a reboot, add a line such as echo noop > /sys/block/sda/queue/scheduler to /etc/rc.local.
4. Mount options
/dev/mapper/VolGroup-lv_root / ext4 defaults,noatime 1 1
UUID=5b090266-7503-460c-9907-775d47913531 /boot ext4 defaults,noatime 1 2
Apply it without a reboot, then check that the option is active:
mount -o remount,noatime /
grep ' / ' /proc/mounts
Do not add barrier=0 unless the write path is known to be protected, as described above.
5. InnoDB settings
An example /etc/my.cnf fragment for a 4 GB machine dedicated to MySQL on fast storage:
[mysqld]
default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
# 1 = full durability, 2 = flush once per second
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 2000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity can be tested on the running server before it is written to the file:
mysql -e "SET GLOBAL innodb_io_capacity = 2000"
mysql -e "SHOW VARIABLES LIKE 'innodb_io_capacity'"
6. Resizing the redo log safely
MySQL 5.5 refuses to start InnoDB if innodb_log_file_size no longer matches the existing log files, so changing it needs a clean shutdown and a move of the old files. Do this in a maintenance window:
mysql -e "SET GLOBAL innodb_fast_shutdown = 0"
service mysqld stop
mkdir /root/ib_logs_old
mv /var/lib/mysql/ib_logfile* /root/ib_logs_old/
service mysqld start
mysql -e "SHOW VARIABLES LIKE 'innodb_log_file_size'"
The server creates new log files of the configured size at start. Delete the old ones once the server runs normally. The same restart also applies the buffer pool, flush method and thread settings, which are not dynamic in 5.5.
7. Verify
iostat -x 1 10
mysql -e "SHOW ENGINE INNODB STATUS\G"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free'"
Compare await, %iowait and the load average with the figures from step 1, under comparable traffic. Change one setting at a time so that the effect of each is clear.
Limits and alternatives
- Software tuning cannot replace faster storage. The scheduler, mount options and InnoDB settings reduce overhead and allow the hardware to be used fully. They do not make a saturated rotating array faster. When
awaitstays high with a well-sized buffer pool and a sensible query load, the practical fix is lower-latency storage such as SSDs, or a RAID controller with a battery-backed write cache, and not further tuning. - Do not rely on the scheduler alone. The difference between
noopanddeadlineis small compared with the effects of a correct buffer pool, good indexes and fewer synchronous writes. - Virtualisation affects what can be changed. In a full-virtualisation guest (KVM) the guest kernel and its scheduler can be configured. In a container (OpenVZ) they cannot, because the host chooses them.
- Durability trade-offs are real.
innodb_flush_log_at_trx_commit = 2andnobarriertrade safety for speed. Use them only when the data tolerates loss or the hardware guarantees the write path, and document the decision. - Values need to fit the hardware. A high
innodb_io_capacityon slow disks causes bursts of flushing that make latency worse. Start with the measured capability of the device and raise it gradually. - Look elsewhere when the problem is not I/O. If
%iowaitis low and CPU is high, the bottleneck is queries or code, and tools such as the slow query log andEXPLAINare the right starting point.