MySQL 5.5 Performance Tuning: Stop Optimizing for Rotating Rust

Let’s be honest: default MySQL configurations are garbage. If you install the mysql-server package on a fresh CentOS 6 box today, you are essentially getting a configuration file optimized for a server with 512MB of RAM from 2004. In the last three months alone, I’ve debugged a dozen "slow" Magento stores and Drupal deployments targeting the Norwegian market. The culprit is rarely PHP; it is almost always a choked database layer gasping for I/O.

We are well into 2011. RAM is cheap. SSDs are finally becoming reliable enough for enterprise RAID arrays. MySQL 5.5 is General Availability (GA). If you are still clinging to MyISAM because "it's faster for reads," you are operating on obsolete knowledge. It is time to wake up and tune your stack for the hardware that actually exists today.

1. Kill MyISAM. Long Live InnoDB.

For years, the old guard argued that MyISAM was superior for read-heavy workloads. In 2011, with the arrival of MySQL 5.5, InnoDB became the default storage engine, and for good reason. MyISAM relies on table-level locking. If one user writes to your sessions table, every other user trying to read from it waits. On a high-traffic site, this creates a lock pile-up that looks like the E6 highway into Oslo during rush hour.

InnoDB uses row-level locking and supports ACID transactions. If you are running an e-commerce site handling payments, and you aren't using transactions, you are negligent. Period.

Migration Strategy:

First, check what engines you are using. Do not guess.

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

To convert a table (make sure you backup your /var/lib/mysql first):

ALTER TABLE your_table_name ENGINE=InnoDB;

2. The Holy Grail: innodb_buffer_pool_size

If you change only one setting in your /etc/my.cnf today, make it this one. InnoDB loves to cache data and indexes in memory. The default is often a pathetic 8MB or 128MB. This forces MySQL to go to the disk for almost every read.

The Rule of Thumb: On a dedicated database server, set innodb_buffer_pool_size to 70-80% of your total physical RAM. If you are on a VPS sharing resources with Apache, be conservative—aim for 50% to avoid swapping. Swapping is death for databases.

[mysqld]
# For a 4GB VPS (Dedicated DB)
innodb_buffer_pool_size = 3G

# Additional critical tweaks for 5.5
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2

Pro Tip: Setting innodb_flush_log_at_trx_commit to 2 instead of the default 1 provides a massive performance boost for write-heavy applications. The trade-off is that you might lose up to one second of data in a catastrophic power failure. Given the stability of the grid here and the UPS backups at our Oslo data centers, this is a calculated risk most non-banking apps should take.

3. The I/O Bottleneck and the SSD Revolution

You can tune configurations all day, but you cannot tune physics. Standard 7.2k RPM SATA drives can handle maybe 80-100 Input/Output Operations Per Second (IOPS). SAS 15k drives might get you 180 IOPS. A busy database can chew through that in milliseconds.

This is where hardware selection becomes an architectural decision, not just a procurement detail. When checking server load, battle-hardened sysadmins don't just look at CPU; they look at I/O wait.

# run this to see if your disk is the bottleneck
iostat -x 1

If your %util column is consistently hitting 90-100%, your disk heads are thrashing. You have two options: sharding your database (expensive, complex) or upgrading your underlying storage.

The CoolVDS Advantage: KVM + Pure SSD

This is why we built the CoolVDS platform the way we did. We saw too many providers over-selling "burst" RAM on OpenVZ containers while running on slow SATA backends. It’s a recipe for latency.

We utilize KVM virtualization. Unlike containers, KVM provides true kernel isolation. Your MySQL instance isn't fighting for kernel locks with a noisy neighbor. More importantly, we have deployed enterprise-grade SSD arrays in RAID-10 for our storage tiers. We aren't talking about consumer flash drives; these are high-end units capable of thousands of IOPS.

4. Local Compliance: The Data Trust Factor

Performance isn't just about speed; it's about reliability and legal safety. Hosting your data in the US is becoming increasingly complex due to privacy concerns. By keeping your data on CoolVDS servers located physically in Norway, you simplify compliance with the Personal Data Act (Personopplysningsloven) and Datatilsynet guidelines.

Furthermore, our direct peering at NIX (Norwegian Internet Exchange) ensures that your packets don't take a detour through Frankfurt just to reach a customer in Trondheim. Low latency is a feature of geography as much as technology.

5. Final Configuration Checks

Before you restart mysqld, ensure you aren't using the query cache blindly. In MySQL 5.5 running on multi-core CPUs, the Query Cache mutex can actually become a bottleneck. If you have a write-heavy site, disable it.

query_cache_type = 0
query_cache_size = 0

Don't let legacy configurations dictate your performance. The hardware landscape in 2011 has changed. Your database configuration needs to change with it.

Ready to see what MySQL 5.5 can actually do? Stop fighting I/O wait on rotating disks. Deploy a CoolVDS SSD instance today and watch your load averages drop.