The problem
A database-backed web application feels slow, yet the web tier is not busy. A typical sign is a CPU that is mostly idle while the iowait figure in top or vmstat stays high. The database is not computing. It is waiting for the disk. Rewriting application code does not help in this case, because the time is spent in storage.
Related symptoms:
- Writes stall while other sessions queue behind them, especially on tables that still use MyISAM.
- Queries that touch the same working set again and again keep going to disk instead of being served from memory.
- Latency spikes appear when the kernel swaps out part of the MySQL process.
- Every commit is slow because each one forces a disk flush.
Why it happens
Several independent causes usually add up.
- Storage limits. A mechanical disk can serve only a small number of random I/O operations per second. A database doing random reads and writes reaches that ceiling quickly, and no setting in
my.cnfraises it. - Table-level locking. MyISAM locks the whole table for a write. Concurrent writers and readers wait on each other. InnoDB locks individual rows and keeps a transaction log.
- Conservative defaults. The stock configuration assumes a small machine. The InnoDB buffer pool defaults to a few megabytes, so the data cache is tiny and most reads hit the disk.
- Durable commits. With
innodb_flush_log_at_trx_commit = 1, the redo log is written and flushed to stable storage at every commit. This is the safe setting, and it is bound by disk latency. - Kernel behaviour. The default I/O scheduler on CentOS 6 is
cfq. Inside a virtual machine, request ordering is already handled by the host, so extra reordering in the guest adds little. The kernel may also swap out database memory in favour of the file cache.
A correction to a common claim
MySQL 5.5 made InnoDB the default storage engine, but CentOS 6 itself ships MySQL 5.1, where the default engine is still MyISAM. A stock CentOS 6 install therefore creates MyISAM tables unless the engine is named explicitly. MySQL 5.5 comes from the vendor's own RPM repository or a third-party repository, not from the base distribution.
Source of truth
The fixes rest on the MySQL 5.5 Reference Manual and the Linux kernel documentation.
- MySQL 5.5 InnoDB startup options and system variables. It lists
innodb_buffer_pool_size,innodb_log_file_size,innodb_flush_log_at_trx_commit,innodb_flush_methodand the I/O thread settings. It gives each variable's meaning, default and allowed values. For the flush variable, the allowed values are 0, 1 and 2, and 1 is the default, which gives full ACID durability. - Changing the size of the InnoDB log files (5.5 manual). In 5.5 the server does not resize the redo logs by itself. After a clean shutdown the old
ib_logfile*files must be moved away so the server can create new ones at the configured size. - MySQL 5.5 slow query log. It describes
slow_query_log,slow_query_log_fileandlong_query_time, which are used for measuring. - Linux kernel: switching I/O schedulers. It confirms that the scheduler is chosen per block device at runtime through
/sys/block/<device>/queue/schedulerand at boot with theelevator=parameter. - Percona Toolkit: pt-query-digest. It confirms that the tool summarises a slow query log and ranks queries by total time.
Ways to fix it
1. Faster storage
Moving the data directory from spinning disks to SSD raises random I/O capacity far more than any software setting. Where that is not possible, a flash cache in front of the disks, such as Flashcache, is a partial alternative.
2. Change the engine
Convert MyISAM tables to InnoDB for row-level locking, crash recovery and a buffer pool that caches data as well as indexes.
3. Tune InnoDB memory and logs
Enlarge the buffer pool and the redo logs so that reads come from memory and checkpoints are less frequent.
4. Relax the commit flush policy
| Value | Behaviour | Loss window |
|---|---|---|
1 | Write and flush the log at every commit | None for committed transactions |
2 | Write at every commit, flush about once per second | About one second of transactions if the OS crashes or power fails; a mysqld crash alone loses nothing |
0 | Write and flush about once per second | About one second, even after a mysqld crash |
5. Tune the operating system
Use the deadline or noop scheduler and lower vm.swappiness.
6. Fix the queries
Add indexes and rewrite the worst statements, found through the slow query log. This reduces I/O at the source.
Why this fix
The chosen order is to measure first, then fix the engine, memory and logs, then the OS settings. The commit policy comes last and only if the data allows it.
- Measuring comes first because iowait can also come from a missing index. The slow log shows which queries cost the most.
- The engine and the buffer pool are changed first because they remove I/O. They cost nothing in durability.
- The log file size is raised together with the pool, since a small redo log forces frequent flushing of dirty pages.
innodb_flush_log_at_trx_commit = 2is optional. It trades up to about a second of committed data, after an OS crash or power loss, for much cheaper commits. It suits sessions, carts and caches. It does not suit financial records. Keep1when every commit must survive.- Scheduler and swappiness are low-risk changes that are easy to reverse.
- A faster disk remains the strongest single change. Software tuning cannot replace it.
Corrections to the usual recipe
- Per-thread buffers are multiplied by the number of connections. With 300 connections and the commonly quoted values (2M sort, 2M read, 8M random read, 8M join), the theoretical worst case is about 6 GB. That is as large as the buffer pool, and it risks the out-of-memory killer. Keep these buffers small, around 256K to 1M, and raise them per session only for a query that needs it.
- Memory claimed in a virtual machine is not guaranteed by the hypervisor type alone. Check how the host allocates RAM before relying on it.
- A value of 70 to 80 percent of RAM for the buffer pool is a guide for a dedicated server. The remainder must cover the OS, connection buffers and other memory.
Applying the fix
The following commands assume a dedicated 8 GB server running MySQL 5.5 on CentOS 6, accessed as root. Adjust device names and paths to the system. No output is reproduced here, because results depend on the machine. Each step ends with the check to run.
Step 1: confirm that the disk is the bottleneck
vmstat 5
iostat -x 5
A high wa column in vmstat and a high %util with a long await in iostat for the data disk point to storage. iostat is in the sysstat package.
Step 2: find and convert MyISAM tables
mysql -e "SELECT table_schema, table_name FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema NOT IN ('mysql','information_schema','performance_schema')"
mysql -e "ALTER TABLE shop.orders ENGINE=InnoDB"
Run the ALTER once per table, replacing shop.orders. It locks the table while it is rebuilt, so schedule it for a quiet period. Tables that depend on FULLTEXT indexes cannot be converted in 5.5, because InnoDB gained FULLTEXT support only in 5.6.
Step 3: edit the MySQL configuration
In the [mysqld] section of /etc/my.cnf:
[mysqld]
default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_buffer_pool_size = 6G
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
query_cache_type = 0
query_cache_size = 0
sort_buffer_size = 512K
read_buffer_size = 512K
read_rnd_buffer_size = 1M
join_buffer_size = 512K
max_allowed_packet = 64M
max_connections = 300
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
O_DIRECT avoids caching the same data in both the buffer pool and the OS page cache. It is only suitable when the data lives on a local disk or a well-behaved volume. Change the flush value to 2 only if the loss window described above is acceptable. The directory for the slow log must exist and be writable by the mysql user:
mkdir -p /var/log/mysql
chown mysql:mysql /var/log/mysql
Step 4: resize the redo logs safely
In 5.5, the old log files must be moved away after a clean shutdown. The service name is mysqld for most distribution packages and mysql for some vendor RPMs.
mysql -e "SET GLOBAL innodb_fast_shutdown = 0"
service mysqld stop
mkdir /root/ib_logfile_backup
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1 /root/ib_logfile_backup/
service mysqld start
Watch the error log during start-up. The server should report that it is creating new log files at the configured size. Keep the moved files until the server has run normally for a while.
Step 5: set the I/O scheduler
On a KVM guest with virtio disks, the device is usually vda rather than sda.
cat /sys/block/sda/queue/scheduler
echo deadline > /sys/block/sda/queue/scheduler
cat /sys/block/sda/queue/scheduler
The active scheduler is shown in square brackets. To keep the change after a reboot, append the option to the kernel line in /boot/grub/grub.conf:
kernel /vmlinuz-2.6.32-220.el6.x86_64 ro root=/dev/mapper/vg-root rhgb quiet elevator=deadline
Only append elevator=deadline to the existing line. Do not replace the other options. noop is a valid alternative for a guest.
Step 6: reduce swapping
cat /proc/sys/vm/swappiness
sysctl -w vm.swappiness=1
echo "vm.swappiness = 1" >> /etc/sysctl.conf
sysctl -p
A value of 1 is used instead of 0 here. It keeps swapping strongly discouraged. The server also needs enough free memory, since the setting does not create any.
Step 7: verify and measure
mysql -e "SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'"
mysql -e "SHOW ENGINE INNODB STATUS\G"
pt-query-digest /var/log/mysql/mysql-slow.log
Compare Innodb_buffer_pool_reads (reads that went to disk) with Innodb_buffer_pool_read_requests (logical reads). After warm-up, the first should be a small fraction of the second. Repeat vmstat and iostat from step 1 under comparable load. pt-query-digest ranks the remaining slow statements for indexing or rewriting.
Limits and alternatives
- What this achieves. It removes avoidable I/O: lock contention from MyISAM, cache misses from a tiny buffer pool, and swapping. It cannot raise the IOPS ceiling of a slow disk.
- Durability. Setting the flush policy to
2or0weakens ACID guarantees. Choose it per workload, not as a default. - Memory arithmetic. A large buffer pool leaves less for connections. If the server swaps or the OOM killer acts, reduce the pool or the connection count.
- Write-heavy loads. If writes still dominate after tuning, choose faster storage, a battery-backed write cache on a RAID controller, or a split of reads onto replicas.
- Query problems. If a few statements dominate the digest, indexes and query changes help more than any server setting.
- Version caveat. This recipe targets MySQL 5.5. Later releases change some behaviour, including redo log resizing and the semantics of low swappiness values on newer kernels.