Console Login

Diagnosing MySQL I/O Bottlenecks on Linux: Scheduler, Mounts, InnoDB

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:

  • %iowait in 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 with await and the queue length. Treat svctm as 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_size in 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_capacity of 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 relatime by 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 (including O_DIRECT), innodb_read_io_threads, innodb_write_io_threads, innodb_flush_log_at_trx_commit and innodb_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/scheduler and as a default with the elevator= kernel boot parameter.
  • Linux kernel documentation, ext4. It documents the barrier and nobarrier mount options and notes that barriers protect data integrity when a volatile write cache is present. It also documents noatime and relatime.

Fix options

MethodWhat it changesCost and risk
Larger InnoDB buffer poolFewer reads reach the diskNeeds free RAM. Setting it too high causes swapping.
Fewer I/Os from the applicationMissing indexes, full scans, temporary tables on disk, unnecessary session writesNeeds 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 secondHardware or plan change. Check that the volume really sits on that hardware.
I/O scheduler noop or deadlineRemoves seek-oriented reordering that does not help flash or virtual disksLow risk, easy to revert. Gains are modest.
Mount options (noatime)Avoids metadata writes on readsVery low risk. Rarely a problem, though some old mail tools check atime.
Disabling barriers (nobarrier)Skips cache flush requestsCan corrupt data on power loss unless the entire write path is protected. See the next section.
InnoDB I/O settingsinnodb_io_capacity, I/O threads, O_DIRECT, log sizeLow 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 commitAn 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:

  1. Confirm the bottleneck with iostat and the MySQL status counters. Without that, the settings below are guesses.
  2. 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.
  3. Use O_DIRECT to avoid caching the same pages twice, once in the buffer pool and once in the page cache.
  4. Switch the scheduler to noop (or deadline) only when the device is flash or a virtual disk whose host does its own scheduling. Keep CFQ or use deadline on a local rotating disk.
  5. Raise innodb_io_capacity only if the storage can sustain the higher rate, which is the case for SSDs and not for a single SATA disk.
  6. 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.
  7. 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 await stays 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 noop and deadline is 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 = 2 and nobarrier trade 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_capacity on 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 %iowait is low and CPU is high, the bottleneck is queries or code, and tools such as the slow query log and EXPLAIN are the right starting point.