Cancel a load balancing job

Only one load balancing job can be running at a time for each tenant. If the current load balancing job does not meet your business requirements, you can cancel it. Additionally, if you have initiated a Transfer Partition task but there is an ongoing load balancing job causing delays, you can cancel the load balancing job first.

Limitations

You can cancel a load balancing job only on the primary database (that is, the primary tenant).

Background information

Only one load balancing job can be running at a time for each tenant. These jobs include scaling up, scaling down, migration balancing, and partition balancing. Each load balancing job generates multiple load balancing tasks that perform the specific operations for load balancing. Canceling a load balancing job will cancel all running tasks under that job.

Cancel a running load balancing job in 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 actual environment parameters.

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

2. Query the CDB_OB_BALANCE_JOBS view to obtain the current running job.

   obclient(root@sys)[oceanbase]> SELECT TENANT_ID, JOB_ID, BALANCE_STRATEGY, JOB_TYPE, STATUS FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE TENANT_ID = 1002;
   

   The query result is as follows:

   +-----------+--------+----------------------+------------+--------+
   | TENANT_ID | JOB_ID | BALANCE_STRATEGY     | JOB_TYPE   | STATUS |
   +-----------+--------+----------------------+------------+--------+
   |      1002 | 520683 | LS balance by shrink | LS_BALANCE | DOING  |
   +-----------+--------+----------------------+------------+--------+
   1 row in set
   

3. Based on the query result, if you confirm that the job does not meet your expectations, you can execute the following statement to cancel the load balancing job.

   ALTER SYSTEM CANCEL BALANCE JOB TENANT = 'tenant_name';
   

   Example:

   obclient(root@sys)[oceanbase]> ALTER SYSTEM CANCEL BALANCE JOB TENANT ='mysql_tenant';
   

Cancel a running load balancing job in a user tenant

1. Connect to the database as the tenant administrator of a MySQL-compatible or Oracle-compatible tenant.

   The following example shows how to connect to the database. Please replace the actual environment parameters.

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

2. Query the DBA_OB_BALANCE_JOBS view to obtain the current running job.

   MySQL-compatible mode

   Oracle-compatible mode

   The query statement in MySQL-compatible mode is as follows:

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

   The query statement in Oracle-compatible mode is as follows:

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

3. Based on the query result, if you confirm that it does not meet your expectations, you can execute the following statement to cancel the load balancing job.

   obclient> ALTER SYSTEM CANCEL BALANCE JOB;
   

References

• Transfer partitions

• Cancel a "Transfer Partition" task

• Suspend and resume a load balancing job