Stop Letting Default Configurations Kill Your Database

It starts the same way every time. Your application—maybe it's a growing Drupal community or a custom Magento store—runs perfectly on your development box. Then you deploy it. Traffic spikes. Suddenly, your page load times hit 5 seconds, and your logs are screaming Too many connections.

Most developers blame the PHP code. But nine times out of ten, the bottleneck is a default, unoptimized MySQL installation choking on disk I/O.

In the Nordic hosting market, where customers expect near-instant response times, leaving MySQL on default settings is negligence. Here is how we tune the stack for high-performance production environments at CoolVDS, specifically for the hardware realities of 2009.

1. The Storage Engine War: MyISAM vs. InnoDB

If you take nothing else from this post, understand this: MyISAM uses table-level locking.

When a user writes to a MyISAM table, MySQL locks the entire table. No one else can read or write until that operation finishes. On a read-heavy site (like a blog), this is fine. On a write-heavy Web 2.0 application with comments, sessions, and user updates, this creates a disastrous queue.

The Fix: Switch to InnoDB. It supports row-level locking. One user updating their profile doesn't block another user from reading the front page.

ALTER TABLE users ENGINE=InnoDB;

2. The my.cnf Configuration That Actually Matters

Most VPS providers give you a generic template. It’s trash. Open /etc/my.cnf (or /etc/mysql/my.cnf on Debian/Ubuntu) and look at these specific values.

RAM Allocation

MySQL loves RAM. If you are using InnoDB, the innodb_buffer_pool_size is the single most critical setting. It caches data and indexes in memory to avoid hitting the physical disk.

Pro Tip: On a dedicated database server, set this to 70-80% of your total RAM. On a shared web/db VPS, be careful. If you swap, you die.

[mysqld]
# For a 4GB RAM VPS running mostly MySQL
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M

The Query Cache Trap

Don't blindly increase query_cache_size. A large cache adds overhead because MySQL has to prune invalid entries every time a table is updated. Keep it modest.

query_cache_size = 32M
query_cache_limit = 1M

3. The Hardware Reality: Why RAID 10 is Non-Negotiable

You can tune software all day, but you cannot code your way out of slow physics. Hard drives are the slowest component in your server.

Many budget hosts in Europe pile hundreds of VPS containers onto a single SATA drive. This creates "IO Wait"—where your CPU sits idle, doing nothing, just waiting for the disk platter to spin. It ruins performance.

At CoolVDS, we don't play that game. Our architecture relies on 15,000 RPM SAS drives in Hardware RAID 10. We strip the data across multiple disks for speed and mirror it for redundancy.

Why Latency to Oslo Matters

If your primary user base is in Norway, physics is your enemy. Hosting in a US datacenter adds 100-150ms of latency to every packet. For a database-driven site requiring 50 round-trips to render a page, that latency stacks up to seconds of delay.

By keeping your data at the NIX (Norwegian Internet Exchange) via our Oslo nodes, you cut network latency to under 10ms for local users. Furthermore, complying with Personopplysningsloven (Personal Data Act) and Datatilsynet regulations is significantly easier when your data physically resides within Norwegian jurisdiction.

4. DNS Lookups: The Silent Killer

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 immediately.

[mysqld]
skip-name-resolve

This forces MySQL to use IP addresses only for permissions, removing an unnecessary dependency on the network stack.

Final Thoughts: Stability Over Gimmicks

There is a lot of noise right now about "Cloud" computing and distributed hash tables. While interesting, they aren't ready for mission-critical transactional data. The reliability of a well-tuned LAMP stack on solid virtualization technology like Xen or KVM is proven.

Don't let disk I/O bottlenecks destroy your SEO rankings or user experience. If you are tired of fighting with "burstable" RAM limits and overcrowded servers, it is time to get serious.

Need to test your config? Deploy a CoolVDS instance in Oslo. We offer raw performance, root access, and the stability your sysadmin demands.