Stop Killing Your Database: MySQL 5.1 Performance Tuning for High-Traffic Norwegian Sites

Let's be honest. If you just ran yum install mysql-server on your CentOS 5 box and walked away, your database is ticking time bomb. The default my.cnf shipping with most distributions today is tuned for a server with 64MB of RAM, not the modern 4GB or 8GB nodes we are provisioning in 2009.

I recently audited a Magento setup for a client in Oslo that was crashing every time traffic spiked during the lunch rush. They blamed the PHP code. They blamed the network. But a simple look at top and mysqladmin extended-status showed the truth: they were running MyISAM tables on a high-write application, locking the entire table every time a customer added an item to the cart.

Latency matters. Whether your users are in Bergen or Trondheim, milliseconds add up. If your database is thrashing the disk, it doesn't matter that CoolVDS offers low-latency peering at NIX (Norwegian Internet Exchange). Your server is the bottleneck.

1. The Engine War: MyISAM vs. InnoDB

By default, MySQL 5.1 still defaults to MyISAM. For a read-only blog, that’s fine. For anything interactive, it is suicide.

MyISAM uses table-level locking. If one user writes to the sessions table, everyone else waits. InnoDB uses row-level locking. Multiple users can write to the same table simultaneously, provided they aren't touching the same row.

The Fix: Convert your tables to InnoDB. Then, enable the InnoDB Plugin if you are on MySQL 5.1 for better performance, or at least tune the built-in engine.

2. The Holy Grail: innodb_buffer_pool_size

If you only change one setting in /etc/my.cnf, make it this one. This setting dictates how much RAM MySQL allocates to cache data and indexes for InnoDB tables. The default is often a pitiful 8MB.

On a dedicated database server, you want this set to roughly 70-80% of your total physical RAM. On a shared web/DB server (common in VPS environments), be more conservative—maybe 50%—to leave room for Apache/PHP processes.

Here is a configuration snippet for a CoolVDS instance with 4GB RAM:

[mysqld]
# storage engine
default-storage-engine = InnoDB

# memory allocation
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1

# thread handling
thread_cache_size = 8
query_cache_size = 64M

Pro Tip: Watch out for innodb_flush_log_at_trx_commit. Setting it to 1 is the safest (ACID compliant) but requires a disk flush on every transaction. If you can tolerate losing 1 second of data during a power failure, set it to 2. Write performance can increase significantly.

3. Disk I/O: The Silent Killer

You can tune memory all day, but eventually, MySQL has to hit the disk. This is where cheap hosting goes to die. Many providers oversell their storage backend, putting hundreds of VPS containers on a single SATA drive.

When your database tries to flush the buffer pool, I/O Wait spikes, and your site hangs. You can verify this by running iostat -x 1. If %util is consistently near 100%, your disks are too slow.

At CoolVDS, we don't play games with "burst" resources. We use RAID 10 SAS arrays (15k RPM). This provides the redundancy of mirroring with the striping speed needed for database workloads. While Solid State Drives (SSDs) like the Intel X25-E are appearing in the enterprise market, they are still cost-prohibitive for mass storage. High-speed SAS in RAID 10 remains the gold standard for reliable database throughput in 2009.

4. Query Cache: A Double-Edged Sword

The Query Cache looks great on paper: cache the result of a `SELECT` statement. However, in high-concurrency environments, the query cache lock can actually become a bottleneck. Every time a table is modified, all cache entries for that table are flushed.

If you have a write-heavy application, try disabling it to see if throughput improves:

query_cache_type = 0
query_cache_size = 0

5. Compliance and The "Datatilsynet" Factor

Performance isn't just about speed; it's about reliability and compliance. Under the Norwegian Personal Data Act (Personopplysningsloven), you have a responsibility to secure user data. Frequent database crashes due to memory exhaustion can lead to corruption and data loss, putting you in the crosshairs of Datatilsynet.

Stability is a feature. By properly tuning your MySQL configuration and hosting on a platform that offers genuine resource isolation via Xen virtualization—like CoolVDS—you ensure that a neighbor's heavy script doesn't steal your CPU cycles when you need them most.

Final Thoughts

Stop accepting default configs. SSH into your server, back up your my.cnf, and start tuning for your actual workload. If your current host can't handle the I/O requirements of a tuned InnoDB workload, it's time to move.

Don't let slow disk I/O kill your search rankings. Deploy a high-performance Xen VPS instance on CoolVDS today and see what proper RAID 10 storage does for your database latency.