Stop Letting Default MySQL Configs Kill Your Response Times

It is 2010, and yet I still see high-traffic Norwegian web portals running default MySQL installations on CentOS 5. If you are wondering why your site slows to a crawl every time you get a traffic spike from VG or Digg, the answer is likely sitting in your /etc/my.cnf file. While latency to the NIX (Norwegian Internet Exchange) might be under 2ms, your database is likely adding 500ms of locking delays.

I have spent the last week debugging a Magento rollout for a client in Oslo. They threw hardware at the problem—adding more RAM, upgrading CPUs—but the load average kept climbing. The culprit? Table-level locking on the MyISAM storage engine and disk I/O bottlenecks on traditional SAS drives. Here is how we fixed it, and how you can tune your stack to survive real production loads.

The Storage Engine War: MyISAM vs. InnoDB

By default, MySQL 5.1 still defaults to MyISAM. For a read-heavy blog, MyISAM is acceptable. For an e-commerce site or a social platform where users are writing data (orders, comments, sessions), MyISAM is a disaster. It uses table-level locking. If one user writes to the orders table, every other user trying to read from that table must wait.

The solution is InnoDB. It supports row-level locking and transactions (ACID compliance). If you are hosting on CoolVDS, we recommend compiling the InnoDB Plugin rather than the built-in version for better multicore scaling.

Migration Strategy

Don't just flip the switch. You need to verify your table structure first.

ALTER TABLE catalog_product_entity ENGINE=InnoDB;

Once you migrate, you must tune the memory allocation. The default key_buffer_size is useless for InnoDB. You need to focus on the Buffer Pool.

Crucial Configuration: The my.cnf Blueprint

Most VPS providers give you a generic template. Throw it away. Here is the configuration we use for high-performance nodes on CoolVDS, assuming a 4GB RAM slice dedicated mostly to the database:

[mysqld]
# Basic Settings
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
skip-external-locking
skip-name-resolve

# InnoDB Optimization (The Meat)
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so
innodb_file_per_table = 1
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 = 2
innodb_thread_concurrency = 8

# MyISAM (Keep small if mostly InnoDB)
key_buffer_size = 32M
max_allowed_packet = 16M
sort_buffer_size = 1M
read_buffer_size = 1M

# Query Cache (Be careful, can cause mutex contention)
query_cache_size = 64M
query_cache_limit = 1M

Pro Tip: Setting innodb_flush_log_at_trx_commit = 2 allows the transaction to commit to the OS cache rather than forcing a disk sync every single time. You risk losing 1 second of data in a power outage, but for most web apps, the performance gain is massive. If you are compliant with the strict Norwegian Personal Data Act (Personopplysningsloven), ensure your datacenter has redundant power (UPS/Diesel) to mitigate this risk.

The I/O Bottleneck: Why SSDs are the Future

You can tune software all day, but physics is physics. A standard 15k RPM SAS drive can handle roughly 180-200 IOPS (Input/Output Operations Per Second). If your database is doing random reads/writes, the drive head is physically moving, creating latency.

This is where the virtualization platform matters. Many "budget" VPS providers in Europe use OpenVZ containers crammed onto servers with SATA drives. The "noisy neighbor" effect is real; if another customer runs a backup script, your database stalls.

At CoolVDS, we are early adopters of Solid State Drives (SSDs) like the Intel X25-E series. SSDs have no moving parts and can handle thousands of IOPS. In our benchmarks, a MySQL import that takes 45 minutes on a RAID 10 SAS array completes in under 6 minutes on our SSD tier. For database hosting, raw seek time is the only metric that truly counts.

Linux Kernel Tuning for DB Servers

MySQL does not live in a vacuum. The Linux kernel (CentOS 5.5 usually ships with 2.6.18) needs guidance. By default, Linux is too eager to swap memory to disk. When MySQL memory gets swapped, performance falls off a cliff.

Add this to /etc/sysctl.conf:

# Reduce swappiness to keep MySQL in RAM
vm.swappiness = 0

# Increase backlog for high connection rates
net.core.somaxconn = 1024
net.ipv4.tcp_max_syn_backlog = 2048

Apply with sysctl -p. This forces the kernel to utilize physical RAM to the absolute limit before touching the swap partition.

Monitoring and Verification

Do not guess. Use tools. top is fine for CPU, but for disk I/O, use iostat. Install the sysstat package:

yum install sysstat
iostat -x 2

Watch the %util column. If it is consistently hitting 90-100%, your disk subsystem is saturated. No amount of query caching will fix that—you need faster storage or a better host.

Conclusion

Performance is not just about raw CPU power; it is about eliminating bottlenecks. In 2010, the biggest bottlenecks are MyISAM locking and mechanical disk latency. By switching to InnoDB and hosting on modern SSD infrastructure, you can handle the load that crashes your competitors.

Data privacy laws in Norway are strict. Datatilsynet expects you to secure your user data. High availability and prevention of crash-induced data corruption are part of that security posture.

Is your database ready for the holiday rush? Stop fighting with slow SAS drives. Deploy a test instance on CoolVDS today and experience the difference of SSD-backed Xen virtualization. View our High-Performance VPS Plans.