It happens every time. You get featured on a major news site, or maybe you hit the front page of Digg, and suddenly your server load spikes to 50. Your CPU isn't even maxed out, yet the site is crawling. The culprit? It’s almost always disk I/O and database locking.
I’ve spent the last week debugging a Magento deployment for a client in Oslo. They were running a standard LAMP stack on a budget VPS. Every time a customer searched the catalog, the entire table locked up. We fixed it, but not by throwing more RAM at it blindly.
If you are serious about high-performance web applications in 2010, you need to stop treating your database like a black box. Here is how we tune MySQL 5.1 for raw speed and stability.
The MyISAM Trap
By default, many MySQL 5.1 installations still default to the MyISAM storage engine. For a read-only blog, MyISAM is fine. For anything interactive, it is a disaster.
MyISAM uses table-level locking. If one user writes to the users table, everyone else trying to read from that table has to wait. On a busy site, these queues pile up, leading to the dreaded "Error establishing a database connection."
The Fix: Switch to InnoDB. Specifically, use the InnoDB Plugin if you are on MySQL 5.1, as it offers better performance than the built-in version. InnoDB uses row-level locking. User A can update their profile while User B reads the same table without blocking each other.
Optimizing my.cnf for Dedicated Resources
Most default configurations are set for servers with 64MB of RAM. If you are paying for a CoolVDS instance with 2GB or 4GB of RAM, you are wasting resources. Open your /etc/my.cnf (or /etc/mysql/my.cnf on Debian/Ubuntu 10.04) and look at these settings.
1. The Buffer Pool (The Most Important Setting)
If you use InnoDB, this setting determines how much data and indices are cached in memory. Disk access is slow. RAM is fast. You want as much data in RAM as possible.
innodb_buffer_pool_size = 1G
Rule of Thumb: On a dedicated database server, set this to 70-80% of your total physical RAM. If you are on a shared web/DB server, dial it back to 50% so Apache/PHP doesn't starve.
2. The Flush Log Strategy
ACID compliance is great, but do you strictly need it for every single click? The default setting forces a disk flush on every transaction commit. This kills I/O throughput.
innodb_flush_log_at_trx_commit = 2
Setting this to 2 means the log is written to the file cache immediately but flushed to disk once per second. If the server crashes, you might lose 1 second of data. For a payment gateway, keep it at 1. For a forum or content site, 2 will give you a massive speed boost.
3. Stop DNS Lookups
Why does your database check the DNS reverse record of every incoming connection? It adds unnecessary latency, especially if your DNS resolvers are slow.
skip-name-resolve
Add this to your config. Just remember to grant permissions using IP addresses (e.g., 'user'@'192.168.1.5') instead of hostnames.
Pro Tip: Don't trust the Query Cache blindly. In MySQL 5.1, the Query Cache can actually become a bottleneck due to global locking on updates. If you have a write-heavy application, try setting query_cache_type = 0 and see if throughput improves.
Hardware Matters: The I/O Bottleneck
You can tune software all day, but you cannot code your way out of slow physics. A traditional 7200 RPM SATA drive can handle about 75-100 random Input/Output Operations Per Second (IOPS). A complex Magento homepage might generate 50 SQL queries. You do the math.
This is why we architect CoolVDS differently. We don't use oversold OpenVZ containers where "burst RAM" is a marketing lie. We use Xen HVM virtualization.
Why Xen? Because it provides better isolation. A noisy neighbor on the same physical host cannot steal your memory pages. Furthermore, we utilize enterprise 15k RPM SAS drives in RAID 10 arrays. This offers redundancy and significantly higher read/write speeds than standard SATA hosting.
For those pushing the absolute limits, we are beginning to roll out Solid State Drive (SSD) storage tiers. The Intel X25-E series drives we are testing are showing IOPS in the thousands, not hundreds. If your database is I/O bound, SSDs are the future.
Local Latency and Compliance
Speed isn't just about disk reads; it's about the network. If your target audience is in Norway, hosting in Texas makes no sense. The latency from Oslo to a US datacenter is roughly 120-150ms. The latency to our facility in Oslo via NIX (Norwegian Internet Exchange) is often under 5ms.
Beyond speed, there is the Personopplysningsloven (Personal Data Act). Keeping your customer data within Norwegian borders simplifies compliance with Datatilsynet regulations. Don't risk legal headaches by sending sensitive customer data to unverified safe harbors.
The Verdict
Performance is a stack. It starts with hardware (RAID 10 SAS or SSD), moves to the kernel (Xen virtualization), and finishes with configuration (InnoDB tuning). If you are running a default MySQL install on a cheap, oversold VPS, you are leaving money on the table.
Stop letting I/O wait times kill your user experience. SSH into your server, check your innodb_buffer_pool_size, and if the hardware can't keep up, it's time to move.
Ready for real throughput? Deploy a Xen-based Linux instance on CoolVDS today and experience the difference of local Norwegian peering.