Query partition balancing jobs and tasks in the background

You can query views for the execution details of related jobs and tasks.

Query the partition balancing job and tasks being executed 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 statement to query the partition balancing job and tasks being executed in the background of a specified tenant.

   • Query the ongoing partition balancing job of a specified tenant.

     The CDB_OB_BALANCE_JOBS view displays ongoing 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_JOBS WHERE TENANT_ID = 1002 AND JOB_TYPE = 'PARTITION_BALANCE';
     

     The following result is returned:

     +-----------+--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+--------+---------+--------------+
     | TENANT_ID | JOB_ID | CREATE_TIME                | MODIFY_TIME                | BALANCE_STRATEGY                | JOB_TYPE          | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS | COMMENT | MAX_END_TIME |
     +-----------+--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+--------+---------+--------------+
     |      1002 | 374412 | 2024-06-14 14:39:38.710194 | 2024-06-14 14:39:38.710194 | partition attribution alignment | PARTITION_BALANCE |               1 |                       1 | DOING  | NULL    | NULL         |
     +-----------+--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+--------+---------+--------------+
     1 row in set
     

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

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

     The CDB_OB_BALANCE_TASKS view displays ongoing 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_TASKS WHERE TENANT_ID = 1002 AND BALANCE_STRATEGY LIKE '%partition%';
     

     The following result is returned:

     +-----------+---------+----------------------------+----------------------------+-------------+--------+---------+---------------------------------------------------------------------------------------------------+--------------------+------------+---------------------+-------------+----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     | TENANT_ID | TASK_ID | CREATE_TIME                | MODIFY_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 |  374414 | 2024-06-14 14:39:38.710194 | 2024-06-14 14:39:42.356430 | LS_TRANSFER |   1002 |    1001 | 500224:500224,500229:500229,500234:500234,500239:500239,500244:500244,500249:500249,500254:500254 | NULL               |          7 |                   0 |        1001 | TRANSFER |             | 374415     |                        3 | 374412 |         | partition attribution alignment |
     |      1002 |  374415 | 2024-06-14 14:39:38.710194 | 2024-06-14 14:39:38.710194 | LS_ALTER    |   1002 |      -1 | NULL                                                                                              | NULL               |          0 |                   0 |           0 | INIT     | 374414      | NULL       |                       -1 | 374412 | NULL    | partition attribution alignment |
     +-----------+---------+----------------------------+----------------------------+-------------+--------+---------+---------------------------------------------------------------------------------------------------+--------------------+------------+---------------------+-------------+----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     2 rows in set
     

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

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

     obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASKS WHERE TENANT_ID = 1002\G
     

     The following result is returned:

     *************************** 1. row ***************************
                    TENANT_ID:1002
                      TASK_ID: 3
                  CREATE_TIME: 2024-06-14 14:39:42.356430
                  MODIFY_TIME: 2024-06-14 14:38:44.315350
                       SRC_LS: 1002
                      DEST_LS: 1001
                    PART_LIST: 500224:500224,500229:500229,500234:500234,500239:500239,500244:500244,500249:500249,500254:500254
                   PART_COUNT: 7
          NOT_EXIST_PART_LIST: NULL
      LOCK_CONFLICT_PART_LIST: NULL
       TABLE_LOCK_TABLET_LIST: NULL
                  TABLET_LIST: 200089:0,200090:0,200091:0,200092:0,200093:0,200094:0,200095:0,1152921504606847076:0,1152921504606847077:0,1152921504606847078:0,1152921504606847079:0,1152921504606847080:0,1152921504606847081:0,1152921504606847082:0,1152921504606847083:0,1152921504606847084:0,1152921504606847085:0,1152921504606847086:0,1152921504606847087:0,1152921504606847088:0,1152921504606847089:0,1152921504606847090:0,1152921504606847091:0,1152921504606847092:0,1152921504606847093:0,1152921504606847094:0,1152921504606847095:0,1152921504606847096:0,1152921504606847097:0,1152921504606847098:0,1152921504606847099:0,1152921504606847100:0,1152921504606847101:0,1152921504606847102:0,1152921504606847103:0
                 TABLET_COUNT: 35
                    START_SCN: 1718347182784953001
                   FINISH_SCN: 0
                       STATUS: DOING
                     TRACE_ID: YB42AC1E87DC-00061AC2E77146D7-0-0
                       RESULT: 0
              BALANCE_TASK_ID: 374414
          TABLE_LOCK_OWNER_ID: 374434
                      COMMENT:
     1 row in set
     

Query the partition balancing job and tasks being executed 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 partition balancing job and tasks being executed in the background of the current tenant.

   MySQL mode

   Oracle mode

   • Query the ongoing partition balancing job of the current tenant.

     The DBA_OB_BALANCE_JOBS view displays the ongoing load balancing job of the current tenant, such as a scale-out, scale-in, log stream migration, or partition balancing job.

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

     The following result is returned:

     +--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+--------+---------+--------------+
     | JOB_ID | CREATE_TIME                | MODIFY_TIME                | BALANCE_STRATEGY                | JOB_TYPE          | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS | COMMENT | MAX_END_TIME |
     +--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+------------------------- +--------+---------+--------------+
     | 374412 | 2024-06-14 14:39:38.710194 | 2024-06-14 14:39:38.710194 | partition attribution alignment | PARTITION_BALANCE |               1 |                       1 | DOING  | NULL    | NULL         |
     +--------+----------------------------+----------------------------+---------------------------------+-------------------+-----------------+-------------------------+--------+---------+--------------+
     1 row in set
     

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

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

     The DBA_OB_BALANCE_TASKS view displays ongoing 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_TASKS WHERE BALANCE_STRATEGY LIKE '%partition%';
     

     The following result is returned:

     +---------+----------------------------+----------------------------+-------------+--------+---------+---------------------------------------------------------------------------------------------------+--------------------+------------+---------------------+-------------+----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     | TASK_ID | CREATE_TIME                | MODIFY_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                |
     +---------+----------------------------+----------------------------+-------------+--------+---------+---------------------------------------------------------------------------------------------------+--------------------+------------+---------------------+-------------+----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     |  374414 | 2024-06-14 14:39:38.710194 | 2024-06-14 14:39:42.356430 | LS_TRANSFER |   1002 |    1001 | 500224:500224,500229:500229,500234:500234,500239:500239,500244:500244,500249:500249,500254:500254 | NULL               |          7 |                   0 |        1001 | TRANSFER |             | 374415     |                        3 | 374412 |         | partition attribution alignment |
     |  374415 | 2024-06-14 14:39:38.710194 | 2024-06-14 14:39:38.710194 | LS_ALTER    |   1002 |      -1 | NULL                                                                                              | NULL               |          0 |                   0 |           0 | INIT     | 374414      | NULL       |                       -1 | 374412 | NULL    | partition attribution alignment |
     +---------+----------------------------+----------------------------+-------------+--------+---------+---------------------------------------------------------------------------------------------------+--------------------+------------+---------------------+-------------+----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     2 rows in set
     

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

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

     The DBA_OB_TRANSFER_TASKS view displays the ongoing 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 being transferred.

     obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_TRANSFER_TASKS\G
     

     The following result is returned:

     *************************** 1. row ***************************
                      TASK_ID: 3
                  CREATE_TIME: 2024-06-14 14:39:42.356430
                  MODIFY_TIME: 2024-06-14 14:38:44.315350
                       SRC_LS: 1002
                      DEST_LS: 1001
                    PART_LIST: 500224:500224,500229:500229,500234:500234,500239:500239,500244:500244,500249:500249,500254:500254
                   PART_COUNT: 7
          NOT_EXIST_PART_LIST: NULL
      LOCK_CONFLICT_PART_LIST: NULL
       TABLE_LOCK_TABLET_LIST: NULL
                  TABLET_LIST: 200089:0,200090:0,200091:0,200092:0,200093:0,200094:0,200095:0,1152921504606847076:0,1152921504606847077:0,1152921504606847078:0,1152921504606847079:0,1152921504606847080:0,1152921504606847081:0,1152921504606847082:0,1152921504606847083:0,1152921504606847084:0,1152921504606847085:0,1152921504606847086:0,1152921504606847087:0,1152921504606847088:0,1152921504606847089:0,1152921504606847090:0,1152921504606847091:0,1152921504606847092:0,1152921504606847093:0,1152921504606847094:0,1152921504606847095:0,1152921504606847096:0,1152921504606847097:0,1152921504606847098:0,1152921504606847099:0,1152921504606847100:0,1152921504606847101:0,1152921504606847102:0,1152921504606847103:0
                 TABLET_COUNT: 35
                    START_SCN: 1718347182784953001
                   FINISH_SCN: 0
                       STATUS: DOING
                     TRACE_ID: YB42AC1E87DC-00061AC2E77146D7-0-0
                       RESULT: 0
              BALANCE_TASK_ID: 374414
          TABLE_LOCK_OWNER_ID: 374434
                      COMMENT:
     1 row in set
     

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

   • Query the ongoing partition balancing job of the current tenant.

     The DBA_OB_BALANCE_JOBS view displays the ongoing load balancing job of the current tenant, such as a scale-out, scale-in, log stream migration, or partition balancing job.

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

     The following result is returned:

     +--------+------------------------------+------------------------------+---------------------------------+-------------------+-----------------+-------------------------+--------+---------+--------------+
     | JOB_ID | CREATE_TIME                  | MODIFY_TIME                  | BALANCE_STRATEGY                | JOB_TYPE          | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS | COMMENT | MAX_END_TIME |
     +--------+------------------------------+------------------------------+---------------------------------+-------------------+-----------------+-------------------------+--------+---------+--------------+
     | 370858 | 14-JUN-24 02.22.54.870414 PM | 14-JUN-24 02.22.54.870414 PM | partition attribution alignment | PARTITION_BALANCE |               1 |                       1 | DOING  | NULL    | NULL         |
     +--------+------------------------------+------------------------------+---------------------------------+-------------------+-----------------+-------------------------+--------+---------+--------------+
     1 row in set
     

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

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

     The DBA_OB_BALANCE_TASKS view displays ongoing 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_TASKS WHERE BALANCE_STRATEGY LIKE '%partition%';
     

     The following result is returned:

     +---------+------------------------------+------------------------------+-------------+--------+---------+-----------------------------------------------------------------------+--------------------+------------+---------------------+-------------+----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     | TASK_ID | CREATE_TIME                  | MODIFY_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                |
     +---------+------------------------------+------------------------------+-------------+--------+---------+-----------------------------------------------------------------------+--------------------+------------+---------------------+-------------+----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     |  370859 | 14-JUN-24 02.22.54.870414 PM | 14-JUN-24 02.22.55.737260 PM | LS_ALTER    |   1002 |      -1 | NULL                                                                  | NULL               |          0 |                   0 |        1001 | ALTER_LS | NULL        | 370860     |                       -1 | 370858 | NULL    | partition attribution alignment |
     |  370860 | 14-JUN-24 02.22.54.870414 PM | 14-JUN-24 02.22.54.870414 PM | LS_TRANSFER |   1002 |    1001 | 500048:500048,500051:500051,500054:500054,500057:500057,500060:500060 | NULL               |          5 |                   0 |        1001 | INIT     | 370859      | 370861     |                       -1 | 370858 | NULL    | partition attribution alignment |
     |  370861 | 14-JUN-24 02.22.54.870414 PM | 14-JUN-24 02.22.54.870414 PM | LS_ALTER    |   1002 |      -1 | NULL                                                                  | NULL               |          0 |                   0 |           0 | INIT     | 370860      | NULL       |                       -1 | 370858 | NULL    | partition attribution alignment |
     +---------+------------------------------+------------------------------+-------------+--------+---------+-----------------------------------------------------------------------+--------------------+------------+---------------------+-------------+----------+-------------+------------+--------------------------+--------+---------+---------------------------------+
     3 rows in set
     

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

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

     The DBA_OB_TRANSFER_TASKS view displays the ongoing 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 being transferred.

     obclient [SYS]> SELECT * FROM SYS.DBA_OB_TRANSFER_TASKS\G
     

     The following result is returned:

     *************************** 1. row ***************************
                      TASK_ID: 2
                  CREATE_TIME: 14-JUN-24 02.22.58.755501 PM
                  MODIFY_TIME: 14-JUN-24 02.22.59.267311 PM
                       SRC_LS: 1002
                      DEST_LS: 1001
                    PART_LIST: 500048:500048,500051:500051,500054:500054,500057:500057,500060:500060
                   PART_COUNT: 5
          NOT_EXIST_PART_LIST: NULL
      LOCK_CONFLICT_PART_LIST: NULL
       TABLE_LOCK_TABLET_LIST: NULL
                  TABLET_LIST: 200015:0,200016:0,200017:0,200018:0,200019:0,1152921504606847003:0,1152921504606847004:0,1152921504606847005:0,1152921504606847006:0,1152921504606847007:0,1152921504606847008:0,1152921504606847009:0,1152921504606847010:0,1152921504606847011:0,1152921504606847012:0
                 TABLET_COUNT: 15
                    START_SCN: 1718346179101597002
                   FINISH_SCN: 0
                       STATUS: DOING
                     TRACE_ID: YB42AC1E87DC-00061AC2EE11426E-0-0
                       RESULT: 0
              BALANCE_TASK_ID: 370860
          TABLE_LOCK_OWNER_ID: 370880
                      COMMENT: NULL
     1 row in set
     

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

References

Query the history of partition balancing tasks in the background