Database Sharding Strategies: A Survival Guide for High-Traffic Norwegian Applications

It starts with a creeping specific latency in your slow query log. Then, your Munin graphs show I/O wait creeping up to 40%. Finally, you hit the wall: your master database server, despite having 64GB of RAM and decent CPUs, simply cannot write fast enough. You have optimized your indexes, you have tuned your my.cnf until your eyes bled, and you are still timing out.

Welcome to the limit of vertical scaling. It is time to shatter the monolith.

In the Norwegian hosting market, where we value stability and data sovereignty (keeping Datatilsynet happy), moving to a sharded architecture is not just a technical decision; it is a business survival strategy. If you are running a high-load e-commerce platform targeting Oslo or Bergen, you cannot afford the latency introduced by routing traffic through Frankfurt or London. You need local, fast, and horizontally scalable architecture.

The Brutal Reality: Vertical vs. Horizontal

In 2014, hardware is fast, but it is not infinite. We often see clients at CoolVDS trying to throw more hardware at a bad architecture. They upgrade to our largest instance types, and while that buys time, it doesn't solve the write-lock contention on a single MySQL table.

• Vertical Scaling (Scaling Up): Adding more RAM/CPU to a single node. Easy, but has a hard ceiling.
• Horizontal Scaling (Sharding): Splitting data across multiple nodes. Complex, but theoretically infinite limits.

Sharding allows you to distribute the write load. Read replicas are great for reads, but they don't help when you need to insert 5,000 orders per second.

Sharding Topologies: Directory vs. Algorithmic

Before you touch a single config file, you must decide how you will route your data. There are two schools of thought currently dominating the landscape.

1. Directory-Based Sharding

You maintain a lookup table (a "Directory Service") that maps a key (like user_id) to a specific physical shard.

Pros: Flexible. You can move users between shards easily.
Cons: The lookup table becomes the new Single Point of Failure (SPOF) and a performance bottleneck. Every query requires two lookups.

2. Algorithmic Sharding (Recommended)

You use a hash function to determine the shard location. This is what we generally recommend for high-performance setups on CoolVDS because it requires zero network overhead for the lookup.

Pro Tip: Always shard by a key that isolates data completely. For SaaS, shard by customer_id. For social, shard by user_id. Never shard by date unless you enjoy creating "hot spots" where one server takes 100% of the traffic while the others sit idle.

Implementation: The Routing Logic

Here is a battle-tested example of how to implement algorithmic sharding in PHP (standard 5.5/5.6 stack). We calculate the shard ID based on the user integer.

<?php
class ShardManager {
    private $total_shards = 16;
    private $configs = array();

    public function __construct() {
        // Define connection details for shards
        $this->configs[0] = array('host' => '10.0.0.10', 'db' => 'app_shard_0');
        $this->configs[1] = array('host' => '10.0.0.11', 'db' => 'app_shard_1');
        // ... map remaining shards
    }

    public function getShardConnection($user_id) {
        // The Magic: Modulo operator determines destination
        $shard_id = $user_id % $this->total_shards;
        
        // Handle the "Logical to Physical" mapping
        // Multiple logical shards can live on one physical CoolVDS node
        $physical_node = $this->mapLogicalToPhysical($shard_id);
        
        return $this->connect($physical_node);
    }

    private function mapLogicalToPhysical($shard_id) {
        // Simple example: 16 logical shards spread across 2 physical servers
        if ($shard_id < 8) {
            return $this->configs[0];
        } else {
            return $this->configs[1];
        }
    }

    private function connect($config) {
        $dsn = "mysql:host={$config['host']};dbname={$config['db']};charset=utf8";
        return new PDO($dsn, 'db_user', 'secure_password', array(
            PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        ));
    }
}
?>

Infrastructure Matters: The KVM Difference

Logical architecture is useless if the underlying metal is weak. In 2014, we still see providers overselling OpenVZ containers where "guaranteed RAM" is a lie. For database shards, you need kernel isolation.

At CoolVDS, we use KVM (Kernel-based Virtual Machine). This means your database shard gets its own kernel and genuine resource allocation. More importantly, we run on Enterprise SSD arrays. Sharding increases aggregate IOPS (Input/Output Operations Per Second), but latency is defined by the disk speed.

Configuring MySQL 5.6 for Shards

When you split databases, you often end up with smaller datasets per node, allowing you to fit the entire working set into RAM. This is the holy grail of performance. Here is a baseline my.cnf optimization for a 4GB RAM shard instance running on CentOS 6:

[mysqld]
# NETWORK
bind-address            = 0.0.0.0
max_connections         = 500

# INNODB SPECIFIC
# Set this to 70-80% of available RAM if DB is dedicated
innodb_buffer_pool_size = 3G

# Crucial for SSD performance - allow more IO threads
innodb_write_io_threads = 8
innodb_read_io_threads  = 8

# Don't cap your SSD! Default is 200, which is for spinning rust.
innodb_io_capacity      = 2000

# SAFETY
innodb_flush_log_at_trx_commit = 1 # ACID compliant
sync_binlog             = 1

# LOGGING (Slow queries > 1s)
slow_query_log          = 1
long_query_time         = 1

The "Norwegian" Problem: Latency and Jurisdiction

Why host these shards in Norway? Physics. Light speed is finite. If your application servers are in Oslo, but your database shards are in a cheap US datacenter, you are adding ~100ms of round-trip time (RTT) to every query. In a sharded environment where an application might need to query two or three shards to build a view, that latency stacks up fast.

Furthermore, data privacy laws are tightening. While the Safe Harbor agreement currently allows data transfer to the US, many Norwegian entities prefer their user data to never cross the Atlantic to avoid NSA prying eyes. Hosting locally on CoolVDS ensures you are compliant with local interpretation of the Personal Data Act (Personopplysningsloven).

Generating Global Unique IDs

One final headache: Auto-incrementing IDs don't work across shards. If Shard A generates Order #100 and Shard B generates Order #100, you have a collision.

You have two options available right now:

1. UUIDs: Simple, but they are strings and destroy InnoDB clustering index performance.
2. Ticket Servers (Flickr Method): Use a central database dedicated solely to generating IDs via REPLACE INTO.

For high performance, we prefer a custom offset config in MySQL itself:

# On Shard 1
auto_increment_increment = 2
auto_increment_offset    = 1

# On Shard 2
auto_increment_increment = 2
auto_increment_offset    = 2

This ensures Shard 1 generates odd numbers (1, 3, 5) and Shard 2 generates even numbers (2, 4, 6). No collisions, no central bottleneck.

Conclusion

Sharding is not a silver bullet; it is an architectural commitment. It increases complexity in your application code and your deployment pipeline (Puppet/Chef becomes mandatory here). However, when you need to handle terabytes of data with sub-millisecond response times, it is the standard path forward.

Don't let slow I/O kill your SEO or your user experience. Deploy a test cluster on CoolVDS today. With our SSD-backed KVM instances and low-latency peering at NIX, your database will finally be able to breathe.