Scaling Beyond the Master: A Field Guide to Database Sharding in 2014

It starts with a pager alert at 03:00 CET. Your primary database server—the one you just upgraded to 64GB RAM last month—is locked up. The load average is 25.0, and iostat is screaming about disk saturation. You have hit the wall. The Vertical Scaling Wall.

For years, the standard advice in the systems administration world has been: "Don't shard until you absolutely have to." It is good advice. Sharding introduces operational complexity, breaks SQL JOINs, and makes backups a headache. But when you are running a high-traffic SaaS out of Oslo and your user base doubles overnight, throwing more RAM at the problem stops working.

In this post, we are going to look at how to implement application-level sharding without losing your mind, specifically using the new features in MySQL 5.6 and reliable KVM virtualization.

The Symptom: Why Your Single Master is Dying

Before we split the data, we need to confirm the diagnosis. If your CPU is idle but your application is timing out, run this:

$ iostat -x 1 10
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           5.20    0.00    2.10   45.50    0.00   47.20

Device:         rrqm/s   wrqm/s     r/s     w/s   svctm   %util
sda               0.00    15.00  120.50  340.20    2.45   98.50

See that 45.50% iowait? Your disk heads are thrashing. In 2014, standard spinning rust (HDD) in a RAID 10 array gives you maybe 400-600 random IOPS. Even basic SSDs can choke if the write patterns are aggressive enough. When you can't write fast enough, replication lag spikes, and users see stale data.

Strategy 1: Functional Partitioning (The Easy Win)

Before you rewrite your entire application to support sharding, try Functional Partitioning. This involves moving distinct datasets to separate servers.

If you have a monolithic database containing `users`, `forums`, and `logs`, there is rarely a need to join across them in real-time. Move `logs` to a SATA-based storage server (cheap, large capacity) and keep `users` on your high-performance node.

Pro Tip: Use the FederatedX storage engine in MariaDB or standard MySQL Federation to allow occasional cross-server queries for reporting, but keep the application logic separate.

Strategy 2: Horizontal Data Sharding

This is where the battle-hardened engineers earn their salary. You split a single table across multiple database instances (shards). The most common method is Modulo Hashing on a User ID.

The Logic

If you have 4 database servers (Shards 0-3), you determine where a user lives with:

The Configuration

You need to configure MySQL 5.6 to handle unique auto-increments across shards to avoid ID collisions if you ever need to merge data back or run active-active setups. Inside your my.cnf:

[mysqld]
# Server 1 Config
server-id = 1
auto_increment_increment = 4
auto_increment_offset = 1

# Server 2 Config would use offset = 2, etc.

The Application Layer

Since we don't have magic middleware that is production-ready yet (MySQL Proxy is still alpha), you handle this in your PHP/Python application code. Here is a simplified PHP 5.5 example utilizing PDO:

class ShardManager {
    private $shards = [
        'db0.internal.coolvds.net',
        'db1.internal.coolvds.net',
        'db2.internal.coolvds.net',
        'db3.internal.coolvds.net'
    ];

    public function getConnection($userId) {
        $shardIndex = $userId % count($this->shards);
        $host = $this->shards[$shardIndex];
        return new PDO("mysql:host={$host};dbname=app_production", 'user', 'pass');
    }
}

The Infrastructure Requirement: Why IOPS Matter

Sharding increases throughput by spreading load, but it increases latency complexity. If Shard 1 is on a slow host, 25% of your users suffer. Consistency is key.

This is where many "Cloud" providers fail. They use network-attached storage (SAN). Every disk write has to traverse the network, adding milliseconds of latency. In a high-transaction environment, those milliseconds stack up to seconds of delay.

At CoolVDS, we reject network storage for databases. We use local PCIe SSDs (the precursor to the emerging NVMe standard) directly attached to the KVM hypervisor. This minimizes latency and eliminates the "noisy neighbor" effect where another customer's backup job kills your database performance.

Data Sovereignty and The "Patriot Act" Risk

For our Norwegian and European clients, sharding also offers a compliance advantage. By controlling exactly which physical server holds specific data, you ensure compliance with the Personopplysningsloven (Personal Data Act).

Hosting on US-owned mega-clouds puts your data under the jurisdiction of the US Patriot Act, regardless of where the server is physically located. By utilizing CoolVDS, a company headquartered in Europe with infrastructure in Oslo, you keep your shards legally safe within the EEA. With the Datatilsynet keeping a close watch on international transfers, this is a massive headaches-saver for CTOs.

The Deployment

If you are ready to test a sharded architecture, do not try it on shared hosting. You need root access, custom my.cnf tuning, and private networking.

Recommended Stack for 2014:

• OS: CentOS 6.5 or Ubuntu 12.04 LTS
• DB: MySQL 5.6 (Percona Server if you need XtraDB)
• Cache: Redis 2.8 for hot-row caching
• Automation: Ansible (Agentless, simpler than Puppet)

Don't let legacy infrastructure bottleneck your growth. Sharding is complex, but your hardware shouldn't be. Deploy a high-IOPS KVM instance on CoolVDS today and see what sub-millisecond latency looks like.