View the history of partition balancing tasks in the background

After a partition balancing task is completed, you can view the task history in a view.

View the partition balancing tasks in the background of the sys tenant

1. Log in to the sys tenant of the cluster as the root user.

   The following example shows how to connect to the database. Please replace the placeholders with the actual values in your environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. Execute the following statement to view the partition balancing task history in the background of the specified tenant.

   • View the partition balancing task history of the specified tenant.

     The CDB_OB_BALANCE_JOB_HISTORY view displays the macro-level balancing tasks of all tenants, including expansion, reduction, migration, and partition balancing.

     obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOB_HISTORY WHERE TENANT_ID = 1002 AND JOB_TYPE = 'PARTITION_BALANCE';
     

     The query result is as follows:

     +-----------+--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+--------------------+----------------------------------------------------------------------------------------------+
     | TENANT_ID | JOB_ID | CREATE_TIME                | FINISH_TIME                | BALANCE_STRATEGY                | JOB_TYPE          | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS    | COMMENT | MAX_END_TIME | ZONE_UNIT_NUM_LIST | PARAMETER_LIST                                                                               |
     +-----------+--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+--------------------+----------------------------------------------------------------------------------------------+
     |      1002 | 312280 | 2024-06-14 11:00:49.034035 | 2024-06-14 11:01:04.528871 | partition attribution alignment | PARTITION_BALANCE |            NULL |                       1 | COMPLETED | NULL    | NULL         | zone1:1            | ls_scale_out_factor:1,enable_rebalance:true,enable_transfer:true,enable_gts_standalone:false |
     +-----------+--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+--------------------+----------------------------------------------------------------------------------------------+
     1 row in set
     

     For more information about the fields in the CDB_OB_BALANCE_JOB_HISTORY view, see CDB_OB_BALANCE_JOB_HISTORY.

   • View the partition balancing task history at the log stream (LS) level of the specified tenant.

     The CDB_OB_BALANCE_TASK_HISTORY view displays the log stream-level tasks of all tenants, including LS splits (LS_SPLIT), LS merges (LS_MERGE), LS transfers (LS_TRANSFER), and LS property modifications (LS_ALTER).

     obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_BALANCE_TASK_HISTORY WHERE TENANT_ID = 1002 AND BALANCE_STRATEGY LIKE '%partition%';
     

     The query result is as follows:

     +-----------+---------+----------------------------+----------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     | TENANT_ID | TASK_ID | CREATE_TIME                | FINISH_TIME                | TASK_TYPE   | SRC_LS | DEST_LS | PART_LIST | FINISHED_PART_LIST | PART_COUNT | FINISHED_PART_COUNT | LS_GROUP_ID | STATUS    | PARENT_LIST | CHILD_LIST | CURRENT_TRANSFER_TASK_ID | JOB_ID | COMMENT | BALANCE_STRATEGY                |
     +-----------+---------+----------------------------+----------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     |      1002 |  312281 | 2024-06-14 11:00:49.036806 | 2024-06-14 11:00:50.416084 | LS_ALTER    |   1002 |      -1 | NULL      | NULL               |          0 |                   0 |        1001 | COMPLETED | NULL        | 312282     |                       -1 | 312280 | NULL    | partition attribution alignment |
     |      1002 |  312282 | 2024-06-14 11:00:49.038509 | 2024-06-14 11:00:54.460059 | LS_TRANSFER |   1002 |    1001 | NULL      | 500129:500129      |          0 |                   1 |        1001 | COMPLETED | NULL        | 312283     |                       -1 | 312280 | NULL    | partition attribution alignment |
     |      1002 |  312283 | 2024-06-14 11:00:49.038509 | 2024-06-14 11:00:56.470614 | LS_ALTER    |   1002 |      -1 | NULL      | NULL               |          0 |                   0 |           0 | COMPLETED | NULL        | NULL       |                       -1 | 312280 | NULL    | partition attribution alignment |
     +-----------+---------+----------------------------+----------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     3 rows in set
     

     For more information about the fields in the CDB_OB_BALANCE_TASK_HISTORY view, see CDB_OB_BALANCE_TASK_HISTORY.

   • View the partition balancing task history at the tablet level of the specified tenant.

     The CDB_OB_TRANSFER_TASK_HISTORY view displays the tablet-level tasks of all tenants, including the specific partitions and tablets that are transferred.

     obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASK_HISTORY WHERE TENANT_ID = 1002;
     

     The query result is as follows:

     +-----------+---------+----------------------------+----------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+--------------------------------------------------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     | TENANT_ID | TASK_ID | CREATE_TIME                | FINISH_TIME                | SRC_LS | DEST_LS | PART_LIST     | PART_COUNT | NOT_EXIST_PART_LIST | LOCK_CONFLICT_PART_LIST | TABLE_LOCK_TABLET_LIST | TABLET_LIST                                                                                      | TABLET_COUNT | START_SCN           | FINISH_SCN          | STATUS    | TRACE_ID                          | RESULT | BALANCE_TASK_ID | TABLE_LOCK_OWNER_ID | COMMENT |
     +-----------+---------+----------------------------+----------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+--------------------------------------------------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     |      1002 |       1 | 2024-06-14 11:00:52.439818 | 2024-06-14 11:00:53.780044 |   1002 |    1001 | 500129:500129 |          1 | NULL                | NULL                    | NULL                   | 200070:0,1152921504606847005:0,1152921504606847006:0,1152921504606847007:0,1152921504606847008:0 |            5 | 1718334052840440001 | 1718334053449914002 | COMPLETED | YB42AC1E87DC-00061AC2E771139C-0-0 |      0 |          312282 |              312299 |         |
     +-----------+---------+----------------------------+----------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+--------------------------------------------------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     1 row in set
     

     For more information about the fields in the CDB_OB_TRANSFER_TASK_HISTORY view, see CDB_OB_TRANSFER_TASK_HISTORY.

View the partition balancing tasks in the background

1. Log in to the MySQL or Oracle tenant of the cluster as the tenant administrator of the user tenant.

   The following example shows how to connect to the database. Please replace the parameters with the actual values in your environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -A
   

2. Execute the following statement to query the history of partition balancing tasks in the background of the current tenant.

   MySQL mode

   Oracle mode

   • Query the history of partition balancing tasks in the current tenant.

     The DBA_OB_BALANCE_JOB_HISTORY view displays the macro-level balancing tasks of the current tenant, including expansion, reduction, migration, and partition balancing tasks.

     obclient(root@mysql001)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_BALANCE_JOB_HISTORY WHERE JOB_TYPE = 'PARTITION_BALANCE';
     

     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 | MAX_END_TIME | ZONE_UNIT_NUM_LIST | PARAMETER_LIST                                                                               |
     +--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+--------------------+----------------------------------------------------------------------------------------------+
     | 312280 | 2024-06-14 11:00:49.034035 | 2024-06-14 11:01:04.528871 | partition attribution alignment | PARTITION_BALANCE |            NULL |                       1 | COMPLETED | NULL    | NULL         | zone1:1            | ls_scale_out_factor:1,enable_rebalance:true,enable_transfer:true,enable_gts_standalone:false |
     +--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+--------------------+----------------------------------------------------------------------------------------------+
     1 row in set
     

     For more information about the fields in the DBA_OB_BALANCE_JOB_HISTORY view, see DBA_OB_BALANCE_JOB_HISTORY.

   • Query the history of partition balancing tasks at the log stream level in the current tenant.

     The DBA_OB_BALANCE_TASK_HISTORY view displays the log stream-level tasks of the current tenant, including log stream splitting (LS_SPLIT), log stream merging (LS_MERGE), log stream transfer (LS_TRANSFER), and log stream attribute modification (LS_ALTER) tasks.

     obclient(root@mysql001)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_BALANCE_TASK_HISTORY WHERE BALANCE_STRATEGY LIKE '%partition%';
     

     The query result is as follows:

     +---------+----------------------------+----------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     | TASK_ID | CREATE_TIME                | FINISH_TIME                | TASK_TYPE   | SRC_LS | DEST_LS | PART_LIST | FINISHED_PART_LIST | PART_COUNT | FINISHED_PART_COUNT | LS_GROUP_ID | STATUS    | PARENT_LIST | CHILD_LIST | CURRENT_TRANSFER_TASK_ID | JOB_ID | COMMENT | BALANCE_STRATEGY                |
     +---------+----------------------------+----------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     |  312281 | 2024-06-14 11:00:49.036806 | 2024-06-14 11:00:50.416084 | LS_ALTER    |   1002 |      -1 | NULL      | NULL               |          0 |                   0 |        1001 | COMPLETED | NULL        | 312282     |                       -1 | 312280 | NULL    | partition attribution alignment |
     |  312282 | 2024-06-14 11:00:49.038509 | 2024-06-14 11:00:54.460059 | LS_TRANSFER |   1002 |    1001 | NULL      | 500129:500129      |          0 |                   1 |        1001 | COMPLETED | NULL        | 312283     |                       -1 | 312280 | NULL    | partition attribution alignment |
     |  312283 | 2024-06-14 11:00:49.038509 | 2024-06-14 11:00:56.470614 | LS_ALTER    |   1002 |      -1 | NULL      | NULL               |          0 |                   0 |           0 | COMPLETED | NULL        | NULL       |                       -1 | 312280 | NULL    | partition attribution alignment |
     +---------+----------------------------+----------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     3 rows in set
     

     For more information about the fields in the DBA_OB_BALANCE_TASK_HISTORY view, see DBA_OB_BALANCE_TASK_HISTORY.

   • Query the history of partition balancing tasks at the tablet level in the current tenant.

     The DBA_OB_TRANSFER_TASK_HISTORY view displays the tablet-level tasks of the current tenant, that is, the tablets to be transferred in the tenant.

     obclient(root@mysql001)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_TRANSFER_TASK_HISTORY;
     

     The query result is as follows:

     +---------+----------------------------+----------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+--------------------------------------------------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     | TASK_ID | CREATE_TIME                | FINISH_TIME                | SRC_LS | DEST_LS | PART_LIST     | PART_COUNT | NOT_EXIST_PART_LIST | LOCK_CONFLICT_PART_LIST | TABLE_LOCK_TABLET_LIST | TABLET_LIST                                                                                      | TABLET_COUNT | START_SCN           | FINISH_SCN          | STATUS    | TRACE_ID                          | RESULT | BALANCE_TASK_ID | TABLE_LOCK_OWNER_ID | COMMENT |
     +---------+----------------------------+----------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+--------------------------------------------------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     |       1 | 2024-06-14 11:00:52.439818 | 2024-06-14 11:00:53.780044 |   1002 |    1001 | 500129:500129 |          1 | NULL                | NULL                    | NULL                   | 200070:0,1152921504606847005:0,1152921504606847006:0,1152921504606847007:0,1152921504606847008:0 |            5 | 1718334052840440001 | 1718334053449914002 | COMPLETED | YB42AC1E87DC-00061AC2E771139C-0-0 |      0 |          312282 |              312299 |         |
     +---------+----------------------------+----------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+--------------------------------------------------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     1 row in set
     

     For more information about the fields in the DBA_OB_TRANSFER_TASK_HISTORY view, see DBA_OB_TRANSFER_TASK_HISTORY.

   • Query the history of partition balancing tasks in the current tenant.

     The DBA_OB_BALANCE_JOB_HISTORY view displays the macro-level balancing tasks of the current tenant, including expansion, reduction, migration, and partition balancing tasks.

     obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_BALANCE_JOB_HISTORY WHERE JOB_TYPE = 'PARTITION_BALANCE';
     

     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 | MAX_END_TIME | ZONE_UNIT_NUM_LIST | PARAMETER_LIST                                                                               |
     +--------+------------------------------+------------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+--------------------+----------------------------------------------------------------------------------------------+
     | 346408 | 14-JUN-24 01.59.59.014732 PM | 14-JUN-24 02.00.09.055400 PM | partition attribution alignment | PARTITION_BALANCE |            NULL |                       1 | COMPLETED | NULL    | NULL         | zone1:1            | ls_scale_out_factor:1,enable_rebalance:true,enable_transfer:true,enable_gts_standalone:false |
     +--------+------------------------------+------------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+--------------------+----------------------------------------------------------------------------------------------+
     1 row in set
     

     For more information about the fields in the DBA_OB_BALANCE_JOB_HISTORY view, see DBA_OB_BALANCE_JOB_HISTORY.

   • Query the history of partition balancing tasks at the log stream level in the current tenant.

     The DBA_OB_BALANCE_TASK_HISTORY view displays the log stream-level tasks of the current tenant, including log stream splitting (LS_SPLIT), log stream merging (LS_MERGE), log stream transfer (LS_TRANSFER), and log stream attribute modification (LS_ALTER) tasks.

     obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_BALANCE_TASK_HISTORY WHERE BALANCE_STRATEGY LIKE '%partition%';
     

     The query result is as follows:

     +---------+------------------------------+------------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     | TASK_ID | CREATE_TIME                  | FINISH_TIME                  | TASK_TYPE   | SRC_LS | DEST_LS | PART_LIST | FINISHED_PART_LIST | PART_COUNT | FINISHED_PART_COUNT | LS_GROUP_ID | STATUS    | PARENT_LIST | CHILD_LIST | CURRENT_TRANSFER_TASK_ID | JOB_ID | COMMENT | BALANCE_STRATEGY                |
     +---------+------------------------------+------------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     |  346409 | 14-JUN-24 01.59.59.016760 PM | 14-JUN-24 02.00.00.652394 PM | LS_ALTER    |   1002 |      -1 | NULL      | NULL               |          0 |                   0 |        1001 | COMPLETED | NULL        | 346410     |                       -1 | 346408 | NULL    | partition attribution alignment |
     |  346410 | 14-JUN-24 01.59.59.017468 PM | 14-JUN-24 02.00.04.959125 PM | LS_TRANSFER |   1002 |    1001 | NULL      | 500021:500021      |          0 |                   1 |        1001 | COMPLETED | NULL        | 346411     |                       -1 | 346408 | NULL    | partition attribution alignment |
     |  346411 | 14-JUN-24 01.59.59.017468 PM | 14-JUN-24 02.00.06.970879 PM | LS_ALTER    |   1002 |      -1 | NULL      | NULL               |          0 |                   0 |           0 | COMPLETED | NULL        | NULL       |                       -1 | 346408 | NULL    | partition attribution alignment |
     +---------+------------------------------+------------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     3 rows in set
     

     For more information about the fields in the DBA_OB_BALANCE_TASK_HISTORY view, see DBA_OB_BALANCE_TASK_HISTORY.

   • Query the history of partition balancing tasks at the tablet level in the current tenant.

     The DBA_OB_TRANSFER_TASK_HISTORY view displays the tablet-level tasks of the current tenant, that is, the tablets to be transferred in the tenant.

     obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_TRANSFER_TASK_HISTORY;
     

     The query result is as follows:

     +---------+------------------------------+------------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     | TASK_ID | CREATE_TIME                  | FINISH_TIME                  | SRC_LS | DEST_LS | PART_LIST     | PART_COUNT | NOT_EXIST_PART_LIST | LOCK_CONFLICT_PART_LIST | TABLE_LOCK_TABLET_LIST | TABLET_LIST                                          | TABLET_COUNT | START_SCN           | FINISH_SCN          | STATUS    | TRACE_ID                          | RESULT | BALANCE_TASK_ID | TABLE_LOCK_OWNER_ID | COMMENT |
     +---------+------------------------------+------------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     |       1 | 14-JUN-24 02.00.02.675095 PM | 14-JUN-24 02.00.04.443093 PM |   1002 |    1001 | 500021:500021 |          1 | NULL                | NULL                    | NULL                   | 200007:0,1152921504606846987:0,1152921504606846988:0 |            3 | 1718344803084664002 | 1718344803870702005 | COMPLETED | YB42AC1E87DC-00061AC2EE113D0F-0-0 |      0 |          346410 |              346428 | NULL    |
     +---------+------------------------------+------------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     1 row in set
     

     For more information about the fields in the DBA_OB_TRANSFER_TASK_HISTORY view, see DBA_OB_TRANSFER_TASK_HISTORY.

References

View the partition balancing tasks in the background