Deploy and use columnstore replicas

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

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

Limitations and considerations

• Limitations

  • C replicas and F or R replicas cannot be converted into each other.
  • 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 have at most 3.
  • A Zone that hosts C replicas must only host 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

  • Performing DDL operations in a cluster with C replicas typically increases system resource consumption. The main reason is that while processing row data, the system also needs to handle column data, and commit logs (clogs) must be written synchronously to the corresponding replicas, thus putting greater pressure on resources such as CPU, memory, disk, and I/O.
  • C replica major compaction is usually slower than F/R compaction. A new round of tenant-level compaction cannot be initiated until the previous round 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 compaction information.

Deploy columnstore replicas

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

• Scenario A: Create a tenant with 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 preceding 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 the ODP on a single machine. When deploying the 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. Its locality is F@zone1,F@zone2,F@zone3, the tenant's resource pool is pool1, and its ZONE_LIST range is 'zone1','zone2','zone3'. Now you need to add a C replica for the tenant by adding zone4, then modifying 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 pool. 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 FAQs, 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 earlier 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, verify the configuration with 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
• Typical AP architecture deployment guide
• Columnstore FAQ