Scaling MySQL: Vertical Limits, Sharding Strategies, and the I/O Bottleneck

There is a specific moment in every Systems Architect's career that induces a cold sweat. It's not a server crash—crashes are loud and obvious. It's the silence of a database lock queue climbing past 500 connections while CPU usage sits idly at 10%.

Your application isn't down, but it might as well be. You have hit the wall of vertical scaling.

In the Norwegian hosting market, where data sovereignty and latency to the NIX (Norwegian Internet Exchange) are critical, throwing hardware at the problem is usually the first instinct. And frankly, in 2017, hardware is getting incredibly good. We see this daily at CoolVDS; clients migrate legacy magnetic spinning disk workloads to our KVM-based NVMe instances and see immediate 10x throughput gains. But hardware has physical limits. Physics is stubborn.

When your dataset exceeds the capacity of a single node's RAM, or when write-concurrency saturates the I/O bus, you are left with two choices: optimize your queries until your eyes bleed, or shard the database.

The Vertical Trap (and when to stay in it)

Before you slice your data into pieces, you must be absolutely certain you cannot scale up (vertical scaling). Sharding introduces operational complexity that can kill a small team. It breaks ACID compliance across nodes. It makes backups a distributed nightmare.

If your database is under 500GB, you likely do not need sharding yet. You need better hardware and configuration.

The my.cnf Reality Check

Most default MySQL 5.7 installations are criminal. They are configured for systems with 512MB of RAM. If you are running on a CoolVDS 32GB RAM instance, you must adjust the buffer pool. The data must live in memory, not on the disk, even if that disk is fast NVMe storage.

Here is the baseline configuration we deploy for write-heavy single-node instances:

[mysqld]
# Allocate 70-80% of RAM to the buffer pool if this is a dedicated DB server
innodb_buffer_pool_size = 24G

# Essential for write-heavy workloads to prevent disk thrashing
innodb_log_file_size = 2G

# 0 = speed (risk of data loss), 1 = ACID (slow), 2 = Compromise
innodb_flush_log_at_trx_commit = 1 

# Optimization for SSD/NVMe drives
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = 0

# Character set standardization (critical for Norwegian characters)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

If you have applied these settings, optimized your indexes, and your `iowait` is still consistently above 20%, welcome to the sharding precipice.

Sharding Architectures: Range vs. Hash

Sharding is the process of splitting your data horizontally across multiple servers. Instead of one table with 100 million rows, you might have ten tables (on ten servers) with 10 million rows each.

1. Key-Based (Hash) Sharding

This is the most common programmatic approach. You take a unique identifier (like a `user_id`), hash it, and use the modulo operator to determine which server the data lives on.

Pros: Even distribution of data. No "hot spots" based on recent activity.
Cons: Resharding is painful. If you go from 10 to 11 servers, you have to migrate almost all data.

import zlib

def get_shard_id(user_id, total_shards):
    """
    Deterministic routing based on user_id.
    Returns an integer between 0 and total_shards - 1.
    """
    # Use CRC32 for a fast, deterministic hash
    checksum = zlib.crc32(str(user_id).encode('utf-8'))
    return checksum % total_shards

# Configuration map
SHARD_MAP = {
    0: {'host': '10.0.0.1', 'name': 'db_shard_01'},
    1: {'host': '10.0.0.2', 'name': 'db_shard_02'},
    2: {'host': '10.0.0.3', 'name': 'db_shard_03'},
    3: {'host': '10.0.0.4', 'name': 'db_shard_04'},
}

uid = 482910
shard_index = get_shard_id(uid, 4)
server_config = SHARD_MAP[shard_index]

print "Routing User %s to %s (%s)" % (uid, server_config['name'], server_config['host'])

2. Range-Based (Directory) Sharding

You define ranges: Users 1-1,000,000 go to Server A. Users 1,000,001-2,000,000 go to Server B.

Pros: Easy to add new shards (just add a new range).
Cons: Terrible uneven load. The "newest" shard usually takes all the write traffic, creating a hot spot.

The Latency Factor: Why Geography Matters

When you shard, you increase network chatter. An application might need to query three different nodes to assemble a user's dashboard. This brings us to a metric often ignored in US-centric tutorials: Round Trip Time (RTT).

If your application servers are in Oslo and your database shards are scattered across cheap providers in Frankfurt or Amsterdam, the speed of light will punish you. Each query adds 20-30ms of latency. In a complex join simulated in application code, 10 queries equal 300ms of delay. That is perceptible to the user.

Pro Tip: Keep the compute and the data close. For our Norwegian clients, we emphasize local peering at NIX. If your VPS and your database shard communicate over a local private network (LAN) rather than the public internet, latency drops from milliseconds to microseconds. CoolVDS infrastructure uses 10Gbps internal uplinks for precisely this reason.

Managing Schema Changes in a Sharded World

You have 50 shards. You need to add a column. Running `ALTER TABLE` on 50 servers sequentially is a recipe for downtime. In 2017, the tool of choice for this is Percona Toolkit's `pt-online-schema-change`.

It creates a copy of the table, applies the change to the copy, tracks triggers for ongoing updates, and then swaps the tables atomically.

pt-online-schema-change \
  --alter "ADD COLUMN last_login_ip VARCHAR(45)" \
  --host=10.0.0.5 \
  --user=admin \
  --password=${DB_PASS} \
  D=production,t=users \
  --execute

The Infrastructure of Choice: KVM over Containers

There is a lot of buzz right now about running databases in Docker containers. While Docker is fantastic for stateless application code, running stateful, high-IOPS databases inside containers introduces complexity regarding persistence and networking overhead.

For a production database shard, you want raw access to CPU scheduling and disk I/O. This is why we stick to KVM (Kernel-based Virtual Machine) virtualization. It provides the isolation needed so that a "noisy neighbor" on the host node cannot steal your CPU cycles during a critical transaction.

Compliance and the Looming GDPR

We cannot discuss data architecture in Europe today without mentioning the upcoming General Data Protection Regulation (GDPR). The deadline is next year (2018), and Datatilsynet (The Norwegian Data Protection Authority) is already ramping up guidance.

Sharding adds a layer of complexity to compliance. If a user exercises their "Right to be Forgotten," you must ensure their data is purged from all shards. This requires a robust mapping service. If you lose the map (the routing logic), you lose the ability to prove compliance.

Final Recommendation

Sharding is a sledgehammer. Do not use it to hang a picture frame. First, exhaust your vertical options:

1. Optimize your queries (EXPLAIN is your friend).
2. Implement caching (Redis/Memcached).
3. Move to high-performance storage (NVMe).

Only when these fail should you shard. And when you do, ensure the underlying infrastructure is solid. A sharded database on unstable network links is a distributed disaster.

Ready to test your architecture? Deploy a high-performance KVM instance in Oslo with CoolVDS today. We provide the raw NVMe power you need to delay sharding as long as possible.