Behind a 200x SQL Speedup: How Dewu Tuned OceanBase Execution Plans and Hints

Dewu (得物) is a leading global platform that combines authentic fashion e-commerce with a lifestyle community. After evaluating OceanBase as a multimodal database, its DBA team used plan-cache reuse, partition pruning, columnar indexes, parallel execution, and SQL Hints to cut a representative complex query from about 4 seconds to 0.02 seconds—roughly 200x—and later migrated production MySQL and StarRocks traffic with large cost and latency gains.

Background

Complex business scenarios and demanding user-experience requirements raise the bar for database selection and operations.

Dewu’s online database services already span MySQL, TiDB, MongoDB, HBase, DuckDB, ClickHouse / StarRocks, and vector databases, supporting core trading, operational analytics, AI algorithms, community, and other workloads.

A multi-engine estate creates operational complexity, makes technology selection harder for R&D teams, complicates the architecture, and increases resource costs. At the same time, the business continues to raise its expectations for performance, availability, and efficiency. Through ongoing evaluation, OceanBase emerged as a candidate multimodal database—especially the 4.x series, which drew strong interest from both the DBA and R&D teams.

Why Dewu selected OceanBase as a multimodal database

OceanBase uses the Paxos protocol for strongly consistent replication across multiple replicas. It supports deployment topologies ranging from a single data center to three regions and five data centers, with failover of any replica within seconds, RPO = 0, and RTO < 8 seconds. It also supports multi-active and unitized deployments, including concurrent writes across regions, flexible cross-cloud scaling, and millisecond-level replication latency. As zone-level high availability becomes increasingly important, OceanBase’s multi-active architecture addresses needs ranging from disaster recovery to compliance. Overall, its capabilities align closely with Dewu’s use cases and pain points:

Flexible architecture: Integrated standalone and distributed capability lets scattered small MySQL workloads start at minimum scale and grow seamlessly into a distributed cluster. Multi-tenancy allocates resources on demand, so small businesses can be consolidated without a later migration cutover, balancing cost and elasticity.

Lower cost: Hierarchical flushing, incremental merge, and two-level compression maintain high write performance while reducing storage costs by more than 40%, addressing the high storage costs and severe write amplification associated with MySQL-class systems.

• Hierarchical flushing: In-memory data is flushed through L0 → L1 → L2 SSTables, avoiding memory stalls and sustaining write throughput.
• Incremental merge: Only modified hot macroblocks are merged; unmodified data is reused, which cuts merge cost and write amplification.
• Two-level compression: Structured data is first dictionary-encoded with a hybrid row/column layout, then compressed again with a general-purpose codec, trading performance against compression ratio and cutting storage cost sharply.

High availability: Native multi-active architecture based on Paxos supports second-level failover of any replica, RPO = 0, RTO < 8s, and cross-region multi-active writes. That meets strict zone-level HA needs and addresses slow disaster-recovery cutover, weak cross-region consistency, and complex compliance deployments.

To meet AP requirements, the team also compared OceanBase with DuckDB, HBase, and similar engines. While evaluating TP/AP workloads and the migration of traffic from MySQL, StarRocks, and related components, OceanBase outperformed DuckDB in four of six tests involving frequently executed production aggregation queries. JOIN aggregation queries were up to 81.5x faster, demonstrating the value of the hybrid row-column architecture. DuckDB was slightly faster in purely columnar, large-table filtering scenarios (1.3x). The team also ran an early comparison with HBase; results fell short of expectations because of disk type and I/O performance. OceanBase ran on the same disks, whose idle I/O latency was 2–3 ms; SSDs and separate disks would likely improve the results. The team plans to track optimizations in later versions and continue working with the community. Detailed validation data follows:

Deployment experience

Dewu takes a conservative approach to database selection and sets a high bar for stability. As multi-cloud infrastructure, AI, and vector search became strategic priorities, OceanBase’s advantages in stability, performance, and cost became clearer, prompting broader validation and production rollout.

DBA validation

Before promoting OceanBase to business teams, DBAs ran detailed stress tests across concurrency levels and row/column storage modes. OceanBase showed solid performance.

• High concurrency: Across read and write modes, OceanBase delivered strong TPS and QPS. At 200 concurrent sessions, a row-store table reached 13,076.91 TPS, making it suitable for high-concurrency OLTP.
• Efficient reads and writes: In read-only and write-only modes, both row-store and column-store tables performed well. At a concurrency level of about 100, OceanBase reached roughly 7,230 TPS, sufficient for large analytical (OLAP) workloads.
• Resource utilization: CPU stayed reasonable under load. At 200 concurrency, a row-store table used 63.73% CPU, showing healthy allocation under pressure and supporting stability.
• Flexible storage engine: OceanBase supports row store, column store, and hybrid storage, so teams can pick the mode that matches the workload.

Stress-test results and conclusions:

Complex queries were a validation focus. The DBA team compared MySQL and OceanBase on aggregation, paginated sort queries, and similar cases. SQL1 and SQL2 below summarize SQL-audit execution in a time window and paginated sorted detail, respectively.

SQL1

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)

SQL2

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

These are typical complex queries. Even after the MySQL execution plans had been tuned as far as possible, the queries still took 1.3 seconds and 3.9 seconds. On OceanBase, the same SQL improved dramatically through a progressive five-part optimization: SQL2 dropped from 4 seconds to 0.02 seconds, an improvement of about 200x; SQL1 dropped from 1.3 seconds to 0.01 seconds, about 130x. The figure below illustrates the optimization process:

OceanBase reduces the cost of complex SQL across the entire execution path—from plan generation, data scanning, and index selection to execution scheduling and algorithm selection—through five capabilities: plan-cache reuse, partition pruning to reduce scans, columnar indexes that overcome leftmost-prefix limitations, multi-core parallel execution, and hints that fix specific algorithms.

OceanBase also offers a rich set of execution-plan hints, allowing DBAs to specify an algorithm in SQL and precisely influence plan generation. These hints are not intended as everyday tuning knobs; they are a fallback when the optimizer makes a poor choice because of a sudden production regression, stale statistics, or complex join predicates. A DBA can correct the plan through hints without restructuring the statement. In the team’s experience, OceanBase provided more direct control over these algorithm choices than the available MySQL mechanisms.

The figures below are the team’s detailed breakdown of OceanBase SQL plans and SQL Hints.

Business PoC

Before a broader rollout, the DBA team tested OceanBase on internal AP reporting workloads. SQL performance improved by 10–30x on average. The reports had previously run on MySQL, where complex queries often took more than 1 second and some exceeded 30 seconds, while also causing resource contention and lock waits.

After the DBA reporting workload moved to OceanBase and used materialized views plus hybrid row/column storage, average SQL speed improved 10–30x. Chaos engineering was used to validate stop-the-bleed actions in failure scenarios and to write operations SOPs, building the confidence to take OceanBase to business teams.

The first business team to adopt OceanBase

With that internal practice in hand, the DBAs assessed one business team’s systems. The team ran multiple MySQL clusters totaling about 100 CPU cores and TB-scale storage, with these pain points:

• Mixed TP and AP, frequent CPU alerts: Few read replicas, plus many complex aggregations that crowded out TP queries and kept CPU in alarm.
• Disk pressure: Some AI-related MySQL volumes grew quickly, with obvious B+Tree fragmentation; many large tables needed extra free space for DDL, so disk cost was high.
• Poor resource fit: Many low-QPS instances still wasted capacity even at the smallest SKU.
• Hard operations: Many empty long transactions and abnormal DML that the usual control plane could not diagnose quickly.

The DBA team built a full migration plan covering platform onboarding, capacity planning, architecture, cluster configuration, and change windows. The overall flow:

After the move, the database architecture was simpler. Average SQL latency fell 88.3%, timed-out business APIs went to zero, and overall cost dropped 43%.

The resulting architecture is simpler: many MySQL instances became one OceanBase cluster with multi-tenant isolation. Three hosts carry the team’s full workload while preserving high availability and TP/AP resource isolation. The cluster runs on cloud ECS across three OceanBase Zones in two availability zones: two Zones in AZ A handle reads and writes, while one Zone in AZ B is read-only. Each Zone consists of several compute nodes. Spare CPU capacity is reserved so a tenant can be scaled within seconds when CPU usage triggers an alert. The overall architecture is shown below:

Average SQL latency fell by 88.3%. API timeouts were eliminated, and performance improved by 8.6x. Aggregation and range-query response times fell to less than 1% of their MySQL levels. On one R&D efficiency platform, average AP SQL latency dropped from 6.87 seconds to 0.8 seconds—about 8.6x faster, representing an 88% reduction. Details:

Cost fell 43% versus MySQL, mostly in storage. After the move, compression exceeded 80%; simple-field cases reached 10:1 or better. On utilization, OceanBase multi-tenant PrimaryZone spreading plus CPU oversubscription raised cluster ECS CPU utilization to 100%, far above the 50% typical of MySQL primary/standby.

The first major architecture overhaul

After this initial success, the team looked for the next opportunity. Another business team faced several pressing problems:

• Classic MySQL architecture: Downstream heterogeneous replicas served B-side operational aggregations.
• Multiple engines: Operations B-side traffic had all gone to StarRocks; merchant B-side used StarRocks for large campaigns and MySQL for small ones, with sync tools moving data out of MySQL.
• Manual query routing: The application had to split point lookups from aggregations, and the split was often wrong—aggregations hitting MySQL and hurting the business.
• Large storage: StarRocks plus MySQL consumed a lot of space; consistency depended on sync tools.
• AP performance still needed work: Some components had been split to improve queries, but there was more headroom.

After a full POC, OceanBase addressed the architecture pain: it could take MySQL workloads while beating StarRocks on compression and performance. Working closely with R&D, the team overhauled the stack. StarRocks traffic was partly cut over to OceanBase for POC; operations B-side now runs entirely on OceanBase, with results better than expected.

• Architecture upgrade: From MySQL + StarRocks dual pipelines plus application-layer routing to OceanBase hybrid row/column in one system.

• No more heterogeneous synchronization: Delayed data visibility caused by synchronization tools was replaced with real-time access.
• Routing risk removed: Manual routing at the application layer was eliminated. OceanBase internally routes point lookups and aggregation queries, so routing errors no longer affect the business.
• Storage cost down: Storage fell from TB-scale to hundreds of GB—94% compression versus MySQL and 30% versus StarRocks.
• SQL much faster: OceanBase aggregation and range-query latency averaged 65% lower than StarRocks (range 10%–90%) and fell to under 1% of MySQL. High-QPS point lookups traded wins with MySQL and beat StarRocks clearly. OceanBase queries are fully real-time; StarRocks had second-level write-to-read delay.

More migration benefits and comparison data:

Deployment challenges and solutions

Reporting and some TP workloads have already moved to OceanBase. Performance and cost both improved, but the migration hit compatibility and other issues as well.

Migration challenges

When migrating to OceanBase LTS 4.4.2, teams must account for differences from MySQL in SQL syntax, isolation levels, and auto-increment behavior, addressing them through application changes or configuration parameters. For performance issues, binding execution plans and refreshing materialized views during off-peak hours resolved AP queries that chose suboptimal plans and materialized-view refreshes that stalled.

New features require extra care when migrating from MySQL-class systems because they may still be maturing and can have use-case limitations or incompatible combinations. One example in this project was a conflict between real-time materialized views and DDL. Introducing a new feature requires strict readiness reviews, monitoring and alerts, and failure drills. Thorough evaluation before go-live helps avoid unnecessary business impact.

Gaining performance and cost while keeping stability is especially important as OceanBase moves into core, deeper workloads. There is still real work to do and experience to capture.

Problems encountered during migration included conflicts between real-time materialized views and DDL, incompatible SQL syntax, and tenant connection strings that required escape characters:

Operations model change

Operations shifted from instance-level to tenant-level plus cluster-level, with monitoring, backup, and capacity planning adapted to OceanBase’s distributed design. The toolchain moved from cloud MySQL’s “console + data-sync + diagnostics + in-house DBA platform” to OceanBase’s OCP + OMS + diag + ODC / in-house DBA platform, covering control, migration, diagnosis, and development.

The DBA team also organized an OceanBase knowledge framework:

Team capability

Through a three-stage path—foundational training, hands-on practice, and certification—the DBA team strengthened its skills across OceanBase architecture and performance tuning. Achieving the OBCP certification goal established a systematic body of knowledge and trained dedicated OceanBase operators. Technical talks, project mentoring, and an internal knowledge base complete a share → practice → document loop.

Future plans and architectural evolution

OceanBase will expand into Dewu’s deeper, core workloads, with the goal of improving cost, performance, operational efficiency, AI capabilities, and the data foundation for AI agents.

The roadmap has three phases: near term (0.5–1 year)—strengthen the foundation, technical support, standard operating procedures, and emergency drills; mid term (1–2 years)—deploy OBKV and vector search, complete a multi-cloud, multi-AZ architecture, and onboard new core workloads; long term (2–3 years)—build a unified multimodal data platform covering 90% of data scenarios.

For AIOps, the team envisions a closed loop spanning anomaly detection, root-cause analysis, capacity forecasting, and automatic tuning, with performance alerts, self-healing, and intelligent resource planning. Using the OceanBase migration as a catalyst, it is targeting four outcomes across cost, agility, innovation, and risk: a 50% reduction in TCO within three years, business go-live preparation shortened to one day or less, support for real-time analytics and personalized recommendations, and recovery within seconds with zero data loss. The team will continue expanding OceanBase adoption and evaluating products such as OceanBase LakeBase as an AI foundation, with the goal of building future-ready, AI-agent-ready multimodal data infrastructure.

Welcome to join the open-source community Discord.