MySQL Performance Tuning: Surviving High Concurrency on Linux

There is a specific kind of silence that falls over a server room—or a terminal window—right before a database crashes. If you manage high-traffic web portals in Norway, you know the look of it. The load average on your CentOS 6 box creeps past 5.0, 10.0, then 20.0. SSH becomes sluggish. Your Nagios alerts start screaming about timeouts.

In 90% of the cases I investigate, the CPU isn’t the bottleneck. It’s the disk. It is always the disk.

I recently audited a large e-commerce platform targeting the Nordic market. They were running Magento on a standard shared hosting setup with spinning HDDs (7200 RPM). During a localized flash sale, their MySQL process entered a state of permanent D (Uninterruptible Sleep). The disk I/O was saturated, waiting for physical drive heads to seek, while customers were staring at white screens. We migrated them to a CoolVDS instance with pure SSD storage and KVM virtualization, tuned the buffers, and the load dropped to 0.4 instantly.

Here is how you tune MySQL 5.5 for performance in 2012, ensuring your architecture can handle the load without melting down.

1. The Engine War is Over: Use InnoDB

If you are still using MyISAM for your primary tables, stop. Just stop. Prior to MySQL 5.5, MyISAM was the default, which led to a generation of developers thinking table-level locking was acceptable. It is not.

With MyISAM, anytime a user writes to a table, MySQL locks the entire table. On a read-heavy site, this is fine. On a transactional site (like an online store), this is a disaster. One write blocks all reads.

InnoDB uses row-level locking. It is also ACID compliant, meaning if your server loses power (a rare event in Norway thanks to our stable grid, but possible), your data remains consistent. Ensure your my.cnf is prioritized for InnoDB.

2. The Holy Grail: innodb_buffer_pool_size

This is the single most important setting in your MySQL configuration. It determines how much data and how many indexes are cached in RAM. Accessing RAM is nanoseconds; accessing disk is milliseconds. You want to avoid the disk at all costs.

A common mistake is leaving this at the default 8MB or 128MB. If you have a dedicated database server (or a generous VPS), you should set this to 70-80% of your total available RAM.

Open your config:

And adjust the section under [mysqld]:

[mysqld]
# Optimize for 4GB RAM System
innodb_buffer_pool_size = 3G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Pro Tip: If you change innodb_log_file_size, you must stop MySQL, move the old ib_logfile0 and ib_logfile1 out of the data directory, and start MySQL to let it recreate them. If you don't, MySQL 5.5 will refuse to start.

3. Analyzing the Bottleneck

Don't guess. Measure. When your server feels slow, use iostat (part of the sysstat package) to see if the disk is the culprit.

You will see output like this:

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   svctm   %util
sda               0.00    15.00   40.00  120.00    6.25  100.00

Look at %iowait and %util. If %util is near 100% and %iowait is high, your physical storage simply cannot keep up with the database requests. No amount of software tuning will fix a physical limitation.

4. The Hardware Reality: Why SSDs Matter

In 2012, we are seeing a massive shift in server hosting. Traditional SAS/SATA drives spin at 10k or 15k RPM. They are limited by physics to roughly 120-200 IOPS (Input/Output Operations Per Second).

Enterprise SSDs (Solid State Drives) effectively remove the seek time latency. A standard CoolVDS SSD instance can handle thousands of IOPS. For a database doing random reads/writes, moving from HDD to SSD is the difference between a 2-second page load and a 200ms page load.

5. System Level Optimization

MySQL doesn't live in a vacuum; it lives on Linux. The kernel's I/O scheduler can interfere with database performance. The default scheduler is often cfq (Completely Fair Queuing), which is great for desktops but mediocre for databases.

For a database server, especially on the virtualized architecture we use at CoolVDS, you often want the deadline or noop scheduler. This reduces the overhead of the kernel trying to re-order requests.

Check your current scheduler:

Change it on the fly:

Swappiness

You also need to ensure Linux doesn't swap your MySQL process out to disk. By default, vm.swappiness is set to 60. Lower this to 0 or 10 to force the kernel to use RAM.

Add this to /etc/sysctl.conf to make it permanent.

6. The Norwegian Advantage

Finally, we must talk about latency and law. If your customers are in Oslo, Bergen, or Trondheim, hosting your database in a US datacenter adds 100-150ms of latency to every single round-trip packet. MySQL connections are chatty; those milliseconds add up to seconds of delay.

Furthermore, with the Data Inspectorate (Datatilsynet) becoming stricter about how personal data is handled under the Personal Data Act (Personopplysningsloven), keeping your data within Norwegian borders or at least the EEA is a pragmatic choice for compliance. It simplifies your legal exposure compared to relying on US Safe Harbor frameworks.

Conclusion

Performance is not an accident; it is an architecture. By switching to InnoDB, sizing your buffer pool correctly, and ensuring your underlying I/O subsystem uses modern SSD technology, you can handle traffic spikes that would crush a standard server.

Don't let slow I/O kill your SEO rankings or your user experience. Deploy a high-performance, SSD-backed instance on CoolVDS today and see what your database is actually capable of.