Query the history of partition balancing jobs and tasks in the background

You can query views for the execution history of partition balancing jobs and tasks.

Query the history of partition balancing jobs in the background from the sys tenant

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

   Note that you must specify the corresponding parameters in the following sample code based on your actual database configurations.

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

2. Execute the following statements to query the history of partition balancing jobs and tasks in the background of a specified tenant.

   • Query the history of partition balancing jobs of a specified tenant

     The CDB_OB_BALANCE_JOB_HISTORY view displays the load balancing jobs of all tenants, including scale-out, scale-in, log stream migration, and partition balancing jobs.

     obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOB_HISTORY WHERE TENANT_ID = 1002 AND JOB_TYPE = 'PARTITION_BALANCE';
     

     The following result is returned:

     +-----------+--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+
     | TENANT_ID | JOB_ID | CREATE_TIME                | FINISH_TIME                | BALANCE_STRATEGY                | JOB_TYPE          | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS    | COMMENT | MAX_END_TIME |
     +-----------+--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+
     |      1002 | 312280 | 2024-06-14 11:00:49.034035 | 2024-06-14 11:01:04.528871 | partition attribution alignment | PARTITION_BALANCE |               1 |                       1 | COMPLETED | NULL    | NULL         |
     +-----------+--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+
     1 row in set
     

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

   • Query the history of partition balancing tasks at the log stream level of a specified tenant.

     The CDB_OB_BALANCE_TASK_HISTORY view displays the history of partition balancing tasks at the log stream level of all tenants, including LS_SPLIT, LS_MERGE, LS_TRANSFER, and LS_ALTER tasks.

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

     The following result is returned:

     +-----------+---------+----------------------------+----------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     | 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
     

   • Query the history of partition balancing tasks at the tablet level of a specified tenant.

     The CDB_OB_TRANSFER_TASK_HISTORY view displays the history of partition balancing tasks at the tablet level of all tenants. For example, this view displays the partitions and tablets of the tenant that are transferred.

     obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASK_HISTORY WHERE TENANT_ID = 1002;
     

     The following result is returned:

     +-----------+---------+----------------------------+----------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+--------------------------------------------------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     | 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
     

Query the history of partition balancing jobs and tasks in the background from a user tenant

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

   Note that you must specify the corresponding parameters in the following sample code based on your actual database configurations.

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

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

   MySQL mode

   Oracle mode

   • Query the history of partition balancing jobs of the current tenant.

     The DBA_OB_BALANCE_JOB_HISTORY view displays the history of load balancing jobs of the current tenant, including scale-out, scale-in, log stream migration, and partition balancing jobs.

     obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_BALANCE_JOB_HISTORY WHERE JOB_TYPE = 'PARTITION_BALANCE';
     

     The following result is returned:

     +--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+
     | JOB_ID | CREATE_TIME                | FINISH_TIME                | BALANCE_STRATEGY                | JOB_TYPE          | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS    | COMMENT | MAX_END_TIME |
     +--------+----------------------------+----------------------------+---------------------------------+-------------------+----------------- +-------------------------+-----------+---------+--------------+
     | 312280 | 2024-06-14 11:00:49.034035 | 2024-06-14 11:01:04.528871 | partition attribution alignment | PARTITION_BALANCE |               1 |                       1 | COMPLETED | NULL    | NULL         |
     +--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+
     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 of the current tenant.

     The DBA_OB_BALANCE_TASK_HISTORY view displays the history of partition balancing tasks at the log stream level of the current tenant, including LS_SPLIT, LS_MERGE, LS_TRANSFER, and LS_ALTER tasks.

     obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_BALANCE_TASK_HISTORY WHERE BALANCE_STRATEGY LIKE '%partition%';
     

     The following result is returned:

     +---------+----------------------------+----------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     | 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 of the current tenant.

     The DBA_OB_TRANSFER_TASK_HISTORY view displays the history of partition balancing tasks at the tablet level of the current tenant. For example, this view displays the partitions and tablets of the tenant that are transferred.

     obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_TRANSFER_TASK_HISTORY;
     

     The following result is returned:

     +---------+----------------------------+----------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+--------------------------------------------------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     | 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 jobs of the current tenant.

     The DBA_OB_BALANCE_JOB_HISTORY view displays the history of load balancing jobs of the current tenant, including scale-out, scale-in, log stream migration, and partition balancing jobs.

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

     The following result is returned:

     +--------+------------------------------+------------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+
     | JOB_ID | CREATE_TIME                  | FINISH_TIME                  | BALANCE_STRATEGY                | JOB_TYPE          | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS    | COMMENT | MAX_END_TIME |
     +--------+------------------------------+------------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+
     | 346408 | 14-JUN-24 01.59.59.014732 PM | 14-JUN-24 02.00.09.055400 PM | partition attribution alignment | PARTITION_BALANCE |               1 |                       1 | COMPLETED | NULL    | NULL         |
     +--------+------------------------------+------------------------------+---------------------------------+-------------------+-----------------+-------------------------+-----------+---------+--------------+
     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 of the current tenant.

     The DBA_OB_BALANCE_TASK_HISTORY view displays the history of partition balancing tasks at the log stream level of the current tenant, including LS_SPLIT, LS_MERGE, LS_TRANSFER, and LS_ALTER tasks.

     obclient [sys]> SELECT * FROM SYS.DBA_OB_BALANCE_TASK_HISTORY WHERE BALANCE_STRATEGY LIKE '%partition%';
     

     The following result is returned:

     +---------+------------------------------+------------------------------+-------------+--------+---------+-----------+--------------------+------------+---------------------+-------------+-----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     | 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 of the current tenant.

     The DBA_OB_TRANSFER_TASK_HISTORY view displays the history of partition balancing tasks at the tablet level of the current tenant. For example, this view displays the partitions and tablets of the tenant that are transferred.

     obclient [SYS]> SELECT * FROM SYS.DBA_OB_TRANSFER_TASK_HISTORY;
     

     The following result is returned:

     +---------+------------------------------+------------------------------+--------+---------+---------------+------------+---------------------+-------------------------+------------------------+------------------------------------------------------+--------------+---------------------+---------------------+-----------+-----------------------------------+--------+-----------------+---------------------+---------+
     | 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

Query partition balancing jobs and tasks in the background