MySQL 5.5 Performance Tuning: Surviving High Load on Linux VPS

It is September 2012. If you are still running a high-traffic web application on a default MySQL installation, you are practically asking for downtime. I have spent the last week debugging a Magento deployment for a client in Oslo that slowed to a crawl every time traffic spiked above 50 concurrent users. The server wasn't out of RAM; the configuration was just stuck in 2005.

Most hosting providers hand you a VPS with a standard CentOS 6 image, and the /etc/my.cnf file is optimized for a server with 512MB of RAM, not the 8GB or 16GB rig you are paying for. In the Nordic market, where users expect instant responsiveness, latency kills conversions.

This guide cuts through the theoretical fluff. We aren't looking at synthetic benchmarks. We are looking at what actually keeps a database alive under heavy read/write pressure. We will cover the shift from MyISAM to InnoDB, memory allocation, and why the underlying storage technology (specifically SSDs) is the single biggest factor in database performance today.

1. The Hardware Reality: Spindles vs. SSD

Before we touch a single config file, we need to address the physical layer. In traditional hosting, your VPS lives on a SAN backed by 15k RPM SAS drives. That was fine five years ago. Today, with modern CMS platforms like Drupal 7 and Magento causing massive disk I/O, spinning rust is a bottleneck.

If your iostat output looks like this, you have a problem:

$ iostat -x 1 
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           5.20    0.00    2.10   45.30    0.00   47.40

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
vda               0.00    12.00   85.00   45.00  2048.00  1500.00    27.29     5.20   42.00   7.50  98.50

See that %iowait at 45.30? That means your CPU is sitting idle, twiddling its thumbs, waiting for the hard drive to write data. That 98.50% utilization on vda is the sound of your database dying.

This is why at CoolVDS, we have aggressively moved to solid-state storage. We use high-performance SSD arrays in RAID 10. The random I/O performance of an SSD is magnitudes higher than mechanical drives. If you are tuning MySQL on a slow disk, you are just painting a crumbling wall. Ensure your VPS Norway provider is actually giving you the IOPS you need.

2. The Storage Engine: InnoDB is Mandatory

If you are still using MyISAM for your production tables in 2012, stop. MyISAM uses table-level locking. If one user writes to the users table, everyone else trying to read from that table has to wait. InnoDB uses row-level locking, meaning multiple users can read and write to the same table simultaneously without blocking each other.

MySQL 5.5 made InnoDB the default engine, but many legacy applications still create tables as MyISAM. Check your tables:

SELECT table_name, engine 
FROM information_schema.tables 
WHERE table_schema = 'your_database' 
AND engine = 'MyISAM';

Convert them using ALTER TABLE table_name ENGINE=InnoDB;. Be warned: full-text search support in InnoDB is still limited compared to MyISAM in 5.5, so you might need an external search index like Sphinx or Solr if you rely heavily on that feature.

3. The Golden Config: my.cnf

The default settings in MySQL are terribly conservative. Open /etc/my.cnf (or /etc/mysql/my.cnf on Debian/Ubuntu 12.04) and look at these specific directives. NOTE: Always backup your config before changing it.

innodb_buffer_pool_size

This is the single most critical setting. It determines how much data and indexes MySQL caches in RAM. If you fit your "working set" in RAM, you avoid hitting the disk.

Pro Tip: On a dedicated database server, set this to 70-80% of your total RAM. If you have a CoolVDS instance with 8GB RAM, set this to 6G. If you run Apache/PHP on the same server, dial it back to 50% to prevent the OOM killer from murdering mysqld.

innodb_flush_log_at_trx_commit

This controls ACID compliance vs. Speed.

• Value 1 (Default): Safest. Flushes to disk after every transaction. Slowest.
• Value 2: Flushes to OS cache every transaction, syncs to disk once per second. If the server crashes, you might lose 1 second of data.
• Value 0: Fastest. Risky.

For most web apps, setting this to 2 gives a massive performance boost (often 10x on write-heavy loads) with acceptable risk.

innodb_file_per_table

In older versions, InnoDB stored all data in one massive file (ibdata1). This file never shrinks. In MySQL 5.5, enable innodb_file_per_table=1. This creates a separate .ibd file for each table, making it much easier to reclaim space later.

Here is a battle-tested configuration snippet for a 4GB VPS:

[mysqld]
# Basic Settings
user = mysql
default-storage-engine = InnoDB

# Connections
max_connections = 300
wait_timeout = 60

# Buffer Pool (Assuming 4GB RAM VPS)
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2

# Logging & ACID
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# File Management
innodb_file_per_table = 1
open_files_limit = 65535

# Query Cache (Use with caution in high-write envs)
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 64M

4. System Level Tuning

MySQL doesn't live in a vacuum. The Linux kernel needs tuning too. Specifically, Swappiness.

By default, Linux (vm.swappiness = 60) loves to swap out application memory to disk to keep the file system cache large. For a database, this is disastrous. You want MySQL to stay in RAM.

# Check current value
cat /proc/sys/vm/swappiness

# Set to 0 (or 1) to avoid swapping unless absolutely necessary
sysctl -w vm.swappiness=0

# Make it permanent in /etc/sysctl.conf
vm.swappiness = 0

5. The Norwegian Context: Latency and Jurisdiction

Technical performance is one thing; network performance is another. If your target audience is in Norway, hosting in a datacenter in Ashburn, Virginia, or even Amsterdam, adds unavoidable latency. Physics is physics.

A ping from Oslo to a US East Coast server is roughly 90-110ms. A ping to a local server peered at NIX (Norwegian Internet Exchange) is often under 5ms. For a database-driven application making multiple round-trips per page load, that latency compounds.

Furthermore, consider the legal landscape. Under the Norwegian Personal Data Act (Personopplysningsloven) and the EU Data Protection Directive, you have strict obligations regarding where your user data lives. Keeping data on servers physically located in Norway simplifies compliance with the Data Inspectorate (Datatilsynet) and avoids the legal grey areas of US-based hosting.

Conclusion

Optimizing MySQL 5.5 is a mix of art and science. It requires balancing RAM allocation, disk I/O, and data safety. But software tuning can only take you so far. If your underlying storage subsystem is slow, no amount of my.cnf tweaking will save you.

This is why serious system administrators choose CoolVDS. We provide KVM-based virtualization with dedicated resources and pure SSD storage. No noisy neighbors, no stolen CPU cycles, just raw performance.

Is your database the bottleneck? Don't guess. Deploy a test instance on CoolVDS today and benchmark it against your current provider. You will feel the difference.