Surviving the Write Cliff: Practical Database Sharding Strategies for High-Scale Systems

There is a specific moment in every Systems Architect's career that generates pure, unadulterated cold sweat. It usually happens around 3:00 AM. Your primary MySQL node, which you've been vertically scaling for two years by throwing more RAM and faster CPUs at it, finally hits the wall. I/O wait spikes to 80%, connection pools exhaust, and your application—perhaps a high-volume e-commerce platform targeting the Nordic market—grinds to a halt.

We call this the "Write Cliff." You can read all the data you want with read-replicas, but you can only write to one Master. Until you shard.

Sharding is not a silver bullet; it is an complexity grenade. But in October 2018, with data volumes exploding and GDPR (thanks, Datatilsynet) demanding strict data residency, it is often the only path forward. Here is how we implement it without destroying data integrity, and why the underlying infrastructure—specifically network latency and I/O throughput—matters more than your sharding key.

The "Why" and "When" (Don't Do It Yet)

If your database is under 500GB, do not shard. Optimize your queries. Tune your `innodb_buffer_pool_size`. Index properly. Sharding introduces network overhead and distributed transaction headaches. However, if you are pushing 5,000+ writes per second and your NVMe storage is saturated, welcome to the club.

In a recent deployment for a logistics firm in Oslo, we faced a hard limit. The tracking table hit 2 billion rows. `ALTER TABLE` operations became impossible without maintenance windows that the SLA didn't allow. We had to split.

Strategy 1: Application-Level Sharding (The "Manual" Way)

The simplest approach is keeping the logic in your code. You pick a Shard Key—usually a `user_id` or `customer_id`—and route traffic accordingly. The most robust method for this in 2018 is Consistent Hashing.

Unlike a simple modulo operator (`id % 4`), consistent hashing allows you to add nodes without rebalancing the entire dataset. Only K/n keys need to move.

The Logic

function getShardId($resourceId, $totalShards) {
    // Simple CRC32 hashing for distribution
    $hash = crc32($resourceId);
    return $hash % $totalShards;
}

$shardConfig = [
    0 => ['host' => '10.0.0.10', 'db' => 'shard_0'],
    1 => ['host' => '10.0.0.11', 'db' => 'shard_1'],
    2 => ['host' => '10.0.0.12', 'db' => 'shard_2'],
];

$customerId = 49201;
$shardIndex = getShardId($customerId, count($shardConfig));
$connection = connect($shardConfig[$shardIndex]);

This works, but it pollutes your application code. Every developer on the team needs to know how the data is split. If a junior dev runs a cross-shard join, the application crashes or returns incomplete data.

Strategy 2: Middleware Routing (ProxySQL)

A cleaner, more "Battle-Hardened" approach uses middleware. ProxySQL has become the de-facto standard for this in the MySQL world this year. It sits between your app and the databases, speaking the MySQL protocol. Your app thinks it connects to one DB; ProxySQL handles the routing.

This is superior for latency. If you host on CoolVDS, where we prioritize internal network throughput, the extra hop adds negligible latency (often sub-millisecond). However, on congested public clouds, this extra hop can kill performance.

ProxySQL Configuration Example

Here, we route traffic based on the user ID embedded in the query comments or simply by analyzing the query structure. A robust setup uses query rules.

-- Define your shards in the hostgroups
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (10, '10.0.2.15', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, '10.0.2.16', 3306);

-- Route based on User ID (sharding_key)
-- This assumes queries are rewritten or tagged, or we use regex on the WHERE clause
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, "WHERE user_id < 100000", 10, 1);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, "WHERE user_id >= 100000", 20, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

This removes the logic from the PHP/Python layer. The infrastructure handles the complexity.

The Hidden Killer: ID Generation

When you shard, AUTO_INCREMENT is dead. You cannot have two users with ID `501` on different shards if you ever plan to aggregate data. You need globally unique IDs.

Twitter Snowflake (or similar implementations) is the standard solution here. We generate a 64-bit integer composed of:

• 41 bits: Timestamp (milliseconds)
• 10 bits: Machine ID (Node ID)
• 12 bits: Sequence number

This ensures that ID generation is distributed, time-sortable, and collision-free without needing a central coordination server (which would become a bottleneck).

Infrastructure Requirements for Sharding

Sharding turns a CPU problem into a Network and I/O problem. Instead of one big pipe, you have many smaller pipes chattering constantly.

Pro Tip: Never shard across different data centers if you need synchronous consistency. The speed of light is a hard limit. If your app servers are in Oslo, your shards must be in Oslo.

This is where standard VPS providers fail. They oversell the network uplink. At CoolVDS, we use KVM virtualization to ensure strict isolation, but more importantly, our local peering at NIX (Norwegian Internet Exchange) ensures that if you are splitting traffic between instances, the packets don't take a detour through Sweden. Every millisecond of latency locks your row for longer.

MySQL Tuning for Shards

Since each shard is smaller, you must tune `my.cnf` differently than a monolith. You aren't allocating 128GB RAM to one instance anymore.

[mysqld]
# Reduced buffer pool per shard instance
innodb_buffer_pool_size = 8G 

# Crucial for data integrity across distributed nodes
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

# Minimize connection overhead
max_connections = 500
skip-name-resolve

Data Sovereignty and GDPR

Since May 2018, the game has changed. If you shard by user location (e.g., Shard A for EU users, Shard B for non-EU), you solve a legal problem technically. Keeping Norwegian user data physically on servers in Oslo (like CoolVDS provides) simplifies your compliance documentation significantly compared to explaining why data is sharded across unknown availability zones.

Conclusion

Sharding is painful. It breaks joins, complicates backups, and requires rigorous discipline in ID generation. But it is also the only way to scale infinitely. The success of a sharded architecture depends less on the code and more on the underlying iron. You need stable, high-IOPS storage (NVMe) and rock-solid low-latency networking.

If you are planning to break up your monolith, do not do it on hardware that steals your CPU cycles. Deploy a benchmark instance on CoolVDS today and test the network throughput between nodes yourself.