Stop Blaming PHP: Why Your MySQL Config is the Real Bottleneck
It happens every day. A developer complains that their Magento store or Drupal site is sluggish. They blame the PHP memory limit, they blame Apache, or they blame the network. But when I log into the shell and run top, I see the CPU isn't the problem. The problem is iowait spiking to 40%.
Your application isn't slow because of code execution; it's slow because your database is gasping for air, waiting for the hard disk to spin.
In 2010, relying on default MySQL configurations is professional negligence. With the recent release of MySQL 5.5, we have better tools than ever, but they are useless if you don't know how to tune them. Here is how we optimize database performance for high-traffic sites targeting the Norwegian market.
The Storage Reality: SAS vs. SSD
Before touching a single configuration file, acknowledge the hardware. If your VPS is running on a shared SAN with 7.2k SATA drives, no amount of tuning will save you. Database performance is bound by IOPS (Input/Output Operations Per Second).
At CoolVDS, we recently transitioned our high-performance tier to Enterprise SSDs. The difference isn't subtle. A standard 15k RPM SAS drive might give you 180 IOPS. An Intel X25-E SSD pushes thousands. If you are serving a read-heavy application to customers in Oslo or Bergen, the latency reduction from SSDs combined with our proximity to NIX (Norwegian Internet Exchange) is the single biggest upgrade you can make.
The Holy Grail: InnoDB Buffer Pool
If you only change one setting in your /etc/my.cnf, make it this one. By default, MySQL often sets the innodb_buffer_pool_size to a pitiful 8MB. This forces InnoDB to constantly read from the disk.
For a dedicated database server, the golden rule is simple: allocate 70-80% of your available RAM to the buffer pool. This ensures that your active dataset stays in memory.
[mysqld]
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 256M
Pro Tip: Do not set this too high if you are running Apache on the same server. If you cause the OS to swap, your performance will tank harder than if you had a small buffer pool.
The Query Cache Trap
There is a misconception that a larger Query Cache is always better. In high-concurrency environments, a massive query cache can actually degrade performance due to lock contention. Every time a table is updated, the cache for that table must be pruned.
If you have a write-heavy application, consider disabling it or keeping it small (under 64MB). Check your hit rate with:
SHOW STATUS LIKE 'Qcache%';
File System choice: Ext3 vs. Ext4 vs. XFS
Most default CentOS 5 installations still use Ext3. However, Ext3 serialization can be a bottleneck during heavy writes. If you have the option (or if you are on the bleeding edge with RHEL 6), formatting your /var/lib/mysql partition as XFS or Ext4 can yield significant gains in concurrency.
At CoolVDS, our standard KVM templates are optimized with Ext4 by default because we know that file system locking is a silent killer of database performance.
Data Sovereignty and Latency
Performance isn't just about IOPS; it's about network latency. If your target audience is in Norway, hosting your database in a datacenter in Texas is nonsensical. You are adding 120ms of round-trip time to every dynamic query.
Furthermore, with the Personal Data Act (Personopplysningsloven) and the strict oversight of Datatilsynet, keeping your customer data within Norwegian borders is not just a technical preference—it is becoming a compliance necessity for serious businesses. We ensure your data stays on local infrastructure, protected by Norwegian law, not buried in a US cloud subject to the Patriot Act.
Summary: The Checklist
| Parameter | Recommendation |
|---|---|
| Storage | Migrate to SSD or RAID-10 SAS immediately. |
| Engine | Move from MyISAM to InnoDB for row-level locking. |
| Buffer Pool | 70% of total RAM (dedicated server). |
| Virtualization | Avoid oversold OpenVZ; use KVM/Xen for guaranteed resources. |
Don't let slow I/O kill your SEO rankings or drive users away. Database tuning requires a foundation of solid hardware. Deploy a test instance on CoolVDS today to see what proper SSD storage does for your query execution times.