MySQL 5.5 Performance Tuning: Surviving the Slashdot Effect

It starts with a creeping sluggishness. Then, Top shows your load average spiking past 10. Finally, connections start timing out. If you are running a high-traffic e-commerce site targeting the Norwegian market, you know that a 500ms delay isn't just a technical glitch—it is lost revenue. In 2012, users expect instant responses, and if your MySQL configuration is still stuck in the defaults from 2008, you are leaving money on the table.

Most VPS providers in Europe will sell you a slice of a server with decent RAM but strangle you on I/O. They put you on crowded SATA RAID arrays where your database has to fight for disk access. I have spent the last week debugging a Magento cluster that was bringing a quad-core server to its knees, not because of CPU, but because iowait was hitting 40%. The culprit? Default MyISAM tables and a storage backend that couldn't keep up with random writes.

The Great Debate: MyISAM vs. InnoDB in 2012

For years, the rule of thumb was simple: MyISAM for reads, InnoDB for transactions. That advice is rapidly becoming obsolete with the release of MySQL 5.5. Since Oracle acquired Sun, InnoDB has become the default storage engine, and for good reason. MyISAM utilizes table-level locking. If one user writes to your orders table, everyone else trying to read from it has to wait. On a busy site, this creates a massive bottleneck.

InnoDB uses row-level locking. It allows multiple users to read and write to the same table simultaneously, provided they aren't touching the exact same row. Plus, it supports ACID compliance, which is non-negotiable for serious data integrity under the Norwegian Personal Data Act (Personopplysningsloven).

To check what engines you are running, log into your MySQL shell:

mysql> SELECT TABLE_NAME, ENGINE 
    -> FROM information_schema.TABLES 
    -> WHERE TABLE_SCHEMA = 'your_database_name' 
    -> AND ENGINE = 'MyISAM';

If you see core tables listed there, it is time to migrate. But simply switching engines isn't enough; you have to tune the memory allocation.

Configuring my.cnf for Victory

The default /etc/my.cnf on a fresh CentOS 6 install is laughable. It is optimized for a server with 512MB of RAM. If you are running on a modern CoolVDS instance with 4GB or 8GB of RAM, you need to tell MySQL to use it.

1. The Buffer Pool Size

For InnoDB, the innodb_buffer_pool_size is the single most critical setting. This is where data and indexes are cached. If your database fits in RAM, your disk I/O drops to near zero for reads.

Pro Tip: On a dedicated database server, set this to 70-80% of your total physical RAM. If you share the server with Apache/Nginx, dial it back to 50% to prevent swapping.

Here is a robust configuration snippet for a 4GB VPS serving a heavy dynamic site:

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

# InnoDB Tuning
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2 
innodb_file_per_table = 1

# Caching
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 64M

# Connections
max_connections = 300
wait_timeout = 600

Setting innodb_flush_log_at_trx_commit = 2 is a pragmatic choice. It flushes to the OS cache every second rather than every transaction. You risk losing one second of data in a total power failure, but the performance gain is massive. Given the stability of the Norwegian power grid and our data center UPS systems, this is a calculated risk worth taking for web applications.

The I/O Bottleneck: Why SSDs are the Future

You can tune software all day, but you cannot code your way out of physics. Traditional spinning hard drives (HDD) max out at around 100-150 IOPS (Input/Output Operations Per Second). A busy database doing complex joins and writes can easily demand 500+ IOPS.

When you exceed the physical limit of the drive, processes pile up in a state known as 'D' (Uninterruptible Sleep). Your load average skyrockets, and your SSH session starts lagging. This is where hardware selection becomes critical strategy.

At CoolVDS, we have moved aggressively to pure SSD storage for our primary clusters. We use KVM virtualization to ensure that your allocated I/O is actually yours, unlike OpenVZ containers where a "noisy neighbor" can steal your disk throughput. If you are hosting a site that targets users in Oslo or Bergen, the combination of our low latency to NIX (Norwegian Internet Exchange) and SSD throughput means your Time-To-First-Byte (TTFB) stays rock solid.

Identifying Slow Queries

Before you throw more hardware at the problem, find the bad code. MySQL has a built-in slow query log that is invaluable. Enable it in your config:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

Let this run for 24 hours. Then, use mysqldumpslow to analyze the results:

# Show top 5 queries sorted by time
mysqldumpslow -t 5 -s t /var/log/mysql/mysql-slow.log

You will often find queries scanning thousands of rows because of a missing index. Adding a simple INDEX can drop a query time from 2.5 seconds to 0.02 seconds.

System Level Tuning

Finally, Linux itself needs a tweak. By default, the kernel might be too eager to swap memory to disk, which is a death sentence for database performance. Check your swappiness:

cat /proc/sys/vm/swappiness

If it returns 60 (the default), change it to 0 or 10. We want the kernel to avoid swapping unless absolutely necessary.

# Apply instantly
sysctl vm.swappiness=0

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

Conclusion

Performance in 2012 isn't about magic; it is about configuration and removing bottlenecks. By switching to InnoDB, sizing your buffer pool correctly, and leveraging modern SSD infrastructure, you can handle traffic spikes that would crush a standard setup.

Don't let slow I/O kill your SEO rankings or drive customers to competitors. Deploy a test instance on CoolVDS in 55 seconds and see the difference real hardware isolation makes.