Purpose
This statement is used to suspend (SUSPEND), resume (RESUME), or cancel (CANCEL) a balance job.
When a tenant has a running balance job and other urgent changes need to be performed, you can choose to pause the current balance job first. After the urgent changes are completed, you can resume the job to continue its execution. This prevents the balance job from affecting the urgent changes.
If the current balance job does not meet your requirements, you can cancel it. Additionally, if you have started a partition transfer but the task is delayed due to an ongoing balance job, you can also cancel the current balance job.
Limitations and considerations
You can cancel, pause, or resume a balance job only on the primary database (that is, the primary tenant).
When stopping or resuming a balance job, the job's status must not be
CANCELING.
Privilege requirements
The current user has the ALTER SYSTEM privilege.
Syntax
ALTER SYSTEM {SUSPEND | RESUME | CANCEL} BALANCE JOB [TENANT = 'tenant_name'];
Parameters
Parameter |
Description |
|---|---|
| tenant_name | Optional. The name of the tenant for which you want to pause, resume, or cancel a task. This parameter is required when the command is executed by the system tenant. |
Examples
The following examples are based on operations performed in the sys tenant.
Use the following statement to query currently running tasks.
obclient(root@sys)[oceanbase]> SELECT T.TENANT_NAME, J.TENANT_ID, J.JOB_ID, J.CREATE_TIME, J.MODIFY_TIME, J.BALANCE_STRATEGY, J.JOB_TYPE, j.STATUS FROM oceanbase.CDB_OB_BALANCE_JOBS J, oceanbase.DBA_OB_TENANTS T WHERE J.TENANT_ID = T.TENANT_ID;The return result is as follows:
+-------------+-----------+--------+----------------------------+----------------------------+---------------------------+--------------+--------+ | TENANT_NAME | TENANT_ID | JOB_ID | CREATE_TIME | MODIFY_TIME | BALANCE_STRATEGY | JOB_TYPE | STATUS | +-------------+-----------+--------+----------------------------+----------------------------+---------------------------+--------------+--------+ | mysql001 | 1004 | 300793 | 2024-02-29 16:45:12.864110 | 2024-02-29 16:45:12.864110 | LS balance by shrink | LS_BALANCE | DOING | +-------------+-----------+--------+----------------------------+----------------------------+---------------------------+--------------+--------+ 1 row in setChoose the appropriate operation based on your business requirements:
Stop ongoing balance jobs.
obclient(root@sys)[oceanbase]> ALTER SYSTEM SUSPEND BALANCE JOB TENANT = 'mysql001';Resume a paused balance job
obclient(root@sys)[oceanbase]> ALTER SYSTEM RESUME BALANCE JOB TENANT = 'mysql001';Cancel a balance job that does not meet expectations.
obclient(root@sys)[oceanbase]> ALTER SYSTEM CANCEL BALANCE JOB TENANT = 'mysql001';