How OceanBase Partitioning Supercharged KPOS Report Performance

1. Background

In the past 2‑3 years, KPOS’s merchant base and order volume grew severalfold, leading to performance bottlenecks when serving large‑scale chain merchants with data‑intensive reports.

Database performance: introduced parallelism, optimized indexes, and data sharding.

Asynchronous processing: generated heavy reports via background task queues.

Report pre‑generation: cached frequently used reports for fast retrieval.

These optimizations now support nearly ten million daily orders, but further growth still pressures the system.

2. Current Situation

2.1 Business Situation

The reporting service connects to the database (see diagram) and experiences high QPS/TPS during peak periods.

2.2 Database Architecture

The cluster is deployed across two zones (A and B). The primary zone (A) provides read‑write capability, while the backup zone (B) offers read‑only access.

2.3 Query Performance

70% of traffic is write‑heavy; most queries finish within seconds, but month‑range queries for large chain merchants can take 5‑10 seconds.

2.4 OceanBase 3.2 Issues

SQL execution plans deviate.

Large‑table index changes are very slow.

3. Technical Goals

3.1 Performance

Reduce the maximum one‑month query time for 1,000 stores from 5 seconds to under 2 seconds.

3.2 Stability

Keep CPU usage below 65% even if the number of merchants doubles.

4. Why Partition

4.1 Improve Query Efficiency

Partitioning spreads data across multiple partitions, shrinking the amount each query scans.

4.2 Optimize Resource Utilization

Parallel execution across partitions better leverages multi‑core CPUs.

4.3 Support Incremental Data

Time‑based or attribute‑based partitions simplify incremental loads and reduce full‑table scans.

4.4 Simplify Maintenance & Backup

Partitions enable targeted maintenance, faster DDL (30%+ improvement), and flexible backup strategies.

5. Technical Terms

Partition : logical split of a table or index.

Replica : full copy of data for redundancy and high availability.

Zone : logical data‑center region in OceanBase.

Unit : resource allocation unit (CPU, memory, disk) within a cluster.

Dump : materialization of MemTable per tenant/observer.

Merge : global snapshot that freezes all MemStores.

6. Solution Selection

6.1 Deployment Options

Three common patterns:

Multi‑zone : three nodes in three different zones for cross‑zone disaster recovery.

Dual‑zone : two nodes in two zones plus a log‑only node.

Single‑zone : all nodes in one zone, offering only host‑level failover.

Each option’s node count, replica distribution, and cost are summarized in the original table (omitted for brevity).

6.2 Chosen Deployment

The final design uses a dual‑zone 1‑1‑1 layout with four 30C machines handling writes, balancing performance and cost.

7. Partition Count & Key Selection

7.1 Partition Count

Factors include data volume, growth rate, query patterns, resource limits, and concurrency requirements.

7.2 Partition Key

Choose a column that appears frequently in WHERE clauses, distributes data evenly, aligns with growth patterns, and matches business logic (e.g., timestamp, region code, or UUID).

Timestamp/date for time‑series data.

Business attribute such as store ID or region.

Unique identifier (UUID, user ID) for uniform distribution.

8. Implementation

8.1 Parameter Tuning

Key parameters were adjusted to lower CPU load:

parallel_servers_target = 512

_enable_calc_cost_for_range = False

_enable_in_range_optimization = True

optimizer_index_cost_adj = 10

_mini_merge_concurrency = 2

minor_merge_concurrency = 4

freeze_trigger_percentage = 50

8.2 Data Migration

Used the OMS migration tool, supporting source types such as RDS, PolarDB MySQL, VPC‑hosted databases, Database Gateway, public‑IP databases, OceanBase MySQL tenants, and OceanBase Oracle tenants.

8.3 Dual Write

Application writes to both source and target databases within a transaction, with a background verification task ensuring consistency.

8.4 Phased Switch & Rollback

Five stages:

Read verification – low risk, 1‑minute rollback.

Write verification with dual write – low risk, monitor load.

Read traffic cut‑over to new cluster – low risk, 1‑minute rollback.

Write traffic cut‑over – higher risk, 5‑minute rollback.

Old cluster cut‑off – highest risk, no rollback.

9. Benefits

SQL execution time reduced from 9.27 s to 3.31 s; multi‑store IN aggregation from 3.2 s to 1.9 s.

Index plan deviation improved by over 80 %.

Index creation on partitioned tables became >50 % faster.

10. Issues Encountered

OMS incremental phase slowed by DELETE‑splitting.

SQL without partition key caused timeouts; added monitoring.

Hard‑coded index hints needed to avoid full scans.

Nested‑loop joins on large tables were replaced with hash‑join hints.

Cross‑zone query routing increased latency; mitigated by grouping tables on the same node.

11. Future Scaling

11.1 Horizontal Expansion

Steps: add new servers per zone, start observer, execute alter system add server, increase unit count with

alter resource pool <pool_name> unit num = <larger_number>

, and let OceanBase rebalance data.

11.2 Vertical Expansion

Upgrade node specifications (e.g., to 62 C) and migrate units using alter system add server followed by data migration.

11.3 Primary Zone Dispersion

Command:

ALTER TENANT <tenant_name> PRIMARY_ZONE='zone1,zone2,zone3'

triggers automatic rebalancing across zones.

12. Conclusion

Partitioning eliminated most performance bottlenecks and delivered linear scalability for the next 3‑5 years, though a few large‑customer queries still require further tuning in upcoming OceanBase 4.x releases.