Pause and resume a load balancing job

If a load balancing job is running for a tenant and there is an urgent change that needs to be executed, you can choose to pause the current load balancing job to prevent it from affecting the urgent change. Once the urgent change is completed, you can resume the load balancing job to continue its execution.

Limitations

• You can pause or resume a load balancing job only on the primary database (that is, the primary tenant).

• When suspending or resuming a load balancing job, the status of the load balancing job must not be CANCELING.

Suspend the ongoing load balancing job

Suspend a load balancing job on a user tenant from 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. Replace the actual environment parameters in the example.

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

2. Obtain tenant-related information.

   obclient(root@sys)[oceanbase]> SELECT TENANT_ID, TENANT_NAME, TENANT_ROLE FROM oceanbase.DBA_OB_TENANTS WHERE tenant_name = 'mq_t1';
   

   The query result is as follows:

   +-------------+-----------+-------------+
   | TENANT_NAME | TENANT_ID | TENANT_ROLE |
   +-------------+-----------+-------------+
   | mq_t1       |      1002 | PRIMARY     |
   +-------------+-----------+-------------+
   1 row in set
   

3. Query the CDB_OB_BALANCE_JOBS view to obtain the current running job and its status.

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

   The query result is as follows:

   +-----------+--------+--------------------+--------+
   | TENANT_ID | JOB_ID | JOB_TYPE           | STATUS |
   +-----------+--------+--------------------+--------+
   |      1002 |  62944 | TRANSFER_PARTITION | DOING  |
   +-----------+--------+--------------------+--------+
   1 row in set
   

   Based on the query result, the status of the current load balancing job is DOING, not CANCELING. You can suspend the job.

4. Execute the following statement to suspend the load balancing job.

   ALTER SYSTEM SUSPEND BALANCE JOB TENANT = 'tenant_name';
   

   Example:

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

5. After the execution succeeds, query the CDB_OB_BALANCE_JOBS view again. You can see that the status of the current job changes to SUSPEND.

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

   The query result is as follows:

   +-----------+--------+--------------------+---------+
   | TENANT_ID | JOB_ID | JOB_TYPE           | STATUS  |
   +-----------+--------+--------------------+---------+
   |      1002 |  62944 | TRANSFER_PARTITION | SUSPEND |
   +-----------+--------+--------------------+---------+
   1 row in set
   

Suspend the load balancing job on the current tenant from the 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 parameters with those in your environment.

   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 and its status.

   MySQL-compatible mode

   Oracle-compatible mode

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

   obclient(root@mysql001)[oceanbase]> SELECT JOB_ID, JOB_TYPE, STATUS FROM oceanbase.DBA_OB_BALANCE_JOBS;
   

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

   obclient(SYS@oracle001)[SYS]> SELECT JOB_ID, JOB_TYPE, STATUS FROM SYS.DBA_OB_BALANCE_JOBS;
   

   The query result is as follows:

   +--------+--------------------+---------+
   | JOB_ID | JOB_TYPE           | STATUS  |
   +--------+--------------------+---------+
   |  62944 | TRANSFER_PARTITION |   DOING |
   +--------+--------------------+---------+
   1 row in set
   

   Based on the query result, the status of the current load balancing job is DOING, not CANCELING. You can suspend the job.

3. Execute the following statement to pause the load balancing job.

   obclient> ALTER SYSTEM SUSPEND BALANCE JOB;
   

4. After the execution succeeds, query the DBA_OB_BALANCE_JOBS view again. You can see that the status of the current job changes to SUSPEND.

   MySQL-compatible mode

   Oracle-compatible mode

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

   obclient(root@mysql001)[oceanbase]> SELECT JOB_ID, JOB_TYPE, STATUS FROM oceanbase.DBA_OB_BALANCE_JOBS;
   

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

   obclient(SYS@oracle001)[SYS]> SELECT JOB_ID, JOB_TYPE, STATUS FROM SYS.DBA_OB_BALANCE_JOBS;
   

   The query result is as follows:

   +--------+--------------------+---------+
   | JOB_ID | JOB_TYPE           | STATUS  |
   +--------+--------------------+---------+
   |  62944 | TRANSFER_PARTITION | SUSPEND |
   +--------+--------------------+---------+
   1 row in set
   

Resume a suspended load balancing job

Resume a suspended load balancing job of a user tenant from 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. Replace the actual environment parameters as needed.

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

2. Obtain tenant-related information.

   obclient(root@sys)[oceanbase]> SELECT TENANT_ID, TENANT_NAME, TENANT_ROLE FROM oceanbase.DBA_OB_TENANTS WHERE tenant_name = 'mq_t1';
   

   The query result is as follows:

   +-------------+-----------+-------------+
   | TENANT_NAME | TENANT_ID | TENANT_ROLE |
   +-------------+-----------+-------------+
   | mq_t1       |      1002 | PRIMARY     |
   +-------------+-----------+-------------+
   1 row in set
   

3. Query the CDB_OB_BALANCE_JOBS view to obtain the current suspended job and its status.

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

   The query result is as follows:

   +-----------+--------+--------------------+---------+
   | TENANT_ID | JOB_ID | JOB_TYPE           | STATUS  |
   +-----------+--------+--------------------+---------+
   |      1002 |  62944 | TRANSFER_PARTITION | SUSPEND |
   +-----------+--------+--------------------+---------+
   1 row in set
   

   Based on the query result, the status of the current load balancing job is SUSPEND.

4. Execute the following statement to resume the suspended load balancing job.

   ALTER SYSTEM RESUME BALANCE JOB TENANT = 'tenant_name';
   

   Example:

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

5. After the execution succeeds, query the CDB_OB_BALANCE_JOBS view again. You can see that the status of the current job changes to DOING.

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

   The query result is as follows:

   +-----------+--------+--------------------+---------+
   | TENANT_ID | JOB_ID | JOB_TYPE           | STATUS  |
   +-----------+--------+--------------------+---------+
   |      1002 |  62944 | TRANSFER_PARTITION |  DOING  |
   +-----------+--------+--------------------+---------+
   1 row in set
   

Resume the suspended load balancing job in the current tenant from the 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 suspended job and its status.

   MySQL-compatible mode

   Oracle-compatible mode

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

   obclient(root@mysql001)[oceanbase]> SELECT JOB_ID, JOB_TYPE, STATUS FROM oceanbase.DBA_OB_BALANCE_JOBS;
   

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

   obclient(SYS@oracle001)[SYS]> SELECT JOB_ID, JOB_TYPE, STATUS FROM SYS.DBA_OB_BALANCE_JOBS;
   

   The query result is as follows:

   +--------+--------------------+---------+
   | JOB_ID | JOB_TYPE           | STATUS  |
   +--------+--------------------+---------+
   |  62944 | TRANSFER_PARTITION | SUSPEND |
   +--------+--------------------+---------+
   1 row in set
   

   Based on the query result, the status of the current load balancing job is SUSPEND.

3. Execute the following statement to resume the suspended load balancing job.

   obclient> ALTER SYSTEM RESUME BALANCE JOB;
   

4. After the execution succeeds, query the DBA_OB_BALANCE_JOBS view again. You can see that the status of the current job changes to DOING.

   MySQL-compatible mode

   Oracle-compatible mode

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

   obclient(root@mysql001)[oceanbase]> SELECT JOB_ID, JOB_TYPE, STATUS FROM oceanbase.DBA_OB_BALANCE_JOBS;
   

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

   obclient(SYS@oracle001)[SYS]> SELECT JOB_ID, JOB_TYPE, STATUS FROM SYS.DBA_OB_BALANCE_JOBS;
   

   The query result is as follows:

   +--------+--------------------+---------+
   | JOB_ID | JOB_TYPE           | STATUS  |
   +--------+--------------------+---------+
   |  62944 | TRANSFER_PARTITION |  DOING  |
   +--------+--------------------+---------+
   1 row in set
   

References

• Cancel a "Transfer Partition" task
• Cancel a load balancing job task