MySQL Performance Tuning: Stop Killing Your Disk I/O
It starts with a few slow page loads. Maybe your Magento checkout hangs for three seconds. Then the traffic spikes—perhaps you got featured on a busy forum or a news site—and suddenly your server load average hits 20. Apache processes pile up, and top shows MySQL eating 150% CPU. Your site isn't just slow; it's dead.
I've been cleaning up these messes for a decade. The culprit is rarely the CPU. It's almost always disk I/O.
Most default MySQL installations on Linux distributions like CentOS 5 or Debian Lenny are optimized for a machine with 512MB of RAM, not a production web server. If you leave my.cnf untouched, you are essentially asking your database to run with one hand tied behind its back. Here is how we tune for raw speed, stability, and data integrity in the Norwegian hosting market.
1. The MyISAM vs. InnoDB Debate is Over
If you are still running your core application tables on MyISAM, stop. MyISAM uses table-level locking. If one person is writing to the orders table, nobody else can read from it until the write finishes. On a high-traffic site, this creates a queue of processes waiting for locks, driving load averages through the roof.
InnoDB (specifically the InnoDB Plugin available in MySQL 5.1) uses row-level locking. It allows multiple users to read and write simultaneously as long as they aren't touching the exact same row. It is also crash-safe. If the power cuts at the data center, MyISAM tables often get corrupted. InnoDB recovers.
Action: Check your engines.
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database';
2. The Holy Grail: innodb_buffer_pool_size
This is the single most critical setting for performance. MySQL loves RAM. Disk access is slow—even on 15k RPM SAS drives. You want your working data set to live in memory.
By default, this is often set to a pathetic 8MB. If you have a dedicated VPS with 4GB of RAM, and you are running MySQL exclusively, you should allocate 50-70% of that RAM to the buffer pool.
In /etc/my.cnf:
[mysqld]
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 20M
Warning: Do not allocate 100% of your RAM. The OS needs memory, and each connection thread needs memory. If you swap to disk, performance drops to zero.
3. Handle Your Logs: innodb_flush_log_at_trx_commit
This setting controls ACID compliance. The default value is 1, which means every single transaction is flushed to the hard disk immediately. This is the safest for data, but the slowest for I/O.
If you are running a high-volume application where losing 1 second of data in a catastrophic crash is acceptable (like click tracking or session data), change this to 2 or 0.
innodb_flush_log_at_trx_commit = 2
This tells MySQL to flush to the OS cache rather than forcing a physical disk sync every time. I've seen write speeds increase by 10x with this single change.
4. The Hardware Reality: Why Virtualization Matters
You can tune configurations all day, but you cannot tune away bad hardware. In the VPS market, "overselling" is the dirty secret. Many providers pile 50 customers onto a single hard drive array. If your neighbor decides to run a backup or a massive query, your disk I/O wait (iowait) spikes, and your site crawls.
Pro Tip: Runiostat -x 1during peak hours. If%utilis near 100% andawaitis high, your disk subsystem is saturated. No amount of caching will fix this.
This is where architecture counts. At CoolVDS, we don't rely on standard SATA drives for production databases. We utilize enterprise-grade SAS 15k RPM arrays in Hardware RAID 10. RAID 10 gives you the speed striping of RAID 0 with the redundancy of RAID 1.
Furthermore, we use KVM virtualization. Unlike OpenVZ, where resources are often shared too loosely, KVM provides better isolation. Your RAM is your RAM. This stability is crucial for consistent database performance, especially when serving customers in Oslo where latency expectations are under 20ms.
5. Networking and DNS Lookups
Here is a quick win. By default, when a connection hits your MySQL server, it tries to resolve the IP address to a hostname. If your DNS is slow, your login is slow.
Disable it in my.cnf:
[mysqld]
skip-name-resolve
Ensure your user permissions use IP addresses (e.g., 'user'@'192.168.1.1') rather than hostnames.
6. Local Compliance & Reliability
Performance isn't just about speed; it's about availability. Hosting your data outside of Norway introduces latency and potential legal headaches under the Personal Data Act (Personopplysningsloven). Keeping your data on Norwegian soil, protected by the Datatilsynet regulations, builds trust with your local users.
CoolVDS servers are located physically in Oslo, directly connected to NIX (Norwegian Internet Exchange). This ensures that the network hop between your Norwegian users and your database is negligible.
Summary Checklist for your my.cnf
- Switch tables to InnoDB.
- Set innodb_buffer_pool_size to 60% of total RAM.
- Set innodb_flush_log_at_trx_commit to 2 (if suitable).
- Enable skip-name-resolve.
- Ensure your host uses RAID 10 storage.
Don't let a default configuration file determine your business success. Tuning your database is the difference between a server that crashes under a Digg effect and one that handles traffic without breaking a sweat.
Need a platform that keeps up? Deploy a high-performance KVM instance on CoolVDS today and see the difference dedicated RAID 10 resources make for your database.