Experience HTAP mixed-workload scenarios

OceanBase Database adopts an integrated architecture, where the same engine and data can simultaneously support both online transaction processing (TP) and real-time analytical processing (AP) workloads. Through its integrated row-column storage technology, OceanBase can efficiently handle high-concurrency point queries and short-transaction updates on the same table, while also performing large-scale scans and aggregate analysis, eliminating the need to maintain separate OLTP and OLAP systems.

This topic covers the following experiences:

• Create an HTAP test table: Create a hybrid row-column table that maintains both row-based and columnar storage baselines for the same data.
• Experience TP workloads: Execute writes, updates, and primary key point queries on the hybrid table to experience the efficient transaction processing of the row-based path.
• Experience AP workloads: Execute complex analytical queries on the same table to experience the vectorized scanning and aggregation capabilities of the columnar storage path.
• Experience resource isolation: Configure resource groups within a tenant to observe the impact of large AP queries on TP workload and the improvement after enabling resource isolation.

Prerequisites

Before you perform the operations in this topic, ensure that the following conditions are met:

Create an HTAP test table

About row-based storage, columnar storage, and hybrid row-column storage

OceanBase Database creates tables as row-based storage tables by default. You can specify a different storage format by using the WITH COLUMN GROUP option:

A hybrid row-column storage table retains both row-based storage and columnar storage baselines. The optimizer automatically selects the access path based on query characteristics: point queries tend to follow the row-based storage path, while wide-range scans tend to follow the columnar storage path. For more information, see Use columnar storage and hybrid row-column storage.

Procedure

1. Log in to the business tenant (the root user password for the oltp_tenant tenant is empty by default. To log in, press Enter when prompted):

   obclient -h127.0.0.1 -uroot@oltp_tenant -P2881 -p -A test
   

2. Create a hybrid row-column storage table. The payment transaction business is used as an example:

   obclient [test]> CREATE TABLE payment_ledger_htap (
       txn_id       BIGINT       NOT NULL COMMENT 'Transaction ID',
       order_id     BIGINT       NOT NULL COMMENT 'Order ID',
       user_id      BIGINT       NOT NULL COMMENT 'User ID',
       pay_time     DATETIME     NOT NULL COMMENT 'Payment time',
       pay_channel  VARCHAR(32)  NOT NULL COMMENT 'Payment channel',
       txn_status   TINYINT      NOT NULL COMMENT '0 Pending payment 1 Successful 2 Failed',
       amount       DECIMAL(18,2) NOT NULL COMMENT 'Amount',
       merchant_id  BIGINT       NOT NULL COMMENT 'Merchant ID',
       PRIMARY KEY (order_id, pay_time, txn_id)
   )
   PARTITION BY HASH(order_id) PARTITIONS 4
   WITH COLUMN GROUP(all columns, each column);
   

   Note

   The WITH COLUMN GROUP(all columns, each column) statement declares both row-based storage and columnar storage groups, creating two redundant baselines. The optimizer can choose the row-based storage path for point queries and the columnar storage path for wide-range scans based on cost.

3. Write test data:

   obclient [test]> INSERT INTO payment_ledger_htap (txn_id, order_id, user_id, pay_time, pay_channel, txn_status, amount, merchant_id)
   VALUES
       (1, 1001, 100, '2025-01-15 10:30:00', 'ALIPAY', 1, 128.50, 2001),
       (2, 1002, 101, '2025-01-15 11:00:00', 'WECHAT', 1, 256.00, 2002),
       (3, 1003, 100, '2025-01-15 14:20:00', 'ALIPAY', 2, 99.90,  2001),
       (4, 1004, 102, '2025-01-15 16:45:00', 'UNIONPAY', 1, 1024.00, 2003),
       (5, 1005, 101, '2025-01-16 09:00:00', 'WECHAT', 1, 56.80, 2002);
   

4. (Optional) Write more data in batches. The sample above contains only 5 rows, which is enough to demonstrate the process quickly. When the data volume is small, the optimizer does not choose the columnar storage path for analytical queries, so some effects in the following steps are hard to observe. To fully experience the columnar storage path for analytical queries, run the following statement to write about 1 million rows of test data in batches (the actual time consumed depends on machine performance).

   A single SQL statement times out after 10 seconds by default, and the recursion depth of a recursive CTE is limited to 1,000 by default. Because batch writes are time-consuming and involve a large data volume, both limits must be raised first. The following statement raises the timeout to 60 seconds (increase it further if ERROR 4012 still occurs) and the recursion depth to 1,100,000:

   obclient [test]> SET SESSION ob_query_timeout = 60000000, cte_max_recursion_depth = 1100000;
   

   Execute the following statement to write data in batches:

   obclient [test]> INSERT INTO payment_ledger_htap
                   WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 1000000)
                   SELECT n+1000000, 3000000+n, 100+(n%50),
                          DATE_ADD('2025-01-15 00:00:00', INTERVAL (n%20000) MINUTE),
                          ELT(1+(n%3),'ALIPAY','WECHAT','UNIONPAY'), 1+(n%2),
                          ROUND(RAND()*1000,2), 2000+(n%10)
                   FROM seq WHERE n <= 1000000;
   

5. Perform a major compaction and collect statistics to improve columnar storage query performance:

   -- Trigger a major compaction in the sys tenant.
   obclient [oceanbase]> ALTER SYSTEM MAJOR FREEZE;
   

   -- Collect statistics in the business tenant.
   obclient [test]> CALL DBMS_STATS.GATHER_SCHEMA_STATS('test');
   

   Note

   A major compaction is an internal operation in OceanBase Database that organizes incremental data into baseline data. Newly written data must go through a major compaction to enter the baseline layer before it can be efficiently read by the columnar storage path. You can confirm the major compaction status by querying the oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION view. When STATUS shows IDLE, the major compaction is complete. The actual duration depends on the data volume. For detailed information about how to collect statistics using the DBMS_STATS system package, see Manually collect statistics.

Experience TP workloads

In a hybrid row-column table, point queries and frequent short-transaction updates are processed via the row-based path, providing transaction processing performance comparable to traditional row-based tables.

Procedure

By default, obclient displays the execution time after query results, for example, 1 row in set (0.00 sec), so no additional timing is needed. The query response time (RT) for each step below can be directly observed from this display.

1. Write data (simulate adding payment transaction records):

   obclient [test]> INSERT INTO payment_ledger_htap (txn_id, order_id, user_id, pay_time, pay_channel, txn_status, amount, merchant_id)
   VALUES (6, 1006, 103, '2025-01-16 10:00:00', 'ALIPAY', 1, 388.00, 2001);
   

2. Update data (simulate changing payment status):

   obclient [test]> UPDATE payment_ledger_htap SET txn_status = 1 WHERE order_id = 1003;
   

3. Primary key point query (simulate querying transaction records by order number):

   obclient [test]> SELECT txn_id, pay_time, amount, pay_channel
                   FROM payment_ledger_htap
                   WHERE order_id = 1001 AND txn_status = 1
                   ORDER BY pay_time DESC
                   LIMIT 1;
   

   For a primary key point query, the response time is typically in the millisecond or even sub-millisecond range, demonstrating the efficiency of point queries via the row-based path.

   Note

   The preceding query tends to use the row-based path to quickly locate data via the primary key. You can use EXPLAIN to view the execution plan and confirm the access path.

Experience AP workloads

On the same hybrid row-column table, complex analytical queries tend to use the columnar storage path, scanning only the involved columns and leveraging the vectorized execution engine to accelerate aggregation and scanning.

Procedure

1. Aggregate payment amounts by channel (aggregation analysis query):

   obclient [test]> SELECT pay_channel, SUM(amount) AS total_amt, COUNT(*) AS cnt
                   FROM payment_ledger_htap
                   WHERE pay_time >= '2025-01-15 00:00:00'
                     AND pay_time <  '2025-01-17 00:00:00'
                     AND txn_status = 1
                   GROUP BY pay_channel;
   

2. Summarize total payments by merchant:

   obclient [test]> SELECT merchant_id, DATE(pay_time) AS dt, SUM(amount) AS daily_amt
                   FROM payment_ledger_htap
                   WHERE pay_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                     AND txn_status = 1
                   GROUP BY merchant_id, DATE(pay_time);
   

3. Use EXPLAIN to view the execution plan and confirm if the columnar storage path is used:

   obclient [test]> EXPLAIN SELECT pay_channel, SUM(amount) FROM payment_ledger_htap
                   WHERE pay_time >= '2025-01-01' GROUP BY pay_channel;
   

   Note

   If columnar storage-related operators such as COLUMN TABLE FULL SCAN appear in the execution plan, it indicates the query uses the columnar storage path. When the data volume is small (for example, only writing the 5 rows of data in the sample), the optimizer may choose the row-based storage path (displaying TABLE FULL SCAN), which is normal. The columnar storage path will be observed after batch data writes are completed and major compaction is finished. The actual execution plan is subject to the output of the current version.

   Point queries on TP tables use the row-based storage path and typically complete in milliseconds; AP aggregation queries, however, need to scan the entire table's data, so response time increases with data volume. Thanks to columnar storage, which scans only involved columns and leverages the vectorized engine for acceleration, the same query can still maintain high execution efficiency on hybrid row-column tables.

Experience resource isolation

In HTAP scenarios, large AP queries can consume significant CPU and I/O resources, affecting concurrent TP workloads. OceanBase Database supports resource isolation within a tenant through resource groups. By distributing TP and AP workloads to different resource groups and setting upper limits for CPU and IOPS usage, it ensures that AP queries do not interfere with TP transactions.

Principle of resource isolation

Procedure

1. Create test users

Create dedicated TP and AP users in the business tenant:

obclient [test]> CREATE USER tp_user IDENTIFIED BY '******';
obclient [test]> CREATE USER ap_user IDENTIFIED BY '******';
obclient [test]> GRANT ALL PRIVILEGES ON test.* TO tp_user;
obclient [test]> GRANT SELECT ON test.* TO ap_user;

2. Create resource groups

Call the DBMS_RESOURCE_MANAGER system package to create two resource groups for TP and AP:

-- Create a TP resource group
obclient [test]> CALL DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    CONSUMER_GROUP => 'tp_group',
    COMMENT => 'TP Business Resource Group'
);

-- Create an AP resource group
obclient [test]> CALL DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    CONSUMER_GROUP => 'ap_group',
    COMMENT => 'AP Business Resource Group'
);

3. Create a resource management plan

obclient [test]> CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    PLAN => 'htap_plan',
    COMMENT => 'HTAP resource isolation plan'
);

4. Define the resource management plan content

Assign an 80% CPU cap to the TP resource group and a 30% CPU cap to the AP resource group:

-- TP resource group: 80% CPU
obclient [test]> CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN => 'htap_plan',
    GROUP_OR_SUBPLAN => 'tp_group',
    COMMENT => 'TP resource limit',
    UTILIZATION_LIMIT => 80,
    MIN_IOPS => 50,
    MAX_IOPS => 90
);

-- AP resource group: 30% CPU cap
obclient [test]> CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    PLAN => 'htap_plan',
    GROUP_OR_SUBPLAN => 'ap_group',
    COMMENT => 'AP resource limit',
    UTILIZATION_LIMIT => 30,
    MIN_IOPS => 20,
    MAX_IOPS => 60
);

5. Bind users to resource groups

Bind tp_user to tp_group and bind ap_user to ap_group:

obclient [test]> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    ATTRIBUTE => 'user',
    VALUE => 'tp_user',
    CONSUMER_GROUP => 'tp_group'
);

obclient [test]> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    ATTRIBUTE => 'user',
    VALUE => 'ap_user',
    CONSUMER_GROUP => 'ap_group'
);

6. Enable the resource management plan

obclient [test]> SET GLOBAL resource_manager_plan = 'htap_plan';

Execute the following statement to confirm successful enablement (SET GLOBAL statements only modify global values; you must use SHOW GLOBAL VARIABLES to view them in the current session):

obclient [test]> SHOW GLOBAL VARIABLES LIKE 'resource_manager_plan';

If the Value in the returned result is htap_plan, the enablement was successful. After enabling, SQL executed by tp_user will use the resource quota of tp_group, and SQL executed by ap_user will use the resource quota of ap_group.

7. Verify the resource isolation effect

Compare the response times of TP point queries with and without resource isolation to experience the effect directly. We recommend that you use two terminal windows.

Prepare two connections:

• Terminal A: Connect using tp_user:

  obclient -h127.0.0.1 -utp_user@oltp_tenant -P2881 -p -A test
  

• Terminal B: Connect using ap_user:

  obclient -h127.0.0.1 -uap_user@oltp_tenant -P2881 -p -A test
  

Each query statement executed in the terminal displays its execution time after the result (for example, N rows in set (X.XX sec)). Later, observe the resource isolation effect by comparing the changes in TP point query time in Terminal A.

Step 1: Before resource isolation is enabled

1. Temporarily disable the resource isolation plan first to ensure TP and AP share resources:

   obclient [test]> SET GLOBAL resource_manager_plan = '';
   

2. Execute a TP point query once in Terminal A and record the response time as a baseline value:

   obclient [test]> SELECT txn_id, pay_time, amount FROM payment_ledger_htap WHERE order_id = 1001;
   

   At this point, without AP load interference, the response time for a point query is typically in milliseconds.

3. Initiate an AP aggregate query in Terminal B (the larger the data volume, the longer the query time, and the more noticeable the effect):

   obclient [test]> SELECT pay_channel, SUM(amount), COUNT(*) FROM payment_ledger_htap GROUP BY pay_channel;
   

4. While the AP query is executing, return to Terminal A and repeat the TP point query to observe the response time. Since the AP query shares CPU and I/O resources with the TP transaction, when the AP query occupies a large amount of resources, the response time for the TP point query may increase significantly.

Step 2: After resource isolation is enabled

1. Re-enable the resource isolation plan:

   obclient [test]> SET GLOBAL resource_manager_plan = 'htap_plan';
   

   Execute SHOW GLOBAL VARIABLES LIKE 'resource_manager_plan'; to confirm the return value is htap_plan.

2. Initiate another AP aggregate query from Terminal B:

   obclient [test]> SELECT pay_channel, SUM(amount), COUNT(*) FROM payment_ledger_htap GROUP BY pay_channel;
   

3. During the AP query execution, return to Terminal A and repeatedly execute the TP point query to observe the response time. After resource isolation is enabled, AP queries are limited to the CPU cap of ap_group (30%), while TP transactions enjoy the resource guarantee of tp_group. The response time for TP point queries should remain stable and not show significant fluctuations due to AP queries.

Result comparison:

Regardless of whether the latency difference is obvious, you can log in to the oceanbase database as the root user of oltp_tenant (regular users do not have the privilege to query the related views) and execute the following statement to confirm that resource isolation is in effect:

obclient [oceanbase]> SELECT * FROM oceanbase.V$RSRC_PLAN;

In the returned result, if NAME is htap_plan and CPU_MANAGED is ON, it indicates that the resource management plan is enabled. You can also execute the following statement to view the resource limit details for each resource group:

obclient [oceanbase]> SELECT PLAN, GROUP_OR_SUBPLAN, UTILIZATION_LIMIT, MIN_IOPS, MAX_IOPS FROM oceanbase.DBA_OB_RSRC_DIRECTIVES;

The returned result shows the configured CPU and IOPS caps for tp_group and ap_group.

Next steps

After you complete the HTAP experience in this topic, you can explore the following:

1. Experience AI scenarios: Create vector tables and full-text indexes to experience OceanBase Database's hybrid retrieval capabilities. For details, see Experience AI scenarios.
2. Experience multi-tenancy features: Create multiple business tenants to experience cluster-level resource isolation and elastic scaling. For details, see Experience multi-tenancy and resource isolation.
3. Deepen understanding of AP capabilities: Explore core OceanBase AP features such as columnstore indexes, materialized views, and parallel queries. For details, see Overview of OceanBase AP.

References

• For a complete description of columnstore and hybrid row-column storage, see Use columnstore and hybrid row-column storage.
• For core OceanBase AP features and application scenarios, see Overview of OceanBase AP.
• For the complete configuration method and considerations of resource isolation, see Configure resource isolation within a tenant.
• For basic concepts and application scenarios of resource isolation, see Resource isolation overview.
• For parameters related to table creation and storage format, see default_table_store_format.