How a Cross‑Border E‑Commerce Scaled from MySQL to OceanBase: A Full Migration Case Study

01 Cross‑Border E‑Commerce Database Replacement Needs

The company operates a cross‑border e‑commerce platform with over 1 million SKUs and billions of listings, storing all data in a single MySQL instance that grew to 6 TB across 1,400+ tables. The monolithic design caused severe bottlenecks: high CPU usage on the primary, replication lag exceeding thresholds, costly storage, and difficulty partitioning data.

MySQL single‑machine to cluster upgrade led to full‑table updates that saturated the primary and caused replication delays.

Large tables required frequent full‑table updates, creating TPS bottlenecks.

Extensive multi‑table joins made traditional sharding impractical.

MySQL storage consumption drove up cloud‑disk costs.

02 Database Selection and Secondary Migration

The team defined four essential requirements for a new database: robust sharding capabilities, high‑availability cluster with low replica lag, easy scalability, and simple operations with real‑time SQL diagnostics while reducing storage costs.

After evaluating the top ten databases, OceanBase was chosen for its strong architecture, comprehensive documentation, and responsive community support.

Table‑group and replica‑table features dramatically cut cross‑node join overhead.

High compression (up to 1:6) reduced storage from 6 TB to 920 GB after migration.

Cluster’s high availability and the free OCP operation tool simplified monitoring, scaling, and SQL diagnosis.

Paxos‑based replication lowered master‑slave sync latency, improving read performance.

Cost comparison (5‑year total):

RDS MySQL 8.0 – 52 CPU cores, 512 GB RAM, 6 TB disk → ~1.26 M CNY.

OceanBase 4.2.1 – 64 CPU cores, 512 GB RAM, 1.5 TB disk → ~0.76 M CNY.

Migration Process

First Attempt

Deployed nine 16‑core/64 GB nodes (OceanBase 3‑3‑3), partitioned large tables, grouped related tables, and used replica tables to avoid cross‑node joins. This solved TPS latency but the cluster could not sustain production load. Problems encountered:

Residual cross‑node queries still consumed CPU.

Some tables could not be partitioned, causing write‑sync delays.

OBProxy and OBServer on the same machine created CPU contention.

Second Attempt

Learned from the first failure and switched to a 64‑core/512 GB 1‑1‑1 cluster. Migration was split into three phases:

Phase 1: All tables placed in a single large group with MySQL‑compatible schema, no partitioning. Two OBProxy instances were deployed—one routing reads to the primary, the other to replicas—with a 400 ms lag threshold.

Phase 2: Gradually partitioned business‑module tables, moved them into dedicated table groups, and distributed primary replicas across nodes.

Phase 3: Removed read‑write split proxy, directing all reads to the primary to eliminate replica‑lag‑induced inconsistencies and prepare for future scaling.

03 OceanBase Trial Benefits

After one month of trial in the internal OA system, performance matched MySQL and is expected to surpass it as data grows.

Storage cost dropped ~40 % (6 TB → 1.5 TB).

Disk performance tier reduced from high‑end L2 to L1, cutting IOPS expenses.

Replica lag remained low, keeping the cluster stable and preventing query spikes on the primary.

OCP’s full‑SQL diagnostics eliminated the need for costly MySQL‑level monitoring.

Remaining incompatibilities include lack of full‑text index in version 4.2.1, slower SQL planner for parameter‑heavy queries, limited type‑conversion optimizations, no temporary tables, and occasional plan variability.

04 Operational Experience

Key issues and solutions gathered during production:

Read‑write split proxy consistency: Modify application code to force transactions to use the primary proxy.

Memory limit OOM: Reset memory_limit to the default (0 M) or to 90 % of total memory after query optimization.

Disk usage spikes during nightly merge: Allocate an extra 20 % disk capacity for LSM‑Tree compaction.

Backup retention mismatch: Switch to daily full backups to keep exactly seven days of data.

Overall, OceanBase met expectations, delivering cost savings, higher stability, and strong community support, though a smoother onboarding guide would further reduce the learning curve for new users.