DeWu’s OceanBase Deployment: Multi‑Model Database Selection, Performance Gains, and Migration Lessons

Background

DeWu operates a global fashion e‑commerce and community platform with demanding user‑experience requirements, supporting core trading, operational analytics, AI algorithms, and community services. Its online database services span MySQL, TiDB, MongoDB, HBase, DuckDB, ClickHouse/StarRocks, and vector databases, leading to high operational complexity, costly resources, and scaling difficulties.

Multi‑Model Database OceanBase Selection

OceanBase, built on the Paxos protocol, offers strong synchronous replication (RPO = 0, RTO < 8 s), multi‑region deployment, and a flexible architecture that can start from a single‑node MySQL‑like deployment and scale to distributed clusters with multi‑tenant resource allocation. Its layered storage (L0→L1→L2 SSTables), incremental merge, and two‑layer compression can reduce storage costs by over 40% while maintaining high write performance.

Implementation Practice

During the evaluation, OceanBase 4.x was benchmarked against DuckDB, MySQL, and StarRocks in both TP (transaction processing) and AP (analytical processing) scenarios. In a high‑frequency aggregation workload, OceanBase’s JOIN aggregation was 81.5× faster than DuckDB, while DuckDB showed a modest 1.3× advantage in pure column‑store large‑list filtering. Detailed benchmark data are illustrated in the accompanying charts.

DBA Validation and Performance Testing

High Concurrency Support: At 200 concurrent connections, OceanBase’s row‑column mixed storage achieved 13,076.91 TPS.

Efficient Read/Write: In read‑only mode, 100 QPS yielded ~7,230 TPS, demonstrating strong OLAP capability.

Resource Utilization: CPU usage stayed reasonable (e.g., 63.73% at 200 concurrency for row‑store tables).

Flexible Storage Engine: Supports row, column, and hybrid storage, allowing optimal mode selection per workload.

Complex queries (SQL1 and SQL2) were used to compare MySQL and OceanBase. In MySQL, execution times were 1.3 s and 3.9 s respectively; OceanBase reduced them to 0.01 s and 0.02 s—a 130‑200× speedup—through plan caching, partition pruning, column‑store index optimization, multi‑core parallel execution, and hint‑driven algorithm selection.

select count(distinct(a.field_A)) as count,
SUM(CASE WHEN b.`field_B` IN (0,1,3,7) THEN 1 ELSE 0 END) AS `total_cnt`,
SUM(CASE WHEN b.`field_B` IN (0) THEN 1 ELSE 0 END) AS `todo`,
SUM(CASE WHEN b.`field_B` IN (1,7) THEN 1 ELSE 0 END) AS `done`,
SUM(CASE WHEN b.`field_B` IN (3) THEN 1 ELSE 0 END) AS `auto`
from table_A a join table_B b on a.field_A = b.field_C
and b.`field_B` IN (0,1,3,7) and b.create_time >= {START_TS} and b.create_time < {END_TS}
where a.is_deleted in (0)

select a.*, SUM(CASE WHEN b.`field_B` IN (0,1,3,7) THEN 1 ELSE 0 END) AS `totalCount`,
SUM(CASE WHEN b.`field_B` IN (0) THEN 1 ELSE 0 END) AS `amount`,
SUM(CASE WHEN b.`field_B` IN (1,7) THEN 1 ELSE 0 END) AS `done`,
SUM(CASE WHEN b.`field_B` IN (3) THEN 1 ELSE 0 END) AS `auto`,
sum(IF(b.create_time >= {START_TS} and b.field_B = 0, 1, 0)) as amount_current
from table_A a join table_B b on a.field_A = b.field_C
and b.`field_B` IN (0,1,3,7) and b.create_time >= {START_TS} and b.create_time < {END_TS}
where a.is_deleted in (0) group by field_A order by amount desc limit 0,10

Business POC

Before full rollout, the DBA team piloted OceanBase on an internal AP reporting workload. SQL execution times improved 10–30×, eliminating long‑running queries (>30 s) and reducing resource contention and lock wait issues. Materialized views and row‑column hybrid storage were leveraged to achieve these gains.

Migration Challenges and Solutions

Key challenges included SQL syntax differences, transaction isolation nuances, auto‑increment handling, and compatibility with existing tools. Solutions involved parameter tuning, binding execution plans, staggered materialized view refreshes, and strict change‑control processes for new features. Operationally, the team shifted from instance‑level to tenant‑ and cluster‑level management, adopting OCP, OMS, diag, and ODC tooling.

Compatibility Issues: Adjusted SQL dialect and isolation levels to align with OceanBase.

Feature Maturity: Established rigorous admission, monitoring, and fault‑injection practices for emerging capabilities such as real‑time materialized views.

Team Capability Building

The DBA team instituted a three‑tier training pathway—basic training, deep practice, and certification (OBCP)—to build expertise in OceanBase architecture and performance tuning. Knowledge was codified in internal documentation and shared via workshops.

Future Planning and Architecture Evolution

Roadmap spans three phases: short‑term (0.5‑1 yr) to solidify foundations and SOPs; mid‑term (1‑2 yr) to introduce OBKV, vector search, and multi‑zone deployment; long‑term (2‑3 yr) to build a unified multi‑model data platform covering 90 % of data scenarios. The vision includes AI‑ops for anomaly detection, root‑cause analysis, capacity prediction, and automated tuning, targeting a 50 % TCO reduction over three years and sub‑day business‑onboarding.

Overall, the migration delivered an 88.3 % reduction in average SQL latency, a 43 % total cost saving (primarily storage), and a streamlined, highly available architecture supporting both TP and AP workloads.