Diagnosing and Resolving High I/O Wait (wa) in LAMP Stack: MySQL and Apache Performance Optimization


1 views

Looking at your server metrics, we can observe several critical indicators:

  • Sustained high load averages (6.66, 10.39, 13.95) despite normal traffic
  • Consistently elevated I/O wait percentages (57.1% and 40.6%)
  • MySQL consuming significant CPU resources (90% and 44% in your samples)
  • Numerous Apache processes with moderate memory usage

First, let's identify what's actually causing the I/O wait. Run these commands while the issue is occurring:

# Show disk activity breakdown
iostat -x 1 5

# Identify specific processes doing I/O
iotop -oP

# Check MySQL's current queries
mysqladmin -u root -p processlist

# Alternatively for MySQL 5.7+
mysql -e "SELECT * FROM sys.processlist WHERE time > 5 ORDER BY time DESC LIMIT 10;"

Based on your configuration, these are the most likely issues:

1. MySQL Disk-bound Operations

Your MySQL process (PID 23930) is showing high CPU usage, which often translates to disk I/O wait when:

  • Large temp tables are being created on disk
  • Poorly indexed queries cause full table scans
  • The InnoDB buffer pool is too small

Check your current InnoDB status:

SHOW ENGINE INNODB STATUS\G

Look for these critical sections:

  • SEMAPHORES (contention)
  • TRANSACTIONS (long-running)
  • BUFFER POOL AND MEMORY (hit ratio)

2. Apache File Access Patterns

Multiple Apache processes accessing:

  • Session files (default in /tmp)
  • Application logs
  • Uncached PHP includes

Check open files per Apache process:

lsof -p $(pgrep apache2 | head -1) | grep -E '\.php|\.log|\.sess'

MySQL Configuration Adjustments

Add these to your my.cnf (adjust based on your 4GB RAM):

[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 4000
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

Apache/PHP Session Handling

Move sessions to RAM (if possible):

# In php.ini
session.save_handler = redis
session.save_path = "tcp://127.0.0.1:6379"

# Alternative tmpfs solution
mount -t tmpfs -o size=512m tmpfs /var/lib/php/sessions

For deeper analysis, use these commands:

# Capture disk stats during peak
vmstat 1 60 > diskstats.txt

# Identify slow MySQL queries
pt-query-digest /var/log/mysql/mysql-slow.log

# Check for disk queue depth
cat /sys/block/sda/queue/nr_requests

# Monitor kernel I/O scheduler
cat /sys/block/sda/queue/scheduler

For a client with similar symptoms, we discovered:

  • WordPress postmeta table had 4.7 million rows
  • A plugin was running full table scans during wp_cron

The solution was:

ALTER TABLE wp_postmeta ADD INDEX meta_key_index (meta_key(32));
UPDATE mysql.innodb_index_stats SET stat_value=1 WHERE stat_name='n_diff_pfx1';
FLUSH TABLES;

This reduced I/O wait from ~50% to under 5% during peak.

If you're on cloud infrastructure:

# AWS EBS optimized instance check
aws ec2 describe-volumes --volume-ids vol-12345 --query 'Volumes[0].Iops'

# For Azure disks:
az disk show --name your-disk --query diskIopsReadWrite

When your Linux server shows sustained high I/O wait percentages (57% wa in your case) with elevated load averages (13-40 range) while MySQL consumes 90% CPU, this indicates serious storage subsystem bottlenecks. The key observations from your top output:

Cpu(s): 20.6%us,  7.9%sy,  0.0%ni, 13.4%id, 57.1%wa,  0.1%hi,  0.9%si,  0.0%st

Run these simultaneously in different terminals:

# Check disk latency
iostat -xmd 2

# Monitor MySQL queries
mysqladmin -u root -p processlist extended-status

# Check disk queue length
vmstat 1 10

# Find heavy IO processes
iotop -oPa

From experience, these patterns often emerge:

# Slow queries locking tables:
SELECT * FROM large_table WHERE non_indexed_column = 'value';

# Unoptimized writes:
INSERT INTO forum_posts VALUES(...) -- with 10+ indexes

# Disk-bound sessions:
grep "disk full" /var/log/mysql/error.log

Add these to /etc/mysql/my.cnf under [mysqld]:

innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1G # (50-70% of RAM)
innodb_io_capacity=2000 # For SSDs
innodb_read_io_threads=16
innodb_write_io_threads=16

Adjust these in /etc/apache2/mods-available/mpm_prefork.conf:


    StartServers            5
    MinSpareServers         5
    MaxSpareServers        10
    MaxRequestWorkers     150 # Depends on RAM
    MaxConnectionsPerChild 1000

When standard fixes don't work:

# Check for disk errors:
smartctl -a /dev/sda

# Monitor file descriptors:
watch "ls /proc/$(pgrep mysqld)/fd | wc -l"

# Capture IO patterns:
blktrace -d /dev/sda -o trace

For production systems with consistent high wa%:

  1. Migrate MySQL to SSD storage
  2. Implement Redis caching for frequent queries
  3. Partition large tables by date ranges