Configuration guide for read-write splitting in AP scenarios

Background information and objectives

In hybrid transactional and analytical processing (HTAP) scenarios, both online transaction processing (OLTP) and online analytical processing (OLAP) workloads run on the same database cluster. OLAP requests, due to their high resource consumption, might impact the performance of critical OLTP operations. Therefore, we typically use a read-write splitting strategy, routing some of the read requests to follower replicas. This approach helps reduce the resource contention caused by complex analytical computations and minimizes the impact on the response time of online transactions.

OceanBase achieves the following objectives through columnstore replicas and OceanBase Database Proxy (ODP) routing policies:

• OLTP requests: Use leader replicas to ensure high performance and data accuracy.
• AP requests: Create columnstore replicas to accelerate analytical queries and achieve resource isolation.

Version requirements: columnstore replicas are supported starting from OceanBase Database V4.3.3 and OceanBase Cloud Platform (OCP) V4.3.3.

Configuration methods

Method 1: Configure columnstore replicas (core method for AP acceleration)

Challenge:

• AP requests require high-performance analytics, but resources of OLTP leader replicas are limited.
• Rowstore replicas cannot simultaneously meet the low latency demands of OLTP and the high throughput requirements of AP requests.

OceanBase Database V4.3.3 and later allow you to create columnstore replicas for AP requests.

Deploy columnstore replicas and configure ODP in an OceanBase cluster

Method 1: Create a new tenant with columnstore replicas

1. Configure resource units and resource pools. Example SQL statements:

   CREATE RESOURCE UNIT unit1, MAX_CPU=5, MIN_CPU=5, MEMORY_SIZE= '32G', MAX_IOPS=10000, MIN_IOPS=5000, LOG_DISK_SIZE=5301023539200;
   CREATE RESOURCE POOL pool1 UNIT='unit1', UNIT_NUM = 1, ZONE_LIST = ('zone1','zone2','zone3');
   

2. Create a tenant with specified locality. Example SQL statement:

   CREATE TENANT tenant_c LOCALITY = 'F@zone1,F@zone2,C@zone3', primary_zone='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool1') SET ob_tcp_invited_nodes = '%';
   

3. Deploy and configure ODP.

For more information about how to deploy columnstore replicas, see Use columnstore replicas.

Method 2: Add columnstore replicas to an existing tenant

1. Add a new zone to the cluster and add nodes to the new zone. For more information, see Add a zone and Add a node.

2. Create new resource units and resource pools. Example SQL statements:

   CREATE RESOURCE UNIT unit2, MAX_CPU=5, MIN_CPU=5, MEMORY_SIZE= '32G', MAX_IOPS=10000, MIN_IOPS=5000, LOG_DISK_SIZE=5301023539200;
   CREATE RESOURCE POOL pool2 UNIT = 'unit2', UNIT_NUM = 1, ZONE_LIST = ('zone4');
   ALTER TENANT tenant_c RESOURCE_POOL_LIST = ('pool1','pool2');
   

3. Specify new locality for the tenant. Example SQL statement:

   ALTER TENANT tenant_c LOCALITY = 'F@zone1,F@zone2,F@zone3,C@zone4';s
   

4. Deploy and configure ODP.

For more information about how to deploy columnstore replicas, see Use columnstore replicas.

Configure a read-only columnstore replica through OCP

You can also configure a read-only columnstore replica in the OCP console in a more convenient and simpler way.

1. Add a read-only columnstore replica:

   • Case 1: Add a zone to an existing cluster

     In the OCP console, go to the corresponding cluster and add a new zone. You need to fill in the information of the new zone, select the server, and set the configurations.

   • Case 2: Add a replica to a tenant where a read-only columnstore replica needs to be created.

     After adding a zone, go to the tenant of the corresponding cluster and add a read-only columnstore replica for the tenant.

2. Add an independent ODP: If required, create an independent ODP exclusively for AP requests.

3. Configure read-write splitting: After the read-only columnstore replica is added, configure the read-write splitting strategy. For more information, see ODP routing best practices.

For more information about how to deploy a dedicated ODP and handle weak read requests, see Use columnstore replicas.

Method 2: Enable weak-consistency read (prerequisites for routing AP requests)

Challenge: AP requests need to access follower replicas, but the default routing policy is strong consistency.

AP requests must access follower or columnstore replicas through weak consistency reads. The configuration method is as follows:

-- Global session setting (recommended)  
SET GLOBAL OB_READ_CONSISTENCY='WEAK';  

-- Alternatively, you can configure this parameter at the ODP level:  
ALTER PROXYCONFIG SET OBPROXY_READ_CONSISTENCY=1;  -- 1 indicates weak consistency.

Method 3: Configure resource isolation for large queries

Challenge: Large AP queries may occupy excessive CPU resources, resulting in latency in OLTP requests.

OceanBase Database uses parameters to mange the resource consumption of large queries, preventing them from blocking OLTP workloads and reducing their impact on smaller queries. By configuring these parameters, you can effectively limit the resource usage of AP queries to avoid blocking OLTP operations. The configuration steps are as follows:

ALTER SYSTEM SET LARGE_QUERY_THRESHOLD = '10s';     -- Queries exceeding 10 seconds are considered large queries (default is 5 seconds)
ALTER SYSTEM SET LARGE_QUERY_WORKER_PERCENTAGE = 25; -- Large queries can only use 25% of CPU resources (default is 30%)

OceanBase Database limits the number of active worker threads available to large queries within a tenant, thereby restricting the maximum CPU resources they can use. This ensures that sufficient CPU resources remain available for executing OLTP workloads, such as small transactional operations. As a result, OLTP workloads, which are highly sensitive to response times, are guaranteed enough CPU resources to execute efficiently and without delay.

It is important to note, however, that while OceanBase Database can allocate resources between large queries and OLTP workloads, the large_query_threshold parameter should be configured within a reasonable range. Setting it too high may allow large queries to consume excessive system CPU resources, potentially causing slower OLTP response times or even queue backlog issues.

Typical configuration examples

Example 1: Dedicated routing for columnstore replicas

Scenario: Analyze user behavior data after a major e-commerce promotion.

Procedure:

1. Create a zone (zone5) with columnstore replicas and bind it to the dedicated ODP for analytical requests.

2. Set the routing policy:

   ALTER PROXYCONFIG SET PROXY_ROUTE_POLICY="follower_only";  
   

3. Verify the routing configuration:

   EXPLAIN SELECT * FROM analysis_table WHERE region = 'east';  
   -- Confirm that the data source in the plan is zone5.  
   

Advantages:

• Enhances aggregation calculations (like SUM and GROUP BY) using columnar storage.
• Completely isolates OLAP and OLTP resources.

Example 2: Proximity-based routing for AP requests across data centers

Scenario: In a cross-region deployment, AP requests are routed to the nearest columnstore replica.

Procedure:

1. Bind the zone to a geographic location:

   ALTER SYSTEM MODIFY ZONE "zone5" SET REGION="SHANGHAI", IDC="idc5";  -- The data center where the rowstore replica is located.  
   

2. Configure the routing preference:

   ALTER PROXYCONFIG SET PROXY_ROUTE_POLICY="follower_first";  
   

Advantages:

• AP requests from the Shanghai data center are routed to the columnstore replica in zone5 first, minimizing cross-region latency.

Appendix 1: Description of key parameters

Appendix 2: ODP routing policies and LDC basics

Random routing policy

ODP supports three routing policies. To ensure that read requests with weak consistency are prioritized to the follower replica, you need to set the proxy_route_policy parameter for ODP.

• If you set it to FOLLOWER_FIRST, the request is preferentially sent to the follower replica. If the follower replica is unavailable, the request is sent to the leader replica.

  ALTER PROXYCONFIG SET PROXY_ROUTE_POLICY="follower_first";  
  

- If you set it to `FOLLOWER_ONLY`, the request is randomly sent to the follower replica. If the follower replica is unavailable, an error is returned.

 ```sql  
 ALTER PROXYCONFIG SET PROXY_ROUTE_POLICY="follower_only";  

LDC configuration (optional)

In typical AP scenarios, columnstore replicas are created separately, and LDC routing does not need to be configured. If read-write splitting is implemented in the cluster, refer to this section to learn how to optimize routing based on geographic location:

1. Set the zone's geographic location:

   ALTER SYSTEM MODIFY ZONE "zone5" SET REGION="SHANGHAI", IDC="idc5";  
   

2. Bind the LDC information of ODP:

   Method 1: Specify the LDC information by using the -o parameter when the ODP process starts:

   ./obproxy -o proxy_idc_name=idc5  
   

   Method 2: Set the LDC information by executing an SQL statement:

   obclient> alter proxyconfig set proxy_idc_name='idc1';
   

References

• For more information about routing policies of OceanBase Database, see OceanBase Database Proxy (ODP).

• For more information about common routing features and their configurations of ODP, and how to configure routing policies and troubleshoot routing issues in business scenarios, see ODP routing best practices.