MySQL 5.5 Performance Tuning: Stop Killing Your I/O

It is 3:00 AM. Your monitoring system is screaming. The load average on your database server just hit 25.0, and your web application—whether it's Magento, Drupal, or a custom PHP stack—is timing out. You check top and see MySQL consuming 140% CPU. You check iotop and the disk wait is effectively infinite.

Welcome to the reality of default configurations. Most VPS providers hand you a server with a stock Linux installation where MySQL is configured for a system with 512MB of RAM from 2005. In 2012, this is negligence.

As a sysadmin who has spent the last decade debugging LAMP stacks across the Nordics, I can tell you that 90% of performance issues aren't code—they are configuration and hardware bottlenecks. If you are serving traffic in Norway, latency matters, but raw database throughput matters more.

The Storage Engine War: MyISAM is Dead

If you are still using MyISAM for your production tables, stop reading and migrate to InnoDB right now. With the release of MySQL 5.5, InnoDB became the default engine, and for good reason. MyISAM utilizes table-level locking. If one user writes to the sessions table, every other user trying to read from it waits. On a high-traffic site, this creates a lock pile-up that looks exactly like a server crash.

InnoDB uses row-level locking. It is ACID compliant. It recovers from crashes. To check what you are running, log into the MySQL console:

SELECT TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database_name' AND ENGINE = 'MyISAM';

If that returns results, schedule maintenance and run ALTER TABLE table_name ENGINE=InnoDB; immediately.

The Golden Variable: innodb_buffer_pool_size

By default, many distributions ship with an innodb_buffer_pool_size of 8MB. This is laughable. This variable determines how much data and indexes MySQL caches in RAM. If your data doesn't fit in the pool, MySQL hits the disk. Disk I/O is the death of performance.

The Rule of Thumb: On a dedicated database server, set this to 70-80% of your total available RAM.

Open your /etc/my.cnf (or /etc/mysql/my.cnf on Debian/Ubuntu systems) and adjust:

[mysqld]
# Optimized for a 4GB RAM VPS
innodb_buffer_pool_size = 3G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

Pro Tip: Always set innodb_file_per_table = 1. Without this, all your InnoDB tables live in a single massive file called ibdata1. If you drop a large table later, that file never shrinks. Separating them allows you to reclaim disk space.

The Query Cache Trap

Many junior admins see query_cache_size and think "bigger is better." They set it to 512MB. This is a mistake. The Query Cache requires a global lock to update. If you have a write-heavy application, the overhead of invalidating the cache often costs more than the time saved by caching the query.

For most modern web apps (WordPress, Joomla, Magento), keep it small (under 64MB) or disable it entirely if you are using an external object cache like Memcached.

# A conservative, safe starting point
query_cache_size = 32M
query_cache_limit = 1M

The Hardware Reality: Spindles vs. SSD

You can tune my.cnf until you are blue in the face, but you cannot defeat physics. Traditional SAS 15k RPM drives are fast, but they have a mechanical seek time. In a random-read scenario (typical for databases), a hard drive might give you 150-200 IOPS (Input/Output Operations Per Second).

This is where SSD technology changes the game. While still a premium in the hosting market, Solid State Drives offer near-zero latency and thousands of IOPS. We recently migrated a high-load news portal from a standard RAID-10 SAS array to a CoolVDS SSD instance. The CPU load dropped by 60% instantly because the processor was no longer waiting for the disk.

Benchmarking Disk Latency

Don't believe the marketing hype. Test your disk speed yourself with dd to check write latency:

dd if=/dev/zero of=testfile bs=64k count=16k conv=fdatasync

If you aren't seeing write speeds north of 200MB/s, your database is going to suffer during peak traffic.

Network Latency and Geography

If your target audience is in Norway, hosting your database in Texas or even Frankfurt introduces unavoidable network latency. Every millisecond counts when your application makes 50 database queries to generate a single page.

At CoolVDS, our infrastructure is peered directly at NIX (Norwegian Internet Exchange) in Oslo. This keeps round-trip times (RTT) to local ISPs in the single digits. Furthermore, keeping data within Norwegian borders simplifies compliance with the Personal Data Act (Personopplysningsloven), keeping Datatilsynet happy.

Summary

Performance isn't magic; it's math. To survive the traffic spikes of 2012:

1. Use InnoDB, not MyISAM.
2. Maximize your Buffer Pool.
3. Don't oversize your Query Cache.
4. Move to SSDs.

If you are tired of fighting with "noisy neighbors" on oversold platforms and spinning rust that bottlenecks your SQL queries, it is time for an upgrade. Don't let slow I/O kill your SEO rankings.

Ready to see the difference? Deploy a high-performance SSD VPS with CoolVDS today and experience true low latency.