跪拜 Guibai
← Back to the summary

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

1. Background

Dewu is a globally leading next-generation trendy lifestyle platform integrating authentic trendy e-commerce and a trendy lifestyle community. Complex business scenarios and the pursuit of ultimate user experience have placed higher demands on database operations and selection.

Currently, Dewu's online database services cover multiple categories including MySQL, TiDB, MongoDB, HBase, DuckDB, ClickHouse / StarRocks, and vector databases, supporting core transactions, operational analysis, AI algorithms, communities, and other business scenarios.

Multi-category databases face operational challenges such as high operational complexity, difficult R&D selection, complex architecture, and high resource costs. At the same time, business requirements for database performance, availability, and cost are gradually increasing. During continuous database research and selection, OceanBase, as a multi-model database, gradually entered the field of vision, and the 4.x version in particular aroused great interest from the DBA team and the R&D team.

2. Multi-Model Database OceanBase Selection

OceanBase implements multi-replica strong synchronization based on the Paxos protocol, supporting multiple deployment modes from a single data center to three locations across five centers. It enables sub-second failover for any replica, with RPO = 0 and RTO < 8s. It also supports multi-active and unitized deployment, enabling simultaneous cross-region writes and flexible cross-cloud expansion, with millisecond-level synchronization latency ensuring data consistency. Under the increasingly urgent business demand for availability zone-level high availability, OceanBase's multi-active architecture provides comprehensive high availability guarantees from disaster recovery to compliance. Overall, OceanBase's capabilities are highly aligned with the company's business scenarios and pain points:

Flexible Architecture: The integrated standalone-distributed capability allows scattered small MySQL businesses to start from the smallest scale and seamlessly scale to distributed clusters as they grow. At the same time, the multi-tenant mechanism allocates resources on demand, solving the pain points of difficult integration for small businesses and the need for migration and switching during expansion, achieving an optimal balance of cost and elasticity.

Cost Reduction: Through tiered compaction, incremental merging, and two-layer compression, storage costs are significantly compressed (reduction can reach 40%+) while ensuring high-performance writes, solving the pain points of high storage costs and severe write amplification in MySQL-like businesses.

High Availability: The native multi-active architecture, based on the Paxos protocol, supports sub-second failover for any replica, RPO = 0, RTO < 8s, and cross-region multi-active writes, meeting stringent business requirements for availability zone-level high availability and solving the pain points of slow disaster recovery switching, weak cross-region data consistency, and complex compliance deployment.

To meet business AP requirements, categories like DuckDB / HBase were added for comparison and validation; while focusing on TP / AP scenarios for implementation and undertaking business from components like MySQL and StarRocks, in a high-frequency aggregation SQL scenario in a certain business production environment, OceanBase's overall performance was better than DuckDB (won 4 out of 6 tests), among which JOIN aggregation queries were 81.5 times faster, fully demonstrating the advantages of the row-column hybrid storage architecture; DuckDB had a slight advantage in pure columnar large table filtering scenarios (1.3x). Besides DuckDB, a preliminary performance comparison with HBase was also conducted, but the results were not as expected (due to differences in IO capability caused by disk types; OceanBase was deployed using the same disk environment, with disk IO latency around 2~3ms during idle runs. If SSD disks were used for separate disk deployment, performance would significantly improve). Follow-up will involve tracking new version optimizations and co-building with the community, continuing research and solution optimization. Detailed test validation data is as follows:

3. Implementation Practice

Dewu's database selection is cautious and requires high stability. Recently, with the formulation of various strategic goals such as multi-cloud, AI, and vectors, OceanBase's advantages in stability, performance, and cost have gradually become prominent, and verification and promotion based on OceanBase have gradually unfolded.

DBA Verification and Evaluation

Before actual business promotion, the DBA team conducted detailed stress test verification. Based on different concurrency levels and row/column storage scenarios, OceanBase demonstrated good performance levels.

Stress test results and conclusions refer to the figure below

Complex queries are a key focus of database validation. During the verification process, the DBA team also deeply compared the performance of MySQL and OceanBase in complex queries, such as aggregation queries, pagination sorting queries, and other scenarios; SQL1 and SQL2 below are used to obtain the execution statistics of SQL audits and pagination sorting query details within a certain period, respectively;

SQL1

select count(distinct(a.field_A)) as count, SUM(CASE WHEN b.field_BIN (0,1,3,7) THEN 1 ELSE 0 END) AStotal_cnt, SUM(CASE WHEN b.field_BIN (0) THEN 1 ELSE 0 END) AStodo, SUM(CASE WHEN b.field_BIN (1,7) THEN 1 ELSE 0 END) ASdone, SUM(CASE WHEN b.field_BIN (3) THEN 1 ELSE 0 END) ASauto 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_BIN (0,1,3,7) THEN 1 ELSE 0 END) AS totalCount , SUM(CASE WHEN b.field_BIN (0) THEN 1 ELSE 0 END) ASamount, SUM(CASE WHEN b.field_BIN (1,7) THEN 1 ELSE 0 END) ASdone, SUM(CASE WHEN b.field_BIN (3) THEN 1 ELSE 0 END) ASauto, 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 two common complex query SQLs. Even with the execution plan optimized to the best in MySQL, the execution time still required 1.3s and 3.9s. The same SQLs were tested in OceanBase, and after optimization and comparison, the performance improvement effect was significant: Through progressive optimization across 5 dimensions, the execution time of SQL2 was reduced from 4s to 0.02s, an overall performance improvement of about 200 times; SQL1 was reduced from 1.3s to 0.01s, an improvement of about 130 times. The figure below details the ideas behind these optimizations:

OceanBase compresses the execution overhead of complex SQL across the entire chain from "plan generation → data scanning → index selection → execution scheduling → algorithm decision" through five major features: plan cache reuse, partition pruning to reduce scanning, column-store indexes breaking the leftmost prefix bottleneck, multi-core parallel execution acceleration, and Hint-specified algorithms.

It should be pointed out that OceanBase provides a rich set of execution plan Hints, allowing DBAs to directly specify algorithms in SQL to precisely intervene in the generation of execution plans. OceanBase's execution plan Hints are not daily tuning tools but a fallback intervention capability when the optimizer deviates—when production SQL suddenly degrades, statistics are inaccurate, or complex join conditions cause the wrong algorithm to be chosen, DBAs can directly specify the algorithm in SQL to correct the execution plan without rewriting the SQL. This capability is not available in MySQL.

The figure below is the team's detailed breakdown and analysis based on OceanBase SQL query plans and OceanBase SQL Hints.

Business POC

Before formally promoting to business, OceanBase was tried internally within the DBA team. In the DBA internal AP report business pilot, SQL execution speed improved by an average of 10 to 30 times. The DBA report business had always been based on MySQL, with the problem of long execution times for complex queries, most exceeding 1 second, and some even taking over 30 seconds to execute successfully. At the same time, complex queries brought operational issues such as resource contention and lock waits.

The DBA internal report business was fully migrated to OceanBase, utilizing OceanBase features like materialized views and row-column hybrid storage. SQL execution speed improved by an average of 10 to 30 times. At the same time, through chaos engineering, the team verified OceanBase's mitigation measures under various abnormal scenarios and formed operational SOPs, accumulating OceanBase operational experience and boosting the DBA team's confidence in implementing OceanBase for business.

The First Business to "Eat Crab"

After accumulating practical experience through internal DBA trials, we conducted a comprehensive evaluation of a certain business team's system. This team managed multiple MySQL clusters, totaling hundreds of CPU cores and TB-level storage, with the following business pain points:

Based on the actual pain points currently faced by the business team, the DBA team constructed a comprehensive migration plan, covering platform access, resource planning, architecture design, cluster configuration, migration changes, and other aspects. The overall migration flowchart is as follows:

After the overall business migration was completed, the database architecture was upgraded, the average SQL execution time decreased by 88.3%, business timeout interfaces were cleared to zero, and overall costs were reduced by 43%.

The architecture is clear and concise, moving from multiple MySQL clusters to 1 OB cluster with multi-tenant isolation; three hosts carry the team's overall business while simultaneously considering high availability and TP/AP business resource isolation. The underlying hosts of the cluster are cloud ECS instances, with three Zones deployed in two availability zones. Availability Zone A has two Zones for read-write business, and Availability Zone B has one Zone for read-only business. Each Zone consists of several computing nodes. Some redundant CPU resources are also reserved in the cluster. When a tenant experiences a CPU alert, the redundant CPU can complete the tenant's CPU expansion in seconds. The overall architecture is as follows:

Average SQL execution time decreased by 88.3%, business timeout interfaces were cleared to zero, performance improved by 8.6 times, and aggregation/range query response times dropped to within 1% compared to MySQL. Taking a certain R&D efficiency platform as an example, the average execution time of AP-related SQL dropped from 6.87s to 0.8s, a performance improvement of about 8.6 times, with an 88% decrease in time consumption. Detailed data is as follows:

Cost benefits are significant, with an overall decrease of 43% compared to MySQL, mainly reflected in storage. After migrating from MySQL to OceanBase, the compression rate exceeded 80%, and in simple field scenarios, the compression ratio could reach over 10:1. In terms of resource utilization, based on OceanBase's unique multi-tenant PrimaryZone distribution + CPU overselling, the cluster ECS CPU utilization was increased to 100%, far exceeding the 50% of the MySQL primary-standby architecture.

The First Business to Undergo a "Major Overhaul"

Database foundation restructuring: After gaining experience from the first business, we looked for new "battlefields" and learned that another business team currently had immediate pain points:

After comprehensive POC validation and testing, OceanBase effectively solved the current problems, especially the architectural pain points. While carrying MySQL business, its compression rate and performance were better than StarRocks. After efficient collaboration with the R&D business, the current architecture underwent a "major overhaul". Currently, part of the StarRocks traffic has been switched to OceanBase for POC, and the operations B-end has fully migrated to OceanBase. The overall effect exceeded expectations.

More migration benefits and comparison data refer to the figure below

4. Problems Encountered During Implementation and Solutions

Currently, the report business and some TP businesses have completed the migration to OceanBase. Although it brings dual benefits of performance and cost, the migration process also encountered many problems and challenges, including performance issues and compatibility issues.

Migration Challenges

In the practice of OceanBase's latest LTS version 4.4.2, attention must be paid to differences from MySQL in SQL syntax, transaction isolation levels, auto-increment column implementation, etc. Compatibility issues are resolved through business adaptation or parameter adjustments. In terms of performance, means such as binding execution plans and staggering materialized view refreshes effectively solved bottlenecks like AP queries going off-track and materialized view refresh getting stuck.

More importantly, when migrating from traditional databases like MySQL to OceanBase, introducing new features requires special care, because new features and functionalities are still being perfected, and inevitably have scenario limitations or even functional conflicts, such as the conflict between real-time materialized views and DDL encountered here. When introducing new features, strict access auditing, monitoring and alerting, and fault drill mechanisms must be established. Comprehensive evaluation should be conducted before going live to avoid unnecessary impact on the business.

Under the premise of ensuring business stability and availability, obtaining dual improvements in performance and cost through migrating to OceanBase, especially when entering the deep-water zone and promoting to core businesses in the future, there are still many practical problems to be solved and experiences to be summarized.

The following are the problems encountered and summarized during the migration process, such as conflicts between real-time materialized views and DDL, SQL syntax incompatibility, user connection string escaping, etc.:

Operational System Transformation

The operational mode shifted from instance-level to tenant-level + cluster-level, fully adapting monitoring, backup, and capacity planning to OceanBase's distributed characteristics. The toolchain was also upgraded accordingly, switching from the cloud MySQL "Console + Data Sync Tool + Database Diagnostic Tool + Self-developed DBA Management Platform" to OceanBase's "OCP + OMS + diag + ODC / Self-developed DBA Management Platform" system, achieving full-chain coverage of management, migration, diagnosis, and development.

The DBA team also organized the OceanBase knowledge framework:

Team Capability Building

Through a three-layer path of "Basic Training — Deep Practice — Certification Cultivation", the DBA team systematically improved practical capabilities from OceanBase architecture principles to performance tuning. By achieving OBCP certification goals, a systematic OceanBase knowledge framework has been established, and dedicated OceanBase operational personnel have been trained. At the same time, through technical sharing, project-based hands-on mentoring, and internal knowledge bases, a knowledge transfer closed loop of "Sharing — Practice — Accumulation" has been formed, continuously enhancing team capabilities.

5. Future Planning and Architecture Evolution

In the future, OceanBase's planning within the team will gradually enter the core and deep-water zones, while empowering business efficiency from multiple dimensions such as cost, performance, efficiency, AI intelligence, and AI Agent foundation.

It is roughly divided into three phases: Short-term (0.5-1 year) to consolidate the foundation, establish technical support, standard SOPs, and emergency drill systems; Mid-term (1-2 years) to introduce OBKV for business scenarios, launch vector retrieval, complete multi-cloud and multi-availability zone construction, and attract core new businesses; Long-term (2-3 years) to build a unified multi-model data platform, covering 90% of data scenarios.

Focusing on the AIOps direction, from anomaly detection, root cause analysis, capacity prediction to automatic tuning, build intelligent full-chain operational capabilities to achieve automation and intelligence in performance warning, fault self-healing, and resource planning. Taking the OceanBase migration as an opportunity, achieve four major benefits of cost, agility, innovation, and risk: reduce TCO by 50% within 3 years, shorten business launch preparation to within 1 day, cover new scenarios such as real-time analysis and personalized recommendations, and achieve second-level fault recovery and zero data loss. Continuously promote the comprehensive implementation of OceanBase, pay attention to and research OceanBase LakeBase and other database products oriented towards AI foundations. The team will be committed to building a multi-model, multi-state data infrastructure oriented towards the future and AI Agents, solving current business pain points and providing a solid data foundation for business innovation.

Past Reviews

1.AI UITester: A New Paradigm for AI Native UI Automation Testing | Dewu Technology

2.From Wild Code to Goal-Oriented Production: Engineering Practice of Dewu's Recommendation AI Harness | AICon Speech Compilation

3.From Forms to Agents: The AI Practice Path of Dewu Community Event Creation

4.From Tracking Requirements to Rule Assets: Hermes Agent Reconstructs Dewu's Data Warehouse Workflow

5.Giving Claude Code Self-Evolution and Memory Systems | Dewu Technology

Text / Phillis, Weiwen

Follow Dewu Technology, technical干货 updated every Thursday

If you find the article helpful, welcome to comment, forward, and like~

Reprinting without permission from Dewu Technology is strictly prohibited, otherwise legal liability will be pursued according to law.