Manually trigger partition balancing

In addition to configuring a scheduled partition balancing task, you can also manually call the TRIGGER_PARTITION_BALANCE subprogram in the DBMS_BALANCE system package to trigger a round of partition balancing.

Limitations

Only user tenants support manually triggering partition balancing. The sys tenant does not support this feature.

Notes

• When other balancing tasks are in progress in the system, you cannot manually trigger a partition balancing task. You need to wait until the current balancing tasks are completed before you manually trigger partition balancing.

  The sys tenant can query the CDB_OB_BALANCE_JOBS view to check whether the specified tenant has other ongoing balancing tasks. A user tenant can query the DBA_OB_BALANCE_JOBS view to check whether there are other ongoing balancing tasks.

• Before you manually trigger partition balancing, make sure that the values of the tenant-level configuration parameters enable_rebalance and enable_transfer are both True. If the value of either configuration parameter is not True, the statement for manually triggering partition balancing fails to execute.

• If the partitions of the current tenant are already balanced, the system returns the error message partitions are already balanced, no need to trigger partition balance.

Manually trigger a round of partition balancing

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

   Here is an example of the connection command. Replace the parameters with those in your actual environment.

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

2. Call the TRIGGER_PARTITION_BALANCE subprogram in the DBMS_BALANCE system package to trigger a round of partition balancing.

   MySQL-compatible mode

   Oracle-compatible mode

   The statement in MySQL-compatible mode is as follows:

   CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(balance_timeout);
   

   The balance_timeout parameter controls the timeout period of the background partition balancing task, in seconds. If this value is not specified, the background partition balancing tasks, including manually triggered and scheduled partition balancing tasks, have no timeout limit and keep running until they are completed.

   Here are some examples:

   • Trigger a round of partition balancing, and the background partition balancing task has no timeout limit.

     obclient [oceanbase]> CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE();
     

   • Trigger a round of partition balancing, and the timeout period of the background partition balancing task is 7200 seconds.

     obclient [oceanbase]> CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(7200);
     

   For more information about the TRIGGER_PARTITION_BALANCE subprogram in the DBMS_BALANCE system package, see TRIGGER_PARTITION_BALANCE.

   The statement in Oracle-compatible mode is as follows:

   BEGIN
       DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(balance_timeout);
   END;
   

   The balance_timeout parameter controls the timeout period of the background partition balancing task, in seconds. If this value is not specified, the background partition balancing tasks, including manually triggered and scheduled partition balancing tasks, have no timeout limit and keep running until they are completed.

   Here are some examples:

   • Trigger a round of partition balancing, and the background partition balancing task has no timeout limit.

     obclient [SYS]> DELIMITER //
     

     obclient [SYS]> BEGIN
         DBMS_BALANCE.TRIGGER_PARTITION_BALANCE();
     END;//
     

     obclient [SYS]> DELIMITER ;
     

   • Trigger a round of partition balancing, and the timeout period of the background partition balancing task is 7200 seconds.

     obclient [SYS]> DELIMITER //
     

     obclient [SYS]> BEGIN
         DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(7200);
     END;//
     

     obclient [SYS]> DELIMITER ;
     

   For more information about the TRIGGER_PARTITION_BALANCE subprogram in the DBMS_BALANCE system package, see TRIGGER_PARTITION_BALANCE.

What to do next

After you manually trigger partition balancing, the actual partition transfer is performed asynchronously by the background balancing task. You can view the execution process of the partition balancing task through a view. For more information, see View the partition balancing task in the background.

References

View the partition balancing task in the background