How to Overcome Database Bottlenecks with Sharding and Scaling Strategies

Database Bottleneck

Both I/O and CPU bottlenecks increase the number of active database connections, eventually reaching the connection limit and causing concurrency, throughput, and crash issues.

I/O Bottleneck

Disk read I/O bottleneck: hot data exceeds cache, causing many reads; solution – sharding and vertical partitioning.

Network I/O bottleneck: excessive request data exceeds bandwidth; solution – sharding.

CPU Bottleneck

SQL issues such as joins, GROUP BY, ORDER BY, or non‑indexed queries increase CPU load; solution – SQL optimization, proper indexes, and moving business calculations to the service layer.

Large single‑table data leads to full scans and high CPU usage; solution – horizontal table partitioning.

Horizontal Database Sharding

Concept: split data across multiple databases based on a field using strategies like hash or range.

Result: each database has identical schema, disjoint data, and the union of all databases equals the full dataset.

Scenario: high absolute concurrency where table sharding alone cannot solve the problem and there is no clear business module for vertical sharding.

Analysis: more databases reduce I/O and CPU pressure.

Horizontal Table Sharding

Concept: split a table into multiple tables based on a field using hash or range.

Result: each table has identical structure, disjoint data, and the union of all tables equals the full dataset.

Scenario: a single large table degrades SQL efficiency and increases CPU load.

Analysis: smaller tables improve query speed and reduce CPU load.

Vertical Database Sharding

Concept: split tables into different databases according to business domains.

Result: each database has different schema and data, with no overlap; the union of all databases equals the full dataset.

Scenario: absolute concurrency spikes and clear business module boundaries exist.

Analysis: enables service‑oriented architecture and isolates configuration or dictionary tables.

Vertical Table Sharding

Concept: split a table into a main table and extension tables based on field activity.

Result: different table structures, disjoint data, with a common key (usually the primary key) for joining.

Scenario: a table has many columns, with hot and cold data mixed, causing large rows and random‑read I/O.

Analysis: keep hot columns in the main table to improve cache hit rate and reduce I/O.

Sharding Tools

sharding-jdbc (Dangdang)

TSharding (Mogujie)

Atlas (Qihoo 360)

Cobar (Alibaba)

MyCAT (based on Cobar)

Oceanus (58.com)

Vitess (Google) – compare pros and cons yourself

Problems Introduced by Sharding

Sharding alleviates single‑node and single‑table bottlenecks but brings new challenges.

Transaction Consistency

Cross‑database transactions require distributed transaction protocols such as XA or two‑phase commit, which increase latency and risk of conflicts or deadlocks as the number of nodes grows.

Eventual Consistency

For systems tolerant of latency, eventual consistency can be achieved via compensation transactions, reconciliation, or periodic synchronization.

Cross‑Node Join Issues

Avoid joins across shards; use global tables, field redundancy, data assembly in the service layer, or ER‑sharding (place related tables in the same shard).

Cross‑Node Pagination, Sorting, Aggregation

Sorting and pagination must be performed per shard and then merged, which can be CPU‑ and memory‑intensive for large page numbers. Aggregation functions (MAX, MIN, SUM, COUNT) also require per‑shard execution followed by a final merge.

Global Primary‑Key Duplication

Auto‑increment IDs are unsuitable; alternatives include UUIDs, a dedicated sequence table, or distributed ID generators.

CREATE TABLE `sequence` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `stub` char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stub` (`stub`)
) ENGINE=MyISAM;

REPLACE INTO sequence (stub) VALUES ('a');
SELECT 1561439;

These approaches have single‑point and performance limitations; using multiple ID‑generation servers can distribute load but adds complexity.

Snowflake Distributed ID Algorithm

1 bit unused.

41 bits for millisecond timestamp (covers 69 years).

10 bits for datacenter and worker IDs (up to 1024 nodes).

12 bits for sequence within the same millisecond (4096 IDs per ms per node).

Data Migration and Capacity Planning

When scaling, migrate historical data to new shards based on the chosen sharding rule, and plan capacity by keeping single‑shard table size below ~10 million rows.

When to Consider Sharding

Only shard when necessary; avoid premature optimization.

Triggers: massive data volume affecting operations, rapid data growth, or fields that need vertical separation.

Large tables cause backup I/O, long DDL locks, and lock contention.

Before sharding, exhaust other optimizations such as hardware upgrades, read/write splitting, and index tuning.