MySQL Performance Tuning: Stop Your Database From Choking on Traffic
It’s 3:00 AM. Your Nagios pager is screaming. Your client's Magento store just hit the front page of Digg, and now the server load is 45.0. You SSH in, run top, and see mysqld eating 140% CPU. If this sounds like your Tuesday night, you have a configuration problem.
Most default Linux distributions ship with a my.cnf optimized for a toaster, not a high-traffic web server. In 2009, with web applications becoming increasingly database-heavy (looking at you, Drupal 6), relying on defaults is professional suicide. Let's fix that.
The Bottleneck is Almost Always I/O
Before we touch a single configuration flag, we need to talk about hardware. CPU cycles are cheap; disk I/O is expensive. When MySQL can't fit its working set in RAM, it hits the disk. On standard 7.2k SATA drives, this means your application grinds to a halt waiting for the platters to spin.
This is why we architect CoolVDS differently. We don't oversubscribe storage. We use enterprise-grade 15k RPM SAS drives in RAID-10. This gives you the redundancy of mirroring with the striping speed required for heavy database writes. While some providers are experimenting with early SSDs, reliable SAS arrays remain the king of sustained throughput for serious production environments.
Pro Tip: Check your I/O wait. Runiostat -x 1. If your%utilis constantly near 100%, no amount of caching will save you. You need faster disks or more RAM.
The Storage Engine War: MyISAM vs. InnoDB
In MySQL 5.1, the default engine is still MyISAM. For read-heavy sites (like a simple WordPress blog), MyISAM is fast and simple. But it has a fatal flaw: table-level locking. If one user writes to a table, everyone else waits until that write is finished.
For modern e-commerce or interactive apps, you should be migrating to InnoDB. It supports row-level locking and transactions (ACID compliance). The trade-off? It requires much more RAM to perform well.
Essential my.cnf Configurations
Open /etc/my.cnf (or /etc/mysql/my.cnf on Debian/Ubuntu) and look for these critical settings. Note: Back up your file first.
1. innodb_buffer_pool_size
If you use InnoDB, this is the single most important setting. It determines how much data and indexes are cached in memory. A good rule of thumb for a dedicated database server is 50-70% of total RAM.
[mysqld]
# For a 4GB VPS running only MySQL
innodb_buffer_pool_size = 2G
2. query_cache_size
The query cache can be a double-edged sword. It stores the result of SELECT queries. However, if a table changes, the cache for that table is flushed. For high-concurrency write environments, a large query cache can actually cause locking contention.
# Start conservative. Too big is bad.
query_cache_size = 32M
query_cache_limit = 1M
3. max_connections
Don't set this to 5000 just because you can. Each connection consumes RAM buffers. If you run out of RAM, the kernel OOM killer will terminate MySQL. Calculate your needs based on your web server's MaxClients.
The "CoolVDS" Factor: Virtualization Matters
Not all VPS hosting is created equal. Many budget hosts use container-based virtualization (like Virtuozzo) where the kernel and resource limits are shared. If your neighbor gets attacked, your database suffers.
At CoolVDS, we use Xen HVM. This provides true hardware virtualization. Your RAM is dedicated to you, not burstable shared memory that disappears when you need it most. This stability is crucial for database consistency.
Latency and Legalities
If your target audience is in Norway, hosting in Germany or the US adds 30-100ms of latency to every database call if your web server is remote. By placing your VPS Norway based instance in our Oslo datacenter, you connect directly to the NIX (Norwegian Internet Exchange).
Furthermore, adhering to the Personal Data Act (Personopplysningsloven) is easier when your data physically resides within Norwegian borders, satisfying the requirements of Datatilsynet without complex legal workarounds.
Summary: The Checklist
| Parameter | Recommendation |
|---|---|
| Storage Engine | Use InnoDB for apps; MyISAM for simple logs/blogs. |
| RAM Allocation | Give InnoDB 50-70% of RAM on dedicated DB servers. |
| Hardware | RAID-10 SAS is mandatory for high I/O. |
| Virtualization | Xen (avoid OpenVZ for heavy DBs). |
Performance tuning is an iterative process. Change one variable, benchmark, and repeat. But remember, software tuning can't fix bad hardware. If you are tired of waiting on I/O wait, it's time to upgrade.
Ready for raw performance? Deploy a Xen-based CoolVDS instance in Oslo today and give your database the I/O headroom it deserves.