Database · Architecture · Distributed

OceanBase Cut SQL Time 200x and Storage Costs 43% at Dewu

By 得物技术 · Jul 9, 2026

Read original on juejin.cn ↗ Google Translate ↗ Alt translation

Teams running MySQL for both OLTP and ad-hoc analytics routinely hit CPU saturation, storage bloat, and fragile read-replica setups. OceanBase’s row-column hybrid engine and native multi-tenancy let a single cluster absorb those mixed workloads while cutting storage by 80–94% and removing the need for separate analytical databases and sync pipelines.

Summary

Dewu’s DBA team replaced a sprawl of MySQL, StarRocks, and DuckDB instances with OceanBase’s row-column hybrid engine. In internal benchmarks, a complex paginated aggregation query dropped from 4 seconds to 0.02 seconds after applying plan cache reuse, partition pruning, column-store indexes, parallel execution, and optimizer hints. A separate JOIN aggregation query ran 81.5x faster on OceanBase than on DuckDB.

Two production migrations delivered the headline numbers. The first consolidated multiple MySQL clusters into a single three-zone OceanBase cluster, driving average SQL latency down 88.3% and eliminating timeout errors entirely. The second ripped out a MySQL-plus-StarRocks dual-path architecture, removing a data-sync tool and manual query routing; storage shrank from several terabytes to hundreds of gigabytes, and aggregation queries against StarRocks accelerated by an average of 65%.

Migration wasn’t frictionless. Real-time materialized views conflicted with DDL, SQL syntax and connection-string escaping required adaptation, and the team had to rebuild its operational toolchain around OCP, OMS, and ODC. The roadmap now targets OBKV, vector search, and a unified multi-model platform covering 90% of data scenarios within three years.

Takeaways

— Complex SQL that took 4 seconds on MySQL ran in 0.02 seconds on OceanBase after five progressive optimizations: plan cache reuse, partition pruning, column-store indexes, parallel execution, and optimizer hints.

— A JOIN aggregation query completed 81.5x faster on OceanBase than on DuckDB; DuckDB was only 1.3x faster on pure columnar large-table filtering.

— One production migration consolidated several MySQL clusters into a single three-zone OceanBase cluster, dropping average SQL latency by 88.3% and eliminating timeout errors.

— Replacing a MySQL-plus-StarRocks dual-path architecture with OceanBase removed a data-sync tool and manual query routing, shrinking storage from multiple terabytes to hundreds of gigabytes.

— Storage compression reached 80–94% versus MySQL; simple-field tables compressed at better than 10:1.

— Overall database costs fell 43%, driven mainly by storage savings and raising CPU utilization from 50% (MySQL primary-standby) to 100% through OceanBase multi-tenant CPU overselling.

— OceanBase’s optimizer hints let DBAs inject a specific join or scan algorithm directly into a SQL statement, correcting a bad plan without rewriting application code—a capability MySQL lacks.

— Real-time materialized views conflicted with DDL operations, requiring staggered refresh schedules and careful feature gating during migration.

— The operational toolchain shifted from cloud-MySQL consoles and sync tools to OceanBase’s OCP, OMS, diag, and ODC stack, with monitoring rebuilt around tenant-level and cluster-level metrics.

Conclusions

OceanBase’s biggest lever in these migrations wasn’t raw speed but architectural simplification: collapsing a MySQL-plus-analytics-database stack into one engine eliminated whole categories of operational toil—sync lag, manual query routing, and dual-system capacity planning.

The 81.5x JOIN advantage over DuckDB is striking because DuckDB is purpose-built for embedded analytics; the result suggests OceanBase’s row-column hybrid engine can outperform a specialized column-store on mixed workloads that include joins, not just scans.

Optimizer hints are framed as a safety valve, not a tuning tool. That framing matters: it acknowledges the optimizer will sometimes choose badly and gives the DBA a direct, auditable override without rewriting SQL, which is a practical differentiator from MySQL in production incident response.

CPU overselling to 100% utilization is a double-edged signal. It demonstrates multi-tenant efficiency gains over idle primary-standby pairs, but it also implies the cluster runs with no headroom unless redundant CPU is explicitly reserved—a capacity-planning discipline that teams accustomed to over-provisioned MySQL may not have.

The real-time materialized view vs. DDL conflict is a reminder that OceanBase’s newer features still carry sharp edges. Teams adopting it should treat each 4.x feature as a candidate for a formal acceptance test suite rather than assuming MySQL-grade maturity.

Concepts & terms

Row-Column Hybrid Storage

A storage engine that maintains data in both row-oriented and column-oriented formats simultaneously, allowing the optimizer to choose the most efficient access path per query—row format for point lookups and transactional writes, column format for analytical scans and aggregations.

Optimizer Hint

A directive embedded in a SQL comment that instructs the database’s query planner to use a specific algorithm (e.g., hash join, nested-loop join) or access path, overriding the optimizer’s cost-based decision. OceanBase supports hints as a fallback for when statistics are stale or the optimizer misjudges a complex predicate.

Real-Time Materialized View

A pre-computed result set that OceanBase updates incrementally as underlying data changes, rather than on a scheduled refresh. This provides fresh aggregated data for queries but can conflict with DDL operations on the base tables.

Multi-Tenant PrimaryZone

An OceanBase mechanism that assigns each tenant’s primary read-write replica to a specific zone, then distributes different tenants’ primary zones across available zones. This spreads load evenly across the cluster instead of concentrating all writes on a single primary node.

Source: juejin.cn ↗ Google Translate ↗ Backup ↗