MySQL Performance Tuning: Surviving the Digg Effect with InnoDB
It starts with a creeping load average. Your Apache processes stack up. Users in Oslo start complaining that the checkout page is timing out. You check top and see MySQL consuming 140% CPU. Panic sets in.
I’ve seen this scenario play out a dozen times this year. Everyone wants to blame the PHP code, but 9 times out of 10, the bottleneck is a poorly tuned database running on oversubscribed hardware. If you are still running default MySQL 5.0 or 5.1 configurations on a generic VPS, you are essentially waiting for your site to crash.
In the Norwegian hosting market, where latency to NIX (Norwegian Internet Exchange) is measured in single-digit milliseconds, a slow database query is the difference between a conversion and a bounce. Here is how we tune MySQL for high-performance web applications, using techniques standard on our CoolVDS Xen infrastructure.
1. The Engine War: MyISAM vs. InnoDB
Default MySQL installations often lean on MyISAM. For read-heavy static sites, MyISAM is fast. But for modern web applications—forums, e-commerce, anything with user sessions—MyISAM is a disaster waiting to happen.
The problem is Table-Level Locking. When a user writes to a MyISAM table, the entire table is locked. No one else can read or write until that operation finishes. On a busy site, these locks pile up fast.
The Solution: Switch to InnoDB. It supports Row-Level Locking, meaning users can modify different rows in the same table simultaneously without blocking each other. It also supports transactions (ACID compliance), which is non-negotiable for e-commerce data integrity.
To check your tables, run this in the MySQL shell:
SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'your_database';
2. The Holy Grail: innodb_buffer_pool_size
If you change only one setting in your /etc/my.cnf, make it this one. By default, MySQL might set this to a paltry 8MB. This forces InnoDB to read from the disk for almost every query.
Disk I/O is the slowest operation in computing. Even with the enterprise-grade 15k RPM SAS RAID-10 arrays we use at CoolVDS, RAM is orders of magnitude faster.
Configuration Rule of Thumb:
- Dedicated Database Server: Set
innodb_buffer_pool_sizeto 70-80% of total RAM. - Shared Web/DB VPS: Be conservative. If you have a 4GB VPS, set it to 1GB-1.5GB. Leave room for Apache/PHP and the OS. If you swap to disk, performance dies instantly.
[mysqld]
innodb_buffer_pool_size = 1536M3. The Query Cache Trap
Many admins crank up query_cache_size thinking "more is better." It's not. The query cache requires a global lock to update. If you set this too high (over 512MB), the overhead of pruning the cache when table data changes actually slows you down.
Pro Tip: Keep query_cache_size modest (e.g., 64M or 128M) and focus on optimizing your indexes instead. If your hit rate is low, disable it entirely to save CPU cycles.
4. Underlying Hardware: Why "Burstable" RAM is a Lie
You can have the perfect my.cnf, but if your underlying host is overselling resources, it won't matter. Many budget providers use OpenVZ to oversell RAM, relying on "burstable" limits that vanish when you actually need them. When your neighbors get busy, your database suffers from "CPU steal" and I/O wait.
This is why serious sysadmins prefer Xen virtualization. It offers strict hardware isolation. When you buy 2GB of RAM on a CoolVDS Xen instance, that memory is reserved for you. It cannot be stolen by another customer.
Disk I/O and RAID
Database performance is bound by Input/Output Operations Per Second (IOPS). Writing to a single SATA drive is a bottleneck. You need RAID.
| RAID Level | Read Speed | Write Speed | Verdict for MySQL |
|---|---|---|---|
| RAID 5 | Good | Poor (Parity Calc) | Avoid. Write penalties kill DB performance. |
| RAID 1 | Good | Okay | Decent for small boot drives. |
| RAID 10 | Excellent | Excellent | Essential. The standard for CoolVDS storage. |
5. Local Compliance & Reliability
Performance isn't just about my.cnf; it's about network topology. If your customers are in Norway, hosting your database in Texas adds 150ms of latency to every round trip. Hosting in Oslo reduces that to <10ms.
Furthermore, the Personopplysningsloven (Personal Data Act) imposes strict requirements on how you handle customer data. Hosting locally simplifies compliance with Datatilsynet guidelines, ensuring sensitive data doesn't cross borders unnecessarily.
Summary: The Checklist
- Migrate tables to InnoDB.
- Tune innodb_buffer_pool_size to fit your RAM.
- Avoid massive query_cache_size.
- Host on Xen, not OpenVZ, to guarantee resources.
- Ensure your provider runs RAID-10 (like CoolVDS).
Don't let disk I/O be the reason your site fails during the holiday rush. If your current host is choking on I/O wait, it’s time to move.
Ready for real throughput? Deploy a high-performance Xen VPS on CoolVDS today and see the difference dedicated RAID-10 makes.