Stop Letting Default MySQL Settings Kill Your Web App
I recently watched a client's e-commerce launch turn into a disaster. They had the traffic—thanks to a front-page mention on a major Norwegian tech news site—but their checkout process was crawling. They threw more RAM at the problem. Nothing happened. They blamed the PHP code. It wasn't the code.
It was the database storage engine. They were running a high-concurrency write workload on MyISAM, the default engine in MySQL 5.0 and 5.1. Every time a customer wrote to the orders table, MyISAM locked the entire table. The queue piled up, Apache processes maxed out, and the server choked.
If you are serious about hosting high-traffic sites in Norway or anywhere else, you need to stop treating your database like a black box. Here is how we tune MySQL for real-world loads at CoolVDS.
1. The Engine War: MyISAM vs. InnoDB
In 2009, this is the single most critical decision you make. MyISAM is fast for reads but terrible for concurrent writes due to table-level locking. InnoDB supports row-level locking and transactions (ACID compliance).
Pro Tip: If your application is write-heavy (forums like phpBB, stores like Magento), you MUST migrate to InnoDB. If it is 99% read (like a simple WordPress blog), MyISAM might still offer slightly better raw speed on older hardware, but the gap is closing.
How to Switch
First, check your tables:
SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'your_db';To convert a table (backup first!):
ALTER TABLE users ENGINE = InnoDB;2. Tuning my.cnf for Hardware
Default MySQL configurations are designed to run on a toaster. You have a VPS with dedicated RAM; use it. The two most important variables in /etc/my.cnf depend on your engine choice.
For InnoDB (The CoolVDS Recommendation)
Focus on the innodb_buffer_pool_size. This is where data and indexes are cached. Set this to 60-70% of your total available RAM if the server is a dedicated database node.
[mysqld]
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit = 2 # Riskier for data integrity, but massive speed boostFor MyISAM
Focus on the key_buffer_size. This only caches indexes, not data.
[mysqld]
key_buffer_size = 256M
query_cache_size = 64M # Be careful, too big creates fragmentation overhead3. The Disk I/O Bottleneck
You can tune software all day, but if your underlying storage is slow, your database will hang. This is where the virtualization technology of your host becomes the limiting factor. Many budget hosts oversell their disk I/O, leading to "iowait" spikes that freeze your database.
At CoolVDS, we avoid the "noisy neighbor" problem by using Xen virtualization. Unlike container-based solutions (like OpenVZ), Xen provides better isolation for disk I/O and memory. When you run iostat -x 1 on our servers, the numbers you see are actually yours.
Furthermore, for database heavy-lifters, we utilize enterprise-grade SAS 15k RPM drives in RAID 10. While SSDs are starting to appear in the enterprise market (like the Intel X25-E), they are still cost-prohibitive for mass storage. A solid RAID 10 array provides the redundancy and striping speed necessary to keep MySQL query latency low.
4. Latency and Norwegian Law
Performance isn't just about disk speed; it's about network latency. If your customers are in Oslo, hosting your database in a datacenter in Texas adds ~140ms of round-trip time to every dynamic page load. That adds up.
Hosting locally in Norway also simplifies compliance with the Personal Data Act (Personopplysningsloven). Keeping data within the EEA/Norway jurisdiction satisfies the Data Inspectorate (Datatilsynet) requirements, ensuring you aren't caught in legal limbo regarding data export.
Summary
- Stop using default configs.
- Move write-heavy tables to InnoDB.
- Host on hardware that guarantees I/O performance (Xen over OpenVZ).
- Keep your data close to your users to minimize network lag.
Don't let a default setting be the reason your site crashes during a traffic spike. SSH into your server and check your my.cnf today.