MySQL 5.5 vs. PostgreSQL 9.1: The Architect's Choice for High-Performance IO

It is May 2012. The dust has settled on Oracle’s acquisition of Sun Microsystems, and the panic regarding MySQL's future is slowly morphing into a pragmatic fork in the road. On one side, we have the ubiquitous MySQL 5.5, the default engine for the web, recently bolstered by InnoDB becoming the default storage engine. On the other, PostgreSQL 9.1 has emerged from academia to challenge the enterprise status quo with synchronous replication and unlogged tables.

As a System Administrator who has spent too many nights debugging replication lag on overloaded masters, I can tell you: the software is only half the battle. The other half is the metal it runs on. Whether you are hosting a Magento cluster for a retailer in Oslo or a Django application for a startup in Trondheim, latency is the enemy.

The Incumbent: MySQL 5.5 (and the rise of Percona)

MySQL is the devil we know. It powers Facebook (heavily modified), it powers WordPress, and it powers 90% of the LAMP stacks deployed across Europe. With version 5.5, the shift from MyISAM to InnoDB as the default is the single biggest improvement for data integrity and write-heavy workloads. Row-level locking is finally the standard out of the box.

However, default configurations in RHEL 6 or Debian Squeeze are often woefully inadequate for production.

Optimizing `my.cnf` for SSDs

If you are lucky enough to be hosting on CoolVDS, you aren't spinning rust; you are on high-speed SSD storage. You need to tell InnoDB that it doesn't need to be polite to a mechanical arm.

Here is a production-ready snippet for /etc/my.cnf on a 4GB RAM VPS intended for high throughput:

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

# The most important setting for InnoDB
# Set to 70-80% of available RAM on a dedicated DB server
innodb_buffer_pool_size = 3G

# SSD Optimization Flags
# Default is 200, crank this up for SSDs
innodb_io_capacity = 2000
innodb_flush_neighbors = 0

# Durability vs Speed
# 1 = safest (fsync every txn), 2 = fast (fsync every sec)
# Use 2 if you have a UPS or trust your host's power redundancy
innodb_flush_log_at_trx_commit = 1

# Concurrency
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64

Using innodb_io_capacity = 2000 is essential when running on flash storage. If you leave this at the default, MySQL assumes you are running on a slow 7200 RPM drive and throttles background flushing, leading to jittery performance during heavy write bursts.

Pro Tip: If you are worried about Oracle's stewardship, look at Percona Server or MariaDB 5.3. They are binary compatible drop-ins that often offer better diagnostics, like the Percona Toolkit (formerly Maatkit). run pt-diskstats to really see what your I/O subsystem is doing.

The Challenger: PostgreSQL 9.1

PostgreSQL has long been criticized for being "slow" compared to MySQL's read speed. In 2012, that argument is largely dead. With version 9.1, PostgreSQL offers features that MySQL simply cannot touch without massive complexity.

• Synchronous Replication: Guarantee zero data loss if the primary node dies.
• hstore: A key-value store inside your relational DB. It’s perfect for catalog data where attributes vary by product.
• GIS: PostGIS remains the gold standard for location-based services.

However, Postgres relies heavily on the OS page cache. While MySQL manages its own memory via the Buffer Pool, Postgres expects the Linux kernel to do the heavy lifting.

Tuning `postgresql.conf` for Linux 2.6.32+

A standard yum install postgresql91-server on CentOS 6 gives you a config designed for a machine from 2002. Let's fix that.

# /var/lib/pgsql/9.1/data/postgresql.conf

# Memory Configuration
# Only 25% of RAM, because Postgres uses OS Cache
shared_buffers = 1024MB 

# Tell the query planner how much OS cache is available
effective_cache_size = 3GB

# Checkpoints (The I/O Spikes Killer)
# Spread out writes to avoid freezing the system
checkpoint_segments = 32
checkpoint_completion_target = 0.9

# Logging
log_min_duration_statement = 250ms # Log slow queries

# SSD Tuning
random_page_cost = 1.1 # Lower this! SSD random seeks are cheap.
effective_io_concurrency = 200 # For RAID/SSD setups

The parameter random_page_cost is the secret weapon. Defaulting to 4.0, it assumes seeking the disk is expensive. On CoolVDS SSD arrays, random seeks are nearly as fast as sequential reads. Lowering this makes the Postgres query planner much more likely to use indexes effectively.

The "I/O Wait" Bottleneck

Whether you choose MySQL or Postgres, your database is only as fast as the underlying block device. In a shared hosting environment, "noisy neighbors" can steal your IOPS, causing your database to hang while waiting to write to disk. This is visible in `top` under the %wa (iowait) column.

To verify your disk latency, you can run a simple `dd` test (carefully!) or use `sysbench`. Here is how we benchmark file I/O on a fresh CoolVDS instance running CentOS 6:

# Install sysbench from EPEL repo
yum install -y sysbench

# Prepare 10GB test file
sysbench --test=fileio --file-total-size=10G prepare

# Run random read/write test
sysbench --test=fileio --file-total-size=10G --file-test-mode=rndrw --max-time=300 --max-requests=0 run

On legacy spinning SAS drives, you might see 150-200 requests per second (RPS). On our infrastructure, we consistently see numbers an order of magnitude higher. This low latency is critical for heavy JOIN operations or high-concurrency INSERT workloads.

Data Sovereignty and The "Datatilsynet" Factor

Performance isn't the only metric. For those of us operating in Norway, compliance with the Personopplysningsloven (Personal Data Act) is mandatory. While the Safe Harbor framework exists for US transfers, many Norwegian CTOs are increasingly wary of hosting sensitive customer data outside the EEA.

Keeping your database close to your users—physically in Oslo or nearby European hubs—solves two problems:

1. Latency: A ping from Oslo to a server in Virginia, USA is ~90ms. A ping to a local CoolVDS instance is <5ms. For a database application doing 50 sequential queries to render a page, that is the difference between a 250ms load time and a 4.5-second load time.
2. Jurisdiction: You ensure compliance with Norwegian data directives by default.

Verdict: Which One to Choose?

Choose MySQL 5.5 if:

• You are running a standard CMS (WordPress, Joomla, Drupal).
• Your team is already fluent in LAMP.
• You need simple Master-Slave replication setup quickly.

Choose PostgreSQL 9.1 if:

• You need complex transactions or financial data integrity.
• You require Geospatial extensions (PostGIS).
• You are building a custom application and need schema flexibility via `hstore`.

The Final Word

A database is a heavy piece of machinery. Don't put a Ferrari engine in a tractor. Virtualization technologies like OpenVZ often overcommit resources, leading to unpredictable database performance. At CoolVDS, we utilize KVM virtualization to ensure that the RAM and Disk I/O you pay for is the RAM and Disk I/O you actually get.

Don't let slow I/O kill your SEO rankings. Deploy a high-performance database instance today and stop watching the %wa column.