In OceanBase AI Database, you can use the DUPLICATE_SCOPE parameter to specify the attribute of a replicated table. After a table is created, you can change its attribute by modifying the value of the DUPLICATE_SCOPE parameter.
Limitations and considerations
When converting a replicated table, if the current tenant has triggered a load balancing task due to scaling, zone change, or locality change, the conversion task must wait for the current balancing task to complete.
Notice
The conversion of a replicated table is asynchronous. After manually changing the
DUPLICATE_SCOPE, you must wait for the background partition balancing task to complete before the table can be used as a table with the target attribute. For more information about partition balancing, see Intra-tenant balancing.Replicated tables and table groups are mutually exclusive. When converting a regular table to a replicated table, if the regular table belongs to a table group, it cannot be converted.
The conversion of a replicated table depends on the tenant's load balancing and transfer capabilities. If any of the tenant-level parameters
enable_rebalance,enable_transfer, orenable_rereplicationis set tofalse, the conversion operation cannot proceed. If you change the value ofenable_rebalanceorenable_transfertofalsewhile there is a running replication table conversion task, the corresponding task will be canceled.
Prerequisites
Before converting a replicated table, ensure the following parameters are set to True:
enable_rebalanceThe tenant-level parameter
enable_rebalancecontrols whether to perform load balancing between tenants in the sys tenant and within tenants in user tenants. The default value isTrue. For detailed information and settings, see enable_rebalance.enable_transferThe tenant-level parameter
enable_transfercontrols whether to enable transfers within a tenant. The default value isTrue. For detailed information and settings, see enable_transfer.enable_rereplicationThe cluster-level parameter
enable_rereplicationis the master switch for automatic migration and replication of log stream replicas. The default value isTrue. For detailed information and settings, see enable_rereplication.
Syntax for changing the attribute of a replicated table
The SQL statement for changing the attribute of a replicated table is as follows:
ALTER TABLE table_name DUPLICATE_SCOPE= 'none | cluster';
In the statement:
none: converts the current table into a regular table.cluster: converts the current table into a replicated table.
Example
The following command demonstrates how to convert a replicated table into a regular table.
Create a replicated table named
tbl1.obclient [test]> CREATE TABLE tbl1 (c1 int,c2 int) DUPLICATE_SCOPE= 'cluster';Convert the replicated table to a regular table.
ALTER TABLE tbl1 DUPLICATE_SCOPE= 'none';Note
The preceding SQL statement immediately returns success after modifying the table attribute, but the actual conversion of the replicated table is performed asynchronously by background threads.
View the replication attributes of the table.
You can view the current replication attributes of a table and its log stream ID through the
oceanbase.DBA_OB_TABLE_LOCATIONSview. ADUPLICATE_SCOPEcolumn value ofCLUSTERindicates it is a replicated table, whileNONEindicates it is a regular table.SELECT database_name, table_name, table_id, tablet_id, ls_id, role, duplicate_scope FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_name = 'tbl1';The query result is as follows:
+---------------+------------+----------+-----------+-------+--------+-----------------+ | database_name | table_name | table_id | tablet_id | ls_id | role | duplicate_scope | +---------------+------------+----------+-----------+-------+--------+-----------------+ | test | tbl1 | 500011 | 200002 | 1002 | LEADER | NONE | +---------------+------------+----------+-----------+-------+--------+-----------------+ 1 row in setNote
You can use the following steps to check whether the distribution of partition replicas meets the target attribute after a replicated table is converted.
Check the conversion progress of the replicated table.
View the properties of the log stream.
All replicated tables in a tenant are placed on a dedicated log stream (broadcast log stream) that carries replicated tables. You can view the properties of a log stream through the
oceanbase.DBA_OB_LSview. AFLAGcolumn containingDUPLICATEindicates it is a broadcast log stream. IfFLAGis empty or does not containDUPLICATE, it indicates it is a regular log stream.SELECT * FROM oceanbase.DBA_OB_LS WHERE LS_ID = 1002;The query result is as follows:
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+ | LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN | DROP_SCN | SYNC_SCN | READABLE_SCN | FLAG | UNIT_LIST | +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+ | 1002 | NORMAL | zone1 | 0 | 0 | 1713164377091593001 | NULL | 1713171198496433000 | 1713171198496433000 | DUPLICATE | | +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+ 1 row in setDetermine whether a replicated table conversion task needs to be generated.
Replicated table conversion tasks are generated by the background load balancing and transfer modules. You can use the following statement to quickly determine whether a replicated table conversion task needs to be generated. Here,
DUPLICATE_SCOPEis an attribute of the table, andFLAGis an attribute of the LS to which the current table'stabletbelongs. IfFLAGcontainsDUPLICATE, it indicates a broadcast log stream.SELECT A.duplicate_scope, B.flag FROM oceanbase.DBA_OB_TABLE_LOCATIONS AS A JOIN oceanbase.DBA_OB_LS AS B ON A.ls_id = B.ls_id WHERE A.table_name = 'tbl1' AND A.role = 'LEADER';The query result is as follows:
+-----------------+-----------+ | duplicate_scope | flag | +-----------------+-----------+ | NONE | DUPLICATE | +-----------------+-----------+ 1 row in setThe return information is explained as follows:
DUPLICATE_SCOPEisNONEandFLAGcontainsDUPLICATE: The non-replicated table is on the broadcast log stream. You need to generate a task to convert the replicated table to a normal table, which transfers the non-replicated table from the broadcast log stream to a normal log stream.DUPLICATE_SCOPEisNONEandFLAGdoes not containDUPLICATE: The replicated table is on a normal log stream. You need to generate a task to convert the normal table to a replicated table, which transfers the replicated table from the normal log stream to the broadcast log stream.
Immediately generate a conversion task.
You can temporarily reduce the parameter
partition_balance_schedule_intervalto immediately generate a conversion task.ALTER SYSTEM SET partition_balance_schedule_interval = '30s';View the replicated table conversion task.
The task generated by the replicated table conversion can be viewed through the related views
DBA/CDB_OB_BALANCE_JOB_HISTORYandDBA/CDB_OB_BALANCE_JOBS.View the execution status of historical conversion tasks.
If there is no historical record, the task may be in progress or not yet generated.
SELECT * FROM oceanbase.DBA_OB_BALANCE_JOB_HISTORY;The query result is as follows:
+--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+ | JOB_ID | CREATE_TIME | FINISH_TIME | BALANCE_STRATEGY | JOB_TYPE | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS | COMMENT | +--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+ | 777854 | 2024-04-15 16:54:29.220400 | 2024-04-15 16:54:39.231516 | partition balance | PARTITION_BALANCE | 1 | 1 | COMPLETED | NULL | +--------+----------------------------+----------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+ 1 row in setView the execution status of the current balancing task.
SELECT * FROM oceanbase.DBA_OB_BALANCE_JOBS;View the subtasks corresponding to the conversion task (including partition lists).
Insert the
job_idfrom the above task into the following SQL statement:SELECT * FROM oceanbase.DBA_OB_BALANCE_TASKS WHERE JOB_ID = xxxx;SELECT * FROM oceanbase.DBA_OB_BALANCE_TASK_HISTORY WHERE JOB_ID = xxxx;
Note
- If other
BALANCE_JOBtasks are running in theDBA_OB_BALANCE_JOBSview, you need to wait for the current task to complete before the partition balancing task is scheduled. - If no corresponding replication table conversion task appears in the aforementioned
BALANCErelated views, it may mean the correspondingBALANCE_JOBhas not yet been generated. You need to wait or temporarily reduce the parameterpartition_balance_schedule_interval. - If no conversion task is generated for a long time, check load balancing-related parameters such as
enable_rebalance,enable_transfer,balancer_idle_time, andenable_rereplication, and verify that Transfer is not disabled.