Use columnstore replicas

To support enhanced transactional processing (TP) in hybrid load scenarios, such as near real-time decision analysis, OceanBase Database provides support for columnstore replicas. Users can deploy columnstore replicas in independent zones. On columnstore replicas, all user tables (including replicated tables, excluding index tables, internal tables, and system tables) are stored in a columnar format. OLAP operations access columnstore replicas through a separate proxy entry and execute decision analysis tasks using weak read consistency.

Considerations

When using columnstore replicas, note the following:

• Starting from V4.3.5 BP1, multiple columnstore replicas are supported. We recommend that you deploy at most three columnstore replicas.

• Columnstore replicas cannot be converted to or from full-featured or read-only replicas.

• Since accessing columnstore replicas requires deploying an independent ODP, we recommend that you deploy only columnstore replicas in zones where they exist. Additionally, when deploying ODP, use ODP V4.3.2 or above.

• Queries on full-featured or read-only replicas cannot be forwarded to columnstore replicas. Conversely, user table queries on columnstore replicas cannot be forwarded to full-featured or read-only replicas, otherwise an error will occur.

• In clusters with deployed columnstore replicas, executing DDL operations will affect the usage of resources such as CPU, memory, disk, and IO.

• The major compaction of columnstore replicas is slower compared with full-featured or read-only replicas. If a columnstore replica has not completed major compaction, a new round of tenant-level major compaction cannot be initiated. Before manually triggering a merge, we recommend that you check the CDB_OB_ZONE_MAJOR_COMPACTION or DBA_OB_ZONE_MAJOR_COMPACTION views to confirm whether all replicas have completed major compaction in the previous round.

  For more information about how to check the status of a major compaction, see View the status of a major compaction.

• Physical restore does not support restoring columnstore replicas. If a columnstore replica is specified when setting the locality of a tenant, the restore operation will fail.

• If the primary database does not have columnstore replicas deployed, it is not recommended to deploy columnstore replicas on the standby database either.

Deploy columnstore replicas and configure ODP

The following content mainly introduces how to deploy and configure ODP in the OceanBase cluster to access columnstore replicas.

Step 1: Deploy Columnstore Replicas

To deploy columnstore replicas in a cluster, simply specify the columnstore replicas in the tenant's locality. There are two main scenarios:

• Specify columnstore replicas when creating a tenant.

• Add columnstore replicas by modifying the tenant's locality.

Specify columnstore replicas when creating a tenant

Assume there is an existing cluster deployed in a 2F1A configuration (two replicas + one arbitration service). The cluster consists of three zones: zone1, zone2, and zone3, with the arbitration service in zone3. To create a tenant with columnstore replicas in this cluster, follow these steps:

1. Locate a machine that can communicate with the current cluster network and deploy ODP on it.

   To avoid resource contention, we recommend that you deploy ODP on a separate machine. Ensure the ODP version is V4.3.2 or higher. For detailed instructions on deploying ODP, see Deploy ODP.

2. Create a resource unit named unit1.

   obclient [oceanbase]> CREATE RESOURCE UNIT unit1, MAX_CPU=5, MIN_CPU=5, MEMORY_SIZE= '32G', MAX_IOPS=10000, MIN_IOPS=5000, LOG_DISK_SIZE=5301023539200;
   

3. Create a resource pool named pool1 and specify unit1 as the resource unit.

   obclient [oceanbase]> CREATE RESOURCE POOL pool1 UNIT='unit1', UNIT_NUM = 1, ZONE_LIST = ('zone1','zone2','zone3');
   

4. Create a tenant named tenant_c and specify the locality of the tenant as F@zone1,F@zone2,C@zone3.

   obclient [oceanbase]> 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 = '%';
   

   In this example, the created tenant is a MySQL-compatible tenant by default. If you want to create an Oracle-compatible tenant, you must explicitly set the ob_compatibility_mode system variable to oracle.

Add columnstore replicas by modifying the locality of a tenant

Assume there is a tenant named tenant_c in the current cluster, with a locality of F@zone1,F@zone2,F@zone3. The tenant's resource pool is pool1, and its ZONE_LIST is 'zone1','zone2','zone3'. Now, to add a columnstore replica for the tenant, you can add zone4 and then modify the tenant's locality to F@zone1,F@zone2,F@zone3,C@zone4. The steps are as follows:

1. Add a new zone named zone4 to the cluster. For more information, see Add a zone.

2. Add a node to zone4. For more information, see Add a node.

3. Find a server that can communicate with the cluster and deploy an ODP on the server.

   To avoid resource contention, it is recommended to deploy ODP on a dedicated server. When deploying ODP, the version must be ODP V4.3.2 or later. For more information about how to deploy an ODP, see Deploy ODP.

4. Create a resource unit named unit2.

   obclient [oceanbase]> CREATE RESOURCE UNIT unit2, MAX_CPU=5, MIN_CPU=5, MEMORY_SIZE= '32G', MAX_IOPS=10000, MIN_IOPS=5000, LOG_DISK_SIZE=5301023539200;
   

5. Create a resource pool named pool2 and specify unit2 as the resource unit.

   obclient [oceanbase]> CREATE RESOURCE POOL pool2 UNIT = 'unit2', UNIT_NUM = 1, ZONE_LIST = ('zone4');
   

6. Add the pool2 resource pool to the tenant.

   obclient [oceanbase]> ALTER TENANT tenant_c RESOURCE_POOL_LIST = ('pool1','pool2');
   

7. Modify the locality of the tenant to F@zone1,F@zone2,F@zone3,C@zone4.

   obclient [oceanbase]> ALTER TENANT tenant_c LOCALITY = 'F@zone1,F@zone2,F@zone3,C@zone4';
   

Step 2: Configure routing policies and weak-consistency read requests

After the columnstore replica is deployed, you need to configure routing policies and weak-consistency read requests so that OLAP requests can be automatically converted into weak-consistency read requests and forwarded to the corresponding columnstore replica.

1. Log in to the ODP where the columnstore replica is located as the root@proxysys user.

   Here is an example of how to connect:

   [admin@obtest ~]$ obclient -uroot@proxysys -h10.10.10.1 -P2883 -p
   

2. Execute the following statements to configure routing policies and weak-consistency read requests.

   The following two parameter combinations are supported. You can choose either one for setup.

   • First combination

     • Configure SQL requests as read-only.

       obclient> ALTER PROXYCONFIG SET obproxy_read_only = 0;
       

     • Configure SQL requests for weak-consistency reads.

       obclient> ALTER PROXYCONFIG SET obproxy_read_consistency = 1;
       

     • Configure weak-read requests so that ODP routes all requests only to the zone where the columnstore replica resides and generates query plans for the columnstore replica.

       For example, assume that the zone where the columnstore replica resides is zone4.

       obclient> ALTER PROXYCONFIG SET proxy_primary_zone_name='zone4';
       

       obclient> ALTER PROXYCONFIG SET init_sql='set @@ob_route_policy = COLUMN_STORE_ONLY';
       

   • Second combination

     • Configure SQL requests as read-only.

       obclient> ALTER PROXYCONFIG SET obproxy_read_only = 0;
       

     • Configure SQL requests for weak-consistency reads.

       obclient> ALTER PROXYCONFIG SET obproxy_read_consistency = 1;
       

     • Configure weak-read requests so that all requests are routed only to the columnstore replica, and generate query plans for the columnstore replica.

       obclient> ALTER PROXYCONFIG SET route_target_replica_type = 'ColumnStore';
       

       obclient> ALTER PROXYCONFIG SET init_sql='set @@ob_route_policy = COLUMN_STORE_ONLY';
       

     • Configure weak-read requests to select only follower replicas. If no follower replicas are available, disconnect from the client.

       obclient> ALTER PROXYCONFIG SET proxy_route_policy='TARGET_REPLICA_TYPE_FOLLOWER_ONLY';
       

3. Execute the following statements to verify the configuration.

   obclient> SHOW PROXYCONFIG ALL LIKE 'obproxy_read_only';
   

   obclient> SHOW PROXYCONFIG ALL LIKE 'obproxy_read_consistency';
   

   obclient> SHOW PROXYCONFIG ALL LIKE 'proxy_primary_zone_name';
   

   obclient> SHOW PROXYCONFIG ALL LIKE 'init_sql';
   

   obclient> SHOW PROXYCONFIG ALL LIKE 'route_target_replica_type';
   

   obclient> SHOW PROXYCONFIG ALL LIKE 'proxy_route_policy';
   

After the configuration is successful, users can direct query requests to the columnstore replica through the independent ODP. By leveraging the batch processing advantages of the columnstore replica, queries can be accelerated without affecting the original OLTP workloads.

Scenarios where columnstore replicas use row-based storage

In OceanBase Database, if a table is created as a rowstore table, the system will create a corresponding pure columnstore table on the columnstore replica. If a table is created as a columnstore table, the storage method on the columnstore replica will be consistent with the full-featured replica. Therefore, the columnstore replica's role is to convert the rowstore user partitions of user tables on the full-featured replica into columnar storage, indicating the "final" state as columnstore. However, not all partitions on the columnstore replica are always in columnstore form. In the following scenarios, user table partitions on the columnstore replica will temporarily use row storage, requiring the system to autonomously schedule row-to-column conversion tasks to transform the latest baseline data into columnstore.

During a row-to-column conversion, although the optimizer generates a columnar query plan, the actual execution still queries the rowstore baseline. You can query the CDB_OB_CS_REPLICA_STATS view (in a sys tenant) or the DBA_OB_CS_REPLICA_STATS view (in a user tenant) for the available information about the columnstore replica and the progress of the row-to-column conversion. After the conversion is complete, you can perform queries on the columnstore replica.

Here is an example that queries the row-to-column conversion progress of tablets in log streams across all tenants in a columnstore replica from a sys tenant:

obclient[oceanbase]> SELECT * FROM oceanbase.CDB_OB_CS_REPLICA_STATS;

The query result is as follows:

+-----------+----------------+----------+-------+------------------+----------------------+-----------------------+---------------------------+-----------+
| TENANT_ID | SVR_IP         | SVR_PORT | LS_ID | TOTAL_TABLET_CNT | AVAILABLE_TABLET_CNT | TOTAL_MACRO_BLOCK_CNT | AVAILABLE_MACRO_BLOCK_CNT | AVAILABLE |
+-----------+----------------+----------+-------+------------------+----------------------+-----------------------+---------------------------+-----------+
|      1004 | xx.xxx.xxx.212 |    63000 |  1001 |             1019 |                 1019 |                 10706 |                     10706 | TRUE      |
|      1006 | xx.xxx.xxx.212 |    63000 |  1001 |              133 |                  133 |                   875 |                       875 | TRUE      |
+-----------+----------------+----------+-------+------------------+----------------------+-----------------------+---------------------------+-----------+
2 rows in set