Database Sharding Strategies: Surviving High Concurrency in a Post-GDPR World

There is a specific moment of panic every systems architect knows. It happens when you look at htop and see all 32 cores pinned at 100%, load average hitting 40, and your iowait is climbing faster than the price of Bitcoin. You have already upgraded the RAM. You have already optimized your indexes. You have tuned innodb_buffer_pool_size until there is no RAM left to give.

Welcome to the limit of vertical scaling. It is time to shatter your monolithic database into pieces.

In this analysis, we are going to look at practical sharding strategies applicable right now, in 2018. We aren't talking about theoretical hyperscale setups like Facebook; we are talking about actionable architecture for high-growth platforms targeting the European and Norwegian markets.

The "Vertical Scaling" Trap

Most VPS providers will happily sell you a massive instance. They will tell you to just "add more cores." But in the database world, specifically with MySQL 5.7 or MariaDB, lock contention eventually kills you regardless of CPU count. If you have a write-heavy application—think logging user activity, IoT sensor data from smart grids, or high-velocity e-commerce—a single master node becomes a bottleneck that no amount of hardware can solve.

The solution is Sharding (Horizontal Partitioning).

Sharding splits your data across multiple physical servers. However, it introduces complexity: distributed transactions, data consistency challenges, and network latency. If your servers are not physically close—for example, routing traffic between Frankfurt and Oslo—your application will crawl.

Strategy 1: Application-Level Sharding

The simplest approach is routing logic inside your application code (PHP, Python, Go). You determine which database server to connect to based on a "Shard Key" (usually user_id or company_id).

The Logic:

function getDatabaseConnection(userId) {
    $shardId = userId % 4; // Modulo operation for 4 shards
    
    $servers = [
        0 => '10.0.0.10', // Shard A (CoolVDS Oslo Node 1)
        1 => '10.0.0.11', // Shard B (CoolVDS Oslo Node 2)
        2 => '10.0.0.12', // Shard C (CoolVDS Oslo Node 3)
        3 => '10.0.0.13'  // Shard D (CoolVDS Oslo Node 4)
    ];
    
    return new PDO("mysql:host=" . $servers[$shardId] . ";dbname=app_data", $user, $pass);
}

While easy to implement, this makes scaling painful. Adding a 5th shard requires rebalancing all data (re-sharding), which is a nightmare scenario involving significant downtime.

Strategy 2: Middleware Routing with ProxySQL

A more robust solution for 2018 is ProxySQL. It sits between your application and your database nodes, parsing SQL traffic and routing it based on rules defined in its own internal admin database.

This allows you to shard without changing application code. It also handles connection pooling, which reduces the TCP handshake overhead—crucial when you are running high-performance KVM instances.

Configuration: Implementing Range-Based Sharding

Let's assume we want to split data based on User IDs. Users 1-10000 go to Shard-1, Users 10001-20000 go to Shard-2.

First, define your hostgroups in ProxySQL:

-- Log into ProxySQL Admin
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (10, '192.168.1.10', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, '192.168.1.20', 3306);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Next, we define the query rules. This involves regex matching on the incoming SQL queries. This is powerful but requires your SQL syntax to be consistent.

-- Route queries for users < 10000 to Hostgroup 10
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, "^SELECT.*WHERE user_id < 10000", 10, 1);

-- Route queries for users >= 10000 to Hostgroup 20
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, "^SELECT.*WHERE user_id >= 10000", 20, 1);

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

Pro Tip: Middleware adds a "hop" to your network path. If your application server is in a different datacenter than your ProxySQL instance or your DB shards, latency will destroy your performance. We strongly recommend utilizing private networking within the same facility. On CoolVDS, we utilize KVM virtualization which allows you to establish low-latency private links between instances in our Oslo datacenter, keeping internal ping times negligible.

The Hardware Reality: NVMe is Non-Negotiable

Sharding solves CPU and RAM bottlenecks, but it exposes I/O weaknesses. When you split a database, you often increase the aggregate throughput requirement. Traditional spinning rust (HDD) or even SATA SSDs often choke under the random Read/Write patterns of a sharded architecture.

In 2018, NVMe (Non-Volatile Memory Express) is the standard for serious database workloads. The queue depth management in NVMe allows for tens of thousands of IOPS per instance.

Optimizing MySQL 5.7 for NVMe

If you are deploying on CoolVDS NVMe instances, you must tune your `my.cnf` to actually utilize that speed. Default MySQL settings are often tuned for spinning disks.

[mysqld]
# Maximize I/O capacity for NVMe
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Disable neighbors flushing (NVMe doesn't have seek time issues)
innodb_flush_neighbors = 0

# Ensure we use the OS cache effectively
innodb_flush_method = O_DIRECT

# Dedicate 70-80% of RAM to the pool
innodb_buffer_pool_size = 12G

# Redo logs - vital for write-heavy shards
innodb_log_file_size = 1G
innodb_log_buffer_size = 16M

The GDPR Factor: Geo-Sharding

With the General Data Protection Regulation (GDPR) enforcement date of May 25, 2018, approaching rapidly, where you store data is just as important as how you store it. Datatilsynet (The Norwegian Data Protection Authority) is becoming increasingly strict regarding data sovereignty.

Sharding offers a unique compliance advantage: Geo-Partitioning.

You can architect your system so that all users with a Norwegian billing address (country_code = 'NO') are routed specifically to Shard nodes physically located in Norway. This simplifies compliance audits significantly. You are not just sharding for performance; you are sharding for legal safety.

The Verdict

Sharding is not a decision to take lightly. It complicates backups, schema changes, and reporting. However, when you need to handle terabytes of data with sub-millisecond response times, it is the only path forward.

Success relies on two pillars: intelligent routing (using tools like ProxySQL) and uncompromising hardware performance. You cannot shard a high-performance database onto budget, oversold hardware. The latency jitter will result in timeouts and application 500 errors.

If you are planning a shard deployment, testing network throughput and disk I/O stability is mandatory. Don't guess. Spin up a CoolVDS NVMe instance today, run sysbench, and see the numbers yourself. Your database is the heart of your infrastructure; treat it with the respect it deserves.