MySQL 5.5 Performance Tuning: Optimizing InnoDB for High-Traffic Norwegian Workloads

It is 3:00 AM. Your monitoring system, likely Nagios or Zabbix, is screaming. The load average on your database server just hit 25.0. Your web application—targeting users in Oslo and Bergen—is timing out. You check top and see MySQL consuming 140% CPU. If this sounds familiar, your database configuration is likely still stuck in 2008.

In the last year, the hosting landscape has shifted. With the release of MySQL 5.5 and the increasing affordability of Enterprise SSDs, the bottleneck is moving from raw disk I/O to memory management and CPU efficiency. Yet, I still see production servers running default my.cnf files that look like they were generated for a Pentium III.

This guide is for systems administrators who are tired of fighting fires. We are going to look at the critical parameters for MySQL 5.5 running on CentOS 6 or Debian Squeeze, specifically focusing on the InnoDB storage engine.

The Hardware Reality: Spindles vs. Silicon

Before we touch a single configuration file, we need to address the physical layer. If you are running a high-transaction database (OLTP) on standard 7.2k RPM SATA drives, you are fighting a losing battle. The random I/O requirements of a busy e-commerce site or a CMS like Drupal will crush mechanical disks.

Pro Tip: RAID 10 with SAS drives was the gold standard, but in 2012, Solid State Drives (SSDs) are rewriting the rules. A single high-performance SSD can deliver 20,000+ IOPS compared to the ~180 IOPS of a mechanical drive. If your hosting provider doesn't offer SSDs, move.

At CoolVDS, we made a controversial decision to phase out mechanical storage for our primary VPS nodes. Why? Because no amount of caching can fix a disk subsystem that is physically too slow to write transaction logs. We use KVM virtualization to ensure that your I/O isn't stolen by a neighbor, unlike OpenVZ containers where disk contention is common.

The Engine: MyISAM is Dead, Long Live InnoDB

With MySQL 5.5, InnoDB became the default storage engine. If you are still explicitly using MyISAM for your tables, stop. MyISAM uses table-level locking. If one user writes to the orders table, every other user reading from it has to wait. InnoDB uses row-level locking and supports ACID transactions.

Here is how to verify your tables are using the right engine:

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

If that returns results, schedule a maintenance window and convert them:

ALTER TABLE table_name ENGINE=InnoDB;

Critical my.cnf Optimizations

The default MySQL configuration is designed to run on a machine with 512MB of RAM. If you are running a server with 8GB, 16GB, or 32GB of RAM, you are wasting resources. Open your configuration file (usually /etc/my.cnf or /etc/mysql/my.cnf) and look at these values.

1. innodb_buffer_pool_size

This is the single most important setting. It determines how much memory MySQL uses to cache data and indexes. If your data fits in memory, you avoid disk I/O entirely.

Recommendation: Set this to 60-70% of your total system RAM on a dedicated database server.

# For a server with 16GB RAM
[mysqld]
innodb_buffer_pool_size = 10G

2. innodb_flush_log_at_trx_commit

This controls durability.

• Value 1 (Default): Flushes to disk after every transaction. Safest, but slowest.
• Value 2: Flushes to OS cache every transaction, syncs to disk once per second. Faster, but you might lose 1 second of data in a power outage.

If you are running a payment gateway, stick to 1. If you are running a forum or a blog, setting this to 2 can improve write throughput by 5x to 10x.

innodb_flush_log_at_trx_commit = 2

3. innodb_file_per_table

By default in older versions (and often still in 5.5 setups depending on the distro), InnoDB stores all data in one massive file: ibdata1. This file never shrinks. If you delete 100GB of data, the file stays the same size.

Enable innodb_file_per_table so that each table gets its own .ibd file. When you drop a table, you actually reclaim the disk space. Note: You must enable this before creating tables, or you will have to dump and reload your database.

innodb_file_per_table = 1

Benchmarking the Changes

Don't guess. Measure. Use sysbench to test your OLTP performance before and after changes.

# Prepare the test
sysbench --test=oltp --mysql-table-engine=innodb --mysql-db-test=test --oltp-table-size=1000000 --mysql-user=root --mysql-password=yourpassword prepare

# Run the benchmark
sysbench --test=oltp --mysql-table-engine=innodb --mysql-db-test=test --oltp-table-size=1000000 --mysql-user=root --mysql-password=yourpassword --max-time=60 --max-requests=0 --num-threads=8 run

Look for the "transactions per second" (TPS) metric. On a standard VPS with spinning disks, you might see 50-100 TPS. On a CoolVDS SSD instance, you should expect significantly higher numbers due to the superior random write speeds.

The "Swappiness" Killer

Linux loves to swap. On a database server, swapping is death. If MySQL memory gets swapped to disk, your performance falls off a cliff. Adjust the kernel's tendency to swap by modifying /etc/sysctl.conf:

vm.swappiness = 0

Run sysctl -p to apply. This tells the kernel to avoid swapping at all costs unless absolutely necessary.

Compliance and Reliability in Norway

Performance isn't just about speed; it's about availability and trust. Hosting your database outside of Norway introduces latency and legal ambiguity. With the strict requirements of the Personopplysningsloven (Personal Data Act) and the watchful eye of Datatilsynet, keeping data within Norwegian borders is often a requirement for enterprise clients.

Furthermore, network latency matters. A round trip from Oslo to a server in Texas takes ~140ms. From Oslo to a server in Frankfurt, ~30ms. From Oslo to our facility connected to NIX (Norwegian Internet Exchange), it is often under 5ms. In the world of high-frequency trading or real-time bidding, those milliseconds translate directly to revenue.

Summary of Settings

Optimizing MySQL is an iterative process. There is no "magic switch," but the combination of SSD storage, KVM isolation, and proper InnoDB tuning comes close.

If you are tired of debugging I/O wait times and want a platform that respects the laws of physics as much as it respects Norwegian data laws, it is time to upgrade. Deploy a CentOS 6 SSD instance on CoolVDS today and see your I/O wait drop to zero.