Stop Letting Default Configs Murder Your Throughput

I looked at a client's my.cnf file yesterday. It was for a high-traffic e-commerce site targeting the Scandinavian market. They were wondering why their checkout page timed out during the lunch rush. The server had 16GB of RAM, yet MySQL was configured to use less than 512MB. It was running on the default settings shipped with RHEL 5. This is negligence. If you are running a serious web application in 2010, relying on package maintainer defaults is a death sentence for your performance.

The bottleneck is rarely the CPU; it is almost always Disk I/O. In the Nordic hosting market, where latency to the NIX (Norwegian Internet Exchange) in Oslo is measured in milliseconds, a sluggish database read can negate all your network advantages. Here is how we tune the stack for raw speed, assuming you are running a modern Linux kernel (2.6.x) and MySQL 5.1 or the new 5.5.

1. The Engine War: MyISAM vs. InnoDB

Many legacy tutorials still suggest MyISAM because it is "faster for reads." Stop listening to them. MyISAM uses table-level locking. If one user writes to the sessions table, every other user trying to read from it waits. This creates a massive lock queue on high-traffic sites.

InnoDB uses row-level locking. It is the only choice for transactional applications. However, the default InnoDB settings are conservative. You need to enable the InnoDB Plugin in MySQL 5.1 to get the performance benefits that come standard in the upcoming 5.5 release.

Crucial Configuration: File Per Table

By default, InnoDB stores all data in one massive system tablespace (ibdata1). If you delete data, that file never shrinks. It just fragments. Fix this immediately in your [mysqld] section:

[mysqld]
innodb_file_per_table = 1

2. Memory: Feed the Buffer Pool

The single most important variable for InnoDB is innodb_buffer_pool_size. This is where data and indexes are cached. If your data fits in RAM, your disk I/O drops to near zero for reads.

Pro Tip: On a dedicated database server, set this to 70-80% of your total physical RAM. Do not set it higher, or you risk the OS swapping out the database process, which is catastrophic.

For a CoolVDS instance with 8GB RAM, your config should look like this:

# Optimize for 8GB RAM System
innodb_buffer_pool_size = 6G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2

Note the innodb_flush_log_at_trx_commit = 2. The default is 1, which flushes to disk after every transaction. Setting it to 2 writes to the OS cache and flushes to disk once per second. You risk losing one second of data in a power outage, but the write performance gain is often 10x or more. Given the stability of Norwegian power grids and our datacenter UPS systems, this is a calculated risk worth taking.

3. The Disk I/O Reality Check

You can tune software all day, but physics is physics. Traditional 7.2k RPM SATA drives cannot keep up with thousands of random I/O operations per second (IOPS). Even 15k SAS drives struggle under heavy random write loads.

This is where hardware selection becomes your architecture's foundation. At CoolVDS, we have started rolling out Enterprise SSD storage arrays. Unlike standard spinning platters, Solid State Drives have near-zero seek time. While expensive, the IOPS return is astronomical compared to mechanical disks.

4. Stop DNS Lookups

By default, MySQL tries to resolve the hostname of every incoming connection. If your DNS resolver is slow or times out, your database hangs. Disable this feature. You should be managing access via IP addresses or local sockets anyway.

skip-name-resolve

5. The Query Cache Trap

The query_cache sounds great: cache the result of a SELECT statement. However, in high-concurrency write environments, the cache lock becomes a bottleneck. Every time a table is written to, the cache for that table is invalidated. On a busy site, the overhead of locking and invalidating the cache often outweighs the benefits.

If you have a read-heavy site (like a blog), keep it low (e.g., 64M). If you have a write-heavy app, disable it entirely:

query_cache_size = 0
query_cache_type = 0

6. OS Level Tuning: Swappiness

Linux loves to swap. It will swap out your application memory to disk to make room for file caching. For a database, this is terrible behavior. We want the database in RAM, always.

Check your swappiness:

cat /proc/sys/vm/swappiness
60

60 is too high for a DB server. Add this to /etc/sysctl.conf to tell the kernel to swap only when absolutely necessary:

vm.swappiness = 0

Run sysctl -p to apply. This ensures your innodb_buffer_pool stays in physical memory.

Why Infrastructure Matters

You can apply all these configs, but if your host is overselling their nodes, you are fighting a losing battle. Many providers cram hundreds of OpenVZ containers onto a single server. If one neighbor spikes their I/O, your database crawls. This is the "noisy neighbor" effect.

We built CoolVDS on KVM (Kernel-based Virtual Machine). This offers true hardware virtualization and better isolation. Combined with our low latency network connected directly to NIX in Oslo and strict adherence to the Norwegian Data Protection Act (Personopplysningsloven), we provide the stability serious developers need.

Don't let I/O wait times kill your user experience. If your iowait is consistently above 10%, your hardware is failing you.

Ready to see what proper isolation and SSD speed feels like? Spin up a KVM instance on CoolVDS today and benchmark the difference.