When automatic load balancing cannot meet your requirements for aggregating or splitting specific partitions, you can manually initiate a partition transfer operation.
Limitations and considerations
The user performing the partition transfer operation must have the
ALTER SYSTEMprivilege. For more information about privileges, see User and permission overview.The partition transfer operation is supported only for primary tenants, and partitions can be transferred only within a user tenant.
If the transfer feature for a tenant is disabled (that is, the value of the
enable_transferparameter isfalse), an error will be returned when executing theTRANSFER PARTITIONstatement for that tenant. ForTRANSFER PARTITIONstatements that have already been successfully executed, the corresponding partition transfer tasks may be canceled.The partition transfer operation is not supported for system tables.
The partition transfer operation is not supported for user tables in the sys tenant.
Transferring a regular table to a broadcast log stream or a replicated table to a regular log stream is not supported.
Non-independent partitions cannot be transferred. For example, partitions in a local index table or a LOB table cannot be transferred.
For the same partition, another partition transfer operation cannot be initiated before the current one is completed.
If a balance job is being executed in the current cluster, a manually triggered partition transfer task will not be scheduled immediately. To schedule the partition transfer task as soon as possible, you can cancel the existing balance job. For more information about how to cancel a balance job, see Cancel a balance job.
Prerequisites
Before you transfer a partition, you must enable the transfer feature. The transfer feature is controlled by the tenant-level parameter
enable_transfer. The default value istrue, indicating that the feature is enabled. For more information about theenable_transferparameter, see enable_transfer.Since the partition transfer strategy may conflict with the automatic rebalancing strategy, to ensure that partition positions remain unchanged, you are advised to disable the automatic rebalancing strategy before you transfer a partition. That is, set the
enable_rebalanceparameter of the corresponding tenant tofalse. For more information about theenable_rebalanceparameter, see enable_rebalance.The partition transfer operation itself is not controlled by the
enable_rebalanceparameter. However, when bothenable_rebalanceandenable_transferare set totrue, it is possible that after a user migrates a partition to its corresponding log stream, the system's automatic rebalancing strategy moves the same partition to another log stream.
Transfer partitions in the sys tenant
Log in to the
systenant of the cluster as therootuser.The following example shows how to connect to the database. Modify the parameters according to your actual environment.
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -AQuery the
DBA_OB_TENANTSview for theTENANT_IDof the target tenant.obclient(root@sys)[oceanbase]> SELECT TENANT_ID FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'mysql001';The query result is as follows:
+-----------+ | TENANT_ID | +-----------+ | 1006 | +-----------+ 1 row in setConfirm the partition information.
You can query the
CDB_OB_TABLE_LOCATIONSview for theTABLE_ID,OBJECT_ID, andLS_IDof a specified partition in a table.Get the
TABLE_ID,OBJECT_ID, andLS_IDof a specified partition in a non-partitioned tableAssume that there is a non-partitioned table named
T1in themysql001tenant. The following example shows how to query it:obclient(root@sys)[oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'T1' LIMIT 1;The query result is as follows:
+----------+-----------+-----------+-------+ | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID | +----------+-----------+-----------+-------+ | 500002 | 500002 | 200001 | 1001 | +----------+-----------+-----------+-------+ 1 row in setGet the
TABLE_ID,OBJECT_ID, andLS_IDof a specified partition in a partitioned tableFor a partitioned table, you only need to specify the name of the partition.
Assume that there is a partitioned table named
TBL1_LOG_Rin themysql001tenant.obclient(root@mysql001)[oceanbase]> CREATE TABLE tbl1_log_r(log_id INT,log_date DATE NOT NULL DEFAULT SYSDATE) PARTITION BY RANGE(log_date) (PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD')) , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD')) , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD')) , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD')) , PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD')) , PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD')) , PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD')) , PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD')) , PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD')) , PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD')) , PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD')) , PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD')) , PARTITION MMAX VALUES LESS THAN (MAXVALUE) );The following example shows how to query the partition
M202005in this table:obclient(root@sys)[oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'TBL1_LOG_R' AND PARTITION_NAME = 'M202005' LIMIT 1;The query result is as follows:
+----------+-----------+-----------+-------+ | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID | +----------+-----------+-----------+-------+ | 500003 | 500009 | 200006 | 1002 | +----------+-----------+-----------+-------+ 1 row in setGet the
TABLE_ID,OBJECT_ID, andLS_IDof a subpartition in a subpartitioned tableFor a subpartitioned table, you need to specify both the name of the partition and the name of the subpartition.
Assume that a subpartitioned table named
T2_F_RLexists in themysql001tenant.obclient(root@mysql001)[oceanbase]> CREATE TABLE t2_f_rl(col1 INT,col2 VARCHAR(50)) PARTITION BY RANGE(col1) SUBPARTITION BY LIST(col2) (PARTITION p0 VALUES LESS THAN(100) (SUBPARTITION sp0 VALUES('01'), SUBPARTITION sp1 VALUES('02') ), PARTITION p1 VALUES LESS THAN(200) (SUBPARTITION sp2 VALUES('01'), SUBPARTITION sp3 VALUES('02'), SUBPARTITION sp4 VALUES('03') ) );The following example shows how to query information related to the subpartition
SP2in this subpartitioned table:obclient(root@sys)[oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'T2_F_RL' AND PARTITION_NAME = 'P1' AND SUBPARTITION_NAME = 'SP2' LIMIT 1;The query result is as follows:
+----------+-----------+-----------+-------+ | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID | +----------+-----------+-----------+-------+ | 500018 | 500023 | 200017 | 1003 | +----------+-----------+-----------+-------+ 1 row in set
Select the destination log stream for the data transfer.
Query the
CDB_OB_LSview for the status and information of the tenant's log streams.obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_LS WHERE TENANT_ID = 1006;The query result is as follows:
+-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+ | TENANT_ID | LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN | DROP_SCN | SYNC_SCN | READABLE_SCN | FLAG | UNIT_LIST | +-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+ | 1006 | 1 | NORMAL | zone1 | 0 | 0 | NULL | NULL | 1701244663685197789 | 1701244663685197789 | | | | 1006 | 1001 | NORMAL | zone1 | 0 | 1001 | 1701239786827662637 | NULL | 1701244663685197789 | 1701244663685197789 | | 1001 | | 1006 | 1002 | NORMAL | zone1 | 0 | 1002 | 1701239786831568305 | NULL | 1701244664066906860 | 1701244664066906859 | | 1002 | | 1006 | 1003 | NORMAL | zone1 | 0 | 1003 | 1701239786834300282 | NULL | 1701244664175263949 | 1701244664175263948 | | 1003 | +-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+ 4 rows in setQuery the
CDB_OB_TABLET_TO_LSview for the distribution information of tablets on the log streams.obclient(root@sys)[oceanbase]> SELECT LS_ID, COUNT(*) AS C FROM oceanbase.CDB_OB_TABLET_TO_LS WHERE TENANT_ID = 1006 GROUP BY LS_ID;The query result is as follows:
+-------+------+ | LS_ID | C | +-------+------+ | 1 | 578 | | 1001 | 7 | | 1002 | 5 | | 1003 | 7 | +-------+------+ 4 rows in setBased on the information presented above, select an appropriate log stream as the destination for the data transfer.
Execute the following command to perform the partition transfer operation.
ALTER SYSTEM TRANSFER PARTITION TABLE_ID [=] table_id, OBJECT_ID [=] object_id TO LS ls_id TENANT = 'tenant_name';where:
table_id: the ID of the table.object_id: the unique identifier of the partition.ls_id: the ID of the log stream at the destination of the data transfer.tenant_name: the tenant to which the partition to be transferred belongs.
Taking tenant
mysql001as an example, the following example shows how to migrate theM202005partition from the current log stream1002to log stream1003for the tableTBL1_LOG_R.obclient(root@sys)[oceanbase]> ALTER SYSTEM TRANSFER PARTITION TABLE_ID = 500003, OBJECT_ID = 500009 TO LS 1003 TENANT = 'mysql001';After the partition transfer statement is executed successfully, you can view the task status by using the following views.
Query the
CDB_OB_TRANSFER_PARTITION_TASKSview for the task'sTASK_ID,TRANSFER_TASK_ID, andBALANCE_JOB_ID.The
CDB_OB_TRANSFER_PARTITION_TASKSview displays all partition transfer tasks currently in progress for all tenants. A sample query is as follows:obclient(root@sys)[oceanbase]> SELECT TASK_ID, BALANCE_JOB_ID, TRANSFER_TASK_ID, STATUS FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASKS WHERE TENANT_ID = 1006 AND TABLE_ID = 500003 AND OBJECT_ID = 500009;A sample query result is as follows:
+---------+----------------+------------------+--------+ | TASK_ID | BALANCE_JOB_ID | TRANSFER_TASK_ID | STATUS | +---------+----------------+------------------+--------+ | 1 | 17304 | 1 | DOING | +---------+----------------+------------------+--------+ 1 row in setYou can confirm the task status based on the value of the
STATUScolumn in the query results to further check the task execution progress:WAITING: The task is waiting and has not started scheduling.INIT: The task has built aBALANCE_JOB. You can view the execution progress of the associatedBALANCE_JOBusing theBALANCE_JOB_ID.DOING: The task has started executing the transfer. You can view the associated partition transfer task using theTRANSFER_TASK_ID. TheTRANSFER_TASK_IDchanges multiple times, as one partition transfer task involves multiple transfers.
If the query result of the
CDB_OB_TRANSFER_PARTITION_TASKSview is empty, you can view the task result through theCDB_OB_TRANSFER_PARTITION_TASK_HISTORYview.Query the
CDB_OB_BALANCE_JOBSorCDB_OB_BALANCE_JOB_HISTORYview with the obtainedBALANCE_JOB_IDto confirm the execution status of the associated BALANCE_JOB.The
CDB_OB_BALANCE_JOBSview displays all load balancing jobs currently in progress for all tenants. Each tenant has only one load balancing job (BALANCE_JOB) at a time, and each job generates multiple load balancing tasks (TRANSFER_TASK). TheCDB_OB_BALANCE_JOB_HISTORYview displays the history of all load balancing jobs executed by all tenants. A sample query is as follows:obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE JOB_ID = 17304;obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOB_HISTORY WHERE JOB_ID = 17304;Here,
17304needs to be replaced with theBALANCE_JOB_IDobtained from the previous step.The
STATUScolumn in the query results shows the execution status of theBALANCE_JOB:DOING: The load balancing job is being executed.COMPLETED: The load balancing job was executed successfully.CANCELING: The load balancing job is being canceled.CANCELED: The load balancing job has been canceled.
Query the
CDB_OB_TRANSFER_TASKSorCDB_OB_TRANSFER_TASK_HISTORYview with the obtainedTRANSFER_TASK_IDto confirm the execution status of the task's associated TRANSFER_TASK.The
CDB_OB_TRANSFER_TASKSview displays all load balancing tasks currently in progress for all tenants. Multiple load balancing tasks may be running at the same time, and these tasks (TRANSFER_TASK) all belong to the same load balancing job (BALANCE_JOB). TheCDB_OB_TRANSFER_TASK_HISTORYview displays the history of all load balancing tasks executed by all tenants. A sample query is as follows:obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASKS WHERE TASK_ID = 1;obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASK_HISTORY WHERE TASK_ID = 1;Here,
1needs to be replaced with theTRANSFER_TASK_IDobtained from the previous step.The
STATUScolumn in the query results shows the execution status of theTRANSFER_TASK:INIT: The task is being created.START: The transfer has started.DOING: The transfer is in progress.ABORTED: The transfer task failed and was terminated.COMPLETED: The transfer task succeeded.FAILED: The transfer task failed.CANCELED: The transfer task was canceled.
Query the
CDB_OB_TRANSFER_PARTITION_TASK_HISTORYview with the obtainedTASK_IDto confirm the result of the partition transfer task.The
CDB_OB_TRANSFER_PARTITION_TASK_HISTORYview displays the history of all partition transfer tasks performed by a tenant.obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASK_HISTORY WHERE TASK_ID = 1;Replace
1with theTASK_IDobtained in the previous step.The
STATUScolumn in the query results indicates the result of the partition transfer task:COMPLETED: The partition transfer task succeeded.FAILED: The partition transfer task failed. You can check theCOMMENTcolumn for the cause of the failure. Common information in theCOMMENTcolumn includes:LS not exist or may be in DROPPING/WAIT_OFFLINE status: The log stream at the destination of the log stream transfer may not exist, or it may be in theDROPPINGorWAIT_OFFLINEstate.LS status is not NORMAL or is in BLOCK_TABLET_IN state: The log stream at the destination of the log stream transfer may not be in theNORMALstate. For example, it may be in theCREATINGorCREATEDstate, or the log stream may have been blocked byBLOCK_TABLET_INand cannot be migrated in.Table has beed dropped: The table to which the partition to be transferred belongs has been dropped.Partition has beed dropped: The partition to be transferred has been dropped.Partition is already in dest LS: The partition is already on the destination log stream.Need retry, partition may be dropped: During the log stream transfer, the partition has been dropped. The system will check again to determine whether the partition exists when generating the next load balancing task.Need retry, partition may be dropped or be transferre: During the log stream transfer, the partition no longer exists. This may be because the partition has been dropped or it is no longer on the source log stream. The system will check whether the partition exists or needs to regenerate a log stream transfer task when generating the next load balancing task.
After the log stream transfer task is successfully executed, view the information about the corresponding partition again.
obclient(root@sys)[oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'TBL1_LOG_R' AND PARTITION_NAME = 'M202005' LIMIT 1;The query result is as follows:
+----------+-----------+-----------+-------+ | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID | +----------+-----------+-----------+-------+ | 500003 | 500009 | 200006 | 1003 | +----------+-----------+-----------+-------+ 1 row in setAccording to the result, the
M202005partition of theTBL1_LOG_Rtable in theSYSdatabase has been migrated from the previous log stream1002to log stream1003. The log stream transfer operation is successful.
Transfer a partition in a user tenant
This document uses the mysql_tenant tenant as an example to provide operational guidance.
Connect to the database as the tenant administrator of the user tenant.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -AConfirm partition information.
Query the
DBA_OB_TABLE_LOCATIONSview for theTABLE_ID,OBJECT_ID, andLS_IDof the specified partition.Retrieve
TABLE_ID,OBJECT_ID, andLS_IDfor a non-partitioned tableAssume the
mysql_tenanttenant contains a non-partitioned table namedt1. The query example is as follows:obclient(root@mysql_tenant)[test]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'test' AND TABLE_NAME= 't1' LIMIT 1;The query result is as follows:
+----------+-----------+-----------+-------+ | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID | +----------+-----------+-----------+-------+ | 500002 | 500002 | 200001 | 1001 | +----------+-----------+-----------+-------+ 1 row in setRetrieve
TABLE_ID,OBJECT_ID, andLS_IDfor a partitioned tableFor a partitioned table, you only need to specify the name of the partition. Here is an example:
Assume the
mysql_tenanttenant contains a partitioned table namedtbl1_l.obclient(root@mysql_tenant)[test]> CREATE TABLE tbl1_l (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) PARTITION BY LIST(col1) (PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (5, 6), PARTITION p2 VALUES IN (DEFAULT) );The following example shows how to query information about partition
p1in this partitioned table:obclient(root@mysql_tenant)[oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'test' AND TABLE_NAME= 'tbl1_l' AND PARTITION_NAME = 'p1' LIMIT 1;The query result is as follows:
+----------+-----------+-----------+-------+ | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID | +----------+-----------+-----------+-------+ | 500012 | 500014 | 200009 | 1001 | +----------+-----------+-----------+-------+ 1 row in setRetrieve
TABLE_ID,OBJECT_ID, andLS_IDfor a subpartitioned tableFor a subpartitioned table, you need to specify both the partition name and the subpartition name.
Assume the
mysql_tenanttenant contains a subpartitioned table namedt2_f_rclc.obclient(root@mysql_tenant)[test]> CREATE TABLE t2_f_rclc (col1 INT,col2 INT) PARTITION BY RANGE COLUMNS(col1) SUBPARTITION BY LIST COLUMNS(col2) (PARTITION p0 VALUES LESS THAN(100) (SUBPARTITION sp0 VALUES IN(1,3), SUBPARTITION sp1 VALUES IN(4,6), SUBPARTITION sp2 VALUES IN(7,9)), PARTITION p1 VALUES LESS THAN(200) (SUBPARTITION sp3 VALUES IN(1,3), SUBPARTITION sp4 VALUES IN(4,6), SUBPARTITION sp5 VALUES IN(7,9)) );The following example shows how to query information about subpartition
sp3in this subpartitioned table:obclient(root@mysql_tenant)[oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'test' AND TABLE_NAME= 't2_f_rclc' AND PARTITION_NAME = 'P1' AND SUBPARTITION_NAME = 'sp3' LIMIT 1;The query result is as follows:
+----------+-----------+-----------+-------+ | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID | +----------+-----------+-----------+-------+ | 500003 | 500009 | 200005 | 1002 | +----------+-----------+-----------+-------+ 1 row in set
Select the destination log stream for the transfer.
Query the
DBA_OB_LSview to obtain the status and information of a tenant's log streams.obclient(root@mysql_tenant)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_LS;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 | +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+ | 1 | NORMAL | zone1 | 0 | 0 | NULL | NULL | 1701247658419109377 | 1701247658419109377 | | | | 1001 | NORMAL | zone1 | 0 | 1001 | 1701239750437064613 | NULL | 1701247658419109376 | 1701247658419109376 | | 1001 | | 1002 | NORMAL | zone1 | 0 | 1002 | 1701239750441114919 | NULL | 1701247658223204599 | 1701247658223204599 | | 1002 | | 1003 | NORMAL | zone1 | 0 | 1003 | 1701239750443869478 | NULL | 1701247659122731843 | 1701247659030755559 | | 1003 | +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+ 4 rows in setQuery the
DBA_OB_TABLET_TO_LSview to obtain the distribution information of tablets on the log stream.obclient(root@mysql_tenant)[oceanbase]> SELECT LS_ID, COUNT(*) AS C FROM oceanbase.DBA_OB_TABLET_TO_LS GROUP BY LS_ID;The query result is as follows:
+-------+------+ | LS_ID | C | +-------+------+ | 1 | 590 | | 1001 | 4 | | 1002 | 3 | | 1003 | 3 | +-------+------+ 4 rows in setBased on the information presented above, select an appropriate log stream as the destination for the transfer.
Execute the following command to perform the partition transfer operation.
ALTER SYSTEM TRANSFER PARTITION TABLE_ID [=] table_id, OBJECT_ID [=] object_id TO LS ls_id;where:
table_id: the table ID.object_id: the unique identifier of the partition.ls_id: the ID of the log stream at the destination of the transfer.tenant_name: the tenant to which the partition to be transferred belongs.
This example migrates the
p1partition from thetbl1_ltable in thetestdatabase within themysql_tenanttenant from the current log stream1001to log stream1003.obclient(root@mysql_tenant)[oceanbase]> ALTER SYSTEM TRANSFER PARTITION TABLE_ID = 500012, OBJECT_ID = 500014 TO LS 1003;After the partition transfer command is successfully executed, you can view the task status using the following views.
Query the
DBA_OB_TRANSFER_PARTITION_TASKSview to obtain the task'sTASK_ID,TRANSFER_TASK_ID, andBALANCE_JOB_ID.The
DBA_OB_TRANSFER_PARTITION_TASKSview displays the current partition transfer tasks being processed under the tenant. An example query is as follows:obclient(root@mysql_tenant)[oceanbase]> SELECT TASK_ID, BALANCE_JOB_ID, TRANSFER_TASK_ID, STATUS FROM oceanbase.DBA_OB_TRANSFER_PARTITION_TASKS WHERE TABLE_ID = 500012 AND OBJECT_ID = 500014;An example of the query result is as follows:
+---------+----------------+------------------+--------+ | TASK_ID | BALANCE_JOB_ID | TRANSFER_TASK_ID | STATUS | +---------+----------------+------------------+--------+ | 1 | 26506 | 1 | DOING | +---------+----------------+------------------+--------+ 1 row in setYou can confirm the task status based on the value of
STATUSin the query result and further check the task execution progress:WAITING: The task is waiting and has not started scheduling.INIT: The task has built aBALANCE_JOB. You can view the execution progress of the associatedBALANCE_JOBby using theBALANCE_JOB_ID.DOING: The task has started executing the transfer. You can view the associated partition transfer task by using theTRANSFER_TASK_ID. TheTRANSFER_TASK_IDchanges multiple times, as one partition transfer task involves multiple transfers.
If the query result of the
DBA_OB_TRANSFER_PARTITION_TASKSview is empty, you can view the task results through theDBA_OB_TRANSFER_PARTITION_TASK_HISTORYview.Query the
DBA_OB_BALANCE_JOBSorDBA_OB_BALANCE_JOB_HISTORYview based on the obtainedBALANCE_JOB_IDto confirm the execution status of the associatedBALANCE_JOB.The
DBA_OB_BALANCE_JOBSview displays the load balancing jobs currently being executed for the current tenant. Each tenant has only one load balancing job (BALANCE_JOB) at a time, and each job generates multiple load balancing tasks (TRANSFER_TASK). TheDBA_OB_BALANCE_JOB_HISTORYview displays the history of load balancing jobs executed for the current tenant. An example query is as follows:obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_BALANCE_JOBS WHERE JOB_ID = 26506; obclient(root@mysql_tenant)[oceanbase]>``` ```shell obclient(root@mysql_tenant)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_BALANCE_JOB_HISTORY WHERE JOB_ID = 26506;Here,
26506needs to be replaced with theBALANCE_JOB_IDobtained from the previous step.The
STATUScolumn in the query result shows the execution status of theBALANCE_JOB:DOING: The load balancing job is being executed.COMPLETED: The load balancing job was executed successfully.CANCELING: The load balancing job is being canceled.CANCELED: The load balancing job has been canceled.
Query the
DBA_OB_TRANSFER_TASKSorDBA_OB_TRANSFER_TASK_HISTORYview based on the obtainedTRANSFER_TASK_IDto confirm the execution status of the associatedTRANSFER_TASK.The
DBA_OB_TRANSFER_TASKSview displays the load balancing tasks currently being executed for the current tenant. Multiple load balancing tasks may be executing at the same time, and these tasks (TRANSFER_TASK) all belong to the same load balancing job (BALANCE_JOB). TheDBA_OB_TRANSFER_TASK_HISTORYview displays the history of load balancing tasks executed for the current tenant. An example query is as follows:obclient(root@mysql_tenant)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_TRANSFER_TASKS WHERE TASK_ID = 1;obclient(root@mysql_tenant)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_TRANSFER_TASK_HISTORY WHERE TASK_ID = 1;Here,
1needs to be replaced with theTRANSFER_TASK_IDobtained from the previous step.The
STATUScolumn in the query result shows the execution status of theTRANSFER_TASK:INIT: The task is being created.START: The transfer has started.DOING: The transfer is in progress.ABORTED: The transfer task failed and the task was terminated.COMPLETED: The transfer task was executed successfully.FAILED: The transfer task failed.CANCELED: The transfer task has been canceled.
Query the
DBA_OB_TRANSFER_PARTITION_TASK_HISTORYview with the obtainedTASK_IDto confirm the result of the partition transfer task.The
DBA_OB_TRANSFER_PARTITION_TASK_HISTORYview displays the history of partition transfer tasks executed for the current tenant. A sample query is as follows:obclient(root@mysql_tenant)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_TRANSFER_PARTITION_TASK_HISTORY WHERE TASK_ID = 1;Here,
1should be replaced with theTASK_IDobtained in the previous step.The
STATUScolumn in the query results indicates the outcome of the partition transfer task:COMPLETED: The partition transfer task was successfully executed.FAILED: The partition transfer task failed. You can check theCOMMENTcolumn for further details about the failure. Common information in theCOMMENTcolumn includes:LS not exist or may be in DROPPING/WAIT_OFFLINE status: The log stream at the destination of the transfer may not exist, or it may be in theDROPPINGorWAIT_OFFLINEstate.LS status is not NORMAL or is in BLOCK_TABLET_IN state: The log stream at the destination of the transfer may not be in theNORMALstate. For example, it may be in theCREATINGorCREATEDstate, or the log stream may have been blocked byBLOCK_TABLET_INand cannot be migrated in.Table has beed dropped: The table to which the partition to be transferred belongs has been dropped.Partition has beed dropped: The partition to be transferred has been dropped.Partition is already in dest LS: The partition is already on the destination log stream.Need retry, partition may be dropped: During the transfer, the partition was dropped. The system will recheck whether the partition exists when generating the next load balancing task.Need retry, partition may be dropped or be transferre: During the transfer, the partition no longer existed. This may be because the partition was dropped or it is no longer on the source log stream. The system will check whether the partition exists or regenerate the transfer task when generating the next load balancing task.
After the partition transfer task is executed successfully, view the partition information again.
obclient(root@mysql_tenant)[oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'test' AND TABLE_NAME= 'tbl1_l' AND PARTITION_NAME = 'p1' LIMIT 1;The query result is as follows:
+----------+-----------+-----------+-------+ | TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID | +----------+-----------+-----------+-------+ | 500012 | 500014 | 200009 | 1003 | +----------+-----------+-----------+-------+ 1 row in setAccording to the results, the
p1partition of thetbl1_ltable in thetestdatabase has been successfully transferred from log stream1001to log stream1003.