Deploy and use columnstore replicas

To support scenarios such as mixed TP and AP workloads, OceanBase Database supports columnstore replicas (COLUMNSTORE, referred to as C replicas). C replicas can be deployed on independent zones; user tables (including replicated tables, but excluding index tables, internal tables, and system tables) are stored in columnstore format within this replica.

This topic describes the steps for deploying a columnstore replica (C replica) in a tenant's Locality, and how to use a C replica.

Limitations and considerations

• Limitations

  • C replicas cannot be converted to F or R replicas.
  • Physical restore does not support restoring C replicas; if a tenant's locality includes C replicas, the restore will fail.

• Deployment recommendations

  • Deploying multiple C replicas is supported starting from V4.3.5 BP1. It is recommended to deploy at most 3.
  • A Zone must only deploy C replicas; the independent ODP accessing the C replicas must be ODP V4.3.2 or later.
  • When no C replicas are deployed on the primary database, it is not recommended to deploy C replicas on the standby database.

• O&M considerations

  • When performing DDL operations in a cluster with C replicas, system resource consumption typically increases. The main reason is that while processing row-based data, the system also needs to handle columnar data, and commit logs (clogs) must be synchronously written to the corresponding replicas, thus placing higher pressure on resources such as CPU, memory, disk, and I/O.
  • C replica major compactions are usually slower than those of F/R replicas; a new tenant-level major compaction cannot be initiated until the previous one is complete. Before manually initiating a major compaction, it is recommended to check CDB_OB_ZONE_MAJOR_COMPACTION and DBA_OB_ZONE_MAJOR_COMPACTION, for details see View major compaction information.

Deploy columnar replicas

Specify C replicas in the tenant's Locality, which involves two common scenarios:

• Scenario A: Create a tenant that includes C replicas from the start.
• Scenario B: Add C replicas to an existing tenant (new Zone + adjust Locality).

The CPU, memory, disk, and other values in the following SQL examples are for reference only. Adjust them according to your actual workload and resource planning.

Scenario A: Create a new tenant containing C replicas

Assume there is already a deployed cluster with a deployment mode of 2F1A (two replicas + one arbitration service). The three Zones in the cluster are named zone1, zone2, and zone3, with the arbitration service deployed on zone3. To create a tenant containing C replicas on this cluster, follow these steps:

1. Find a machine that can communicate with the current cluster network and deploy an ODP dedicated to accessing C replicas on that machine (see the previous section for deployment and version requirements).

   An independent ODP can be deployed on any host that communicates with the cluster network; it is not necessary to use a dedicated physical server. To prevent resource contention, it is recommended to deploy ODP on a single machine. When deploying ODP, its version must be ODP V4.3.2 or later. For detailed ODP deployment instructions, see Deploy ODP.

2. Create a resource unit unit1 for the tenant.

   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 pool1 for the tenant, specifying the resource unit as unit1.

   CREATE RESOURCE POOL pool1
       UNIT='unit1',
       UNIT_NUM = 1,
       ZONE_LIST = ('zone1','zone2','zone3');
   

4. Create a tenant named tenant_c, specifying its Locality as F@zone1,F@zone2,C@zone3.

   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 mode tenant by default. To create an Oracle-compatible mode tenant, you need to explicitly specify ob_compatibility_mode='oracle' using the system variable ob_compatibility_mode.

Scenario B: Add a C replica for an existing tenant

Assume there is a tenant named tenant_c in the current cluster, with a locality of F@zone1,F@zone2,F@zone3, and its resource pool is pool1. Its ZONE_LIST range is 'zone1','zone2','zone3'. Now, you need to add a C replica for the tenant by adding 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 zone4 to the cluster. For detailed operations on adding a zone, see Add a zone.

2. Add nodes to zone4. For detailed operations on adding nodes, see Add a node.

3. Create the resource unit unit2.

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

4. Create the resource pool pool2 and assign it the resource unit unit2.

   CREATE RESOURCE POOL pool2
       UNIT = 'unit2',
       UNIT_NUM = 1,
       ZONE_LIST = ('zone4');
   

5. Add the resource pool pool2 for the tenant.

   ALTER TENANT tenant_c RESOURCE_POOL_LIST = ('pool1','pool2');
   

   Notice

   RESOURCE_POOL_LIST has a complete table replacement semantics: you must list both the existing and the new resource pools. If you write only ('pool2'), the original resource pool will be unbound.

6. Modify the tenant's locality to F@zone1,F@zone2,F@zone3,C@zone4.

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

Use columnstore replicas

Access the C replica through a dedicated ODP

When accessing the C replica, you need to deploy a separate set of ODP (ODP V4.3.2+). The business sends requests to the C replica through this ODP: configure weak read, routing, and init_sql on it (commonly ob_route_policy = COLUMN_STORE_ONLY, etc.). A complete example is shown below.

In multi-IDC scenarios, if you need both IDC-affinity routing (LDC) and columnstore routing, consider them two independent configuration dimensions. For related Q&A, see AP FAQ.

Deploy ODP

Deploy dedicated for accessing the C replica ODP on a machine that can communicate with the cluster, with a version not lower than ODP V4.3.2. For steps, see Deploy ODP.

Configure routing forwarding policies and weak-consistency reads

Log in to the ODP as root@proxysys, for example:

obclient -uroot@proxysys -h10.10.10.1 -P2883 -p

Common parameters

The following table lists common ODP parameters related to weak-consistency reads from columnar storage.

Scenario combinations

Combination 1: Execution example (weak-consistency read + Route to replica C by zone)

-- Configure read-only and weak-consistency read-related items.
obclient> ALTER PROXYCONFIG SET obproxy_read_only = 0;
obclient> ALTER PROXYCONFIG SET obproxy_read_consistency = 1;
-- Configure to route all requests to the zone where replica C resides and generate a query plan for replica C during a weak-consistency read. For example, assume the zone where replica C 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';

Combination 2: Execution example (weak-consistency read + Route to ColumnStore replica)

-- Configure read-only and weak-consistency read-related items.
obclient> ALTER PROXYCONFIG SET obproxy_read_only = 0;
obclient> ALTER PROXYCONFIG SET obproxy_read_consistency = 1;
-- Configure to route all requests to replica C and generate a query plan for replica C during a weak-consistency read:
obclient> ALTER PROXYCONFIG SET route_target_replica_type = 'ColumnStore';
-- Configure to select only from replicas during a weak-consistency read and disconnect from the client if no replicas are available:
obclient> ALTER PROXYCONFIG SET init_sql='set @@ob_route_policy = COLUMN_STORE_ONLY';

After successful modification, you can verify the configuration using the following statement:

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 successful configuration, OLAP services access the database through this standalone ODP, directing queries to replica C.

References

• Columnstore replicas
• Columnstore FAQ