Stop MySQL from Eating Your CPU: A Survival Guide for 2010

There is nothing worse than waking up at 3:00 AM to a buzzing pager because your server load just hit 50.0. You SSH in, run top, and there it is: mysqld consuming 99% CPU while Apache processes stack up like cars on the Ring 3 during rush hour. Your site isn't just slow; it's dead.

Most VPS providers in the Nordic market will tell you to just "upgrade to a larger plan." They want you to pay for more RAM to mask a bad configuration. I've been managing systems from Oslo to Tromsø for a decade, and I can tell you: raw power won't fix a bad my.cnf.

The War Story: The vBulletin Disaster

Last month, I audited a high-traffic forum hosted on a budget provider. They were running a standard LAMP stack on CentOS 5. Every time they sent a newsletter, the site crashed. The culprit wasn't PHP; it was storage engines.

They were using MyISAM for everything. MyISAM uses table-level locking. This means if one user is writing a post, nobody else can read from that table until the write is finished. On a busy site, these locks queue up, Apache workers wait for MySQL, and your server runs out of RAM. We migrated them to InnoDB, and the load dropped from 20 to 0.5 instantly.

Critical Tuning: The my.cnf Breakdown

If you are running a modern web application in 2010—whether it's WordPress, Drupal, or Magento—you need to be using InnoDB. But the default MySQL 5.0/5.1 installation is tuned for 128MB of RAM and MyISAM. Let's fix that.

Here are the settings you need to change in /etc/my.cnf right now:

1. The Buffer Pool is King

If you use InnoDB, innodb_buffer_pool_size is the single most important variable. It determines how much data and indexes MySQL caches in memory. If this is too low, your database hits the disk for every query. Disk I/O is slow. RAM is fast.

2. Stop Tuning key_buffer

I see so many admins set key_buffer_size = 2G while running InnoDB tables. Stop it. The key buffer is only for MyISAM indexes. If you are fully InnoDB, set this low (e.g., 32M) to save RAM for the system.

3. The Query Cache Trap

It sounds good: query_cache_size. But in high-concurrency environments, the query cache can actually become a bottleneck due to mutex contention. If you have a write-heavy application, try disabling it or keeping it small (under 64M).

The Hardware Reality: Why I/O Matters

You can tune MySQL all day, but if your underlying disk system is slow, your database will crawl. This is where the "noisy neighbor" effect kills performance on cheap shared hosting. If another VPS on the same physical node is churning through a backup, your disk seek times skyrocket. MySQL hates latency.

Pro Tip: Use iostat -x 1 to monitor your disk wait times. If %iowait is consistently above 10%, your disk subsystem is the bottleneck, not your CPU.

The CoolVDS Architecture

This is why we architect CoolVDS differently. We don't oversell our storage backends. We use Xen HVM virtualization, which provides better isolation than container-based OpenVZ setups often found elsewhere. Our storage arrays are built on enterprise-grade RAID 10 SAS with high RPM, and we are currently rolling out SSD caching tiers for our premium nodes.

Furthermore, hosting in Norway isn't just about speed; it's about compliance with the Personal Data Act and keeping the Datatilsynet happy. Our data centers in Oslo ensure your data stays within Norwegian jurisdiction while offering sub-2ms latency to the NIX (Norwegian Internet Exchange).

The Final Configuration

Before you restart MySQL, ensure you have a fallback. But if you're ready to see real speed, apply these changes:

Don't let a default configuration file determine your application's fate. Tune your stack, monitor your I/O, and host on infrastructure that respects the hardware requirements of a database.

Need a test environment to benchmark these settings? Deploy a Xen VPS on CoolVDS today and experience the difference of unthrottled I/O.