Configure the scheduled partition balancing job

When a user tenant is created, OceanBase Database provides a built-in scheduled partition balancing job named SCHEDULED_TRIGGER_PARTITION_BALANCE. By default, the partition balancing job is triggered at 00:00 every day. You can call related subprograms of the DBMS_SCHEDULER package to configure the parameters of the scheduled partition balancing job.

Limitations

You cannot configure the scheduled partition balancing job in the sys tenant.

Enable the scheduled partition balancing job

In the current version of OceanBase Database, the scheduled partition balancing job is enabled for a created user tenant by default. For a user tenant upgraded from OceanBase Database V4.2.3 or earlier to the current version of OceanBase Database, the scheduled partition balancing job is disabled by default. To enable the job, perform the following steps:

1. Log in to a MySQL or Oracle tenant of the cluster as the administrator of the 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. Make sure that the value of the tenant-level parameter partition_balance_schedule_interval is set to 0s.

   If you enable the scheduled partition balancing job named SCHEDULED_TRIGGER_PARTITION_BALANCE, make sure that the tenant-level parameter partition_balance_schedule_interval is set to 0s.

   You can execute the following statements to respectively query the tenant-level parameter partition_balance_schedule_interval and set its value to 0s:

   obclient> SHOW PARAMETERS LIKE '%partition_balance_schedule_interval%';
   

   obclient> ALTER SYSTEM SET partition_balance_schedule_interval='0s';
   

3. Call the ENABLE subprogram of the DBMS_SCHEDULER package to enable the scheduled partition balancing job named SCHEDULED_TRIGGER_PARTITION_BALANCE.

   MySQL mode

   Oracle mode

   Execute the following statement in a MySQL tenant:

   obclient [oceanbase]> CALL DBMS_SCHEDULER.ENABLE('SCHEDULED_TRIGGER_PARTITION_BALANCE');
   

   Execute the following statements in an Oracle tenant:

   obclient [SYS]> DELIMITER //
   

   obclient [SYS]> BEGIN 
        DBMS_SCHEDULER.ENABLE('SCHEDULED_TRIGGER_PARTITION_BALANCE'); 
   END;//
   

   obclient [SYS]> DELIMITER ;
   

   For more information about the ENABLE subprogram, see ENABLE.

Disable the scheduled partition balancing job

In the current version of OceanBase Database, the scheduled partition balancing job is enabled for a created user tenant by default. You can disable the job based on your business needs. After you disable the scheduled partition balancing job, you can manually trigger the job, or set the value of the partition_balance_schedule_interval parameter to 0s to enable the job.

1. Log in to a MySQL or Oracle tenant of the cluster as the administrator of the 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. Call the DISABLE subprogram of the DBMS_SCHEDULER package to disable the scheduled partition balancing job named SCHEDULED_TRIGGER_PARTITION_BALANCE.

   MySQL mode

   Oracle mode

   Execute the following statement in a MySQL tenant:

   obclient [oceanbase]> CALL DBMS_SCHEDULER.DISABLE('SCHEDULED_TRIGGER_PARTITION_BALANCE');
   

   Execute the following statements in an Oracle tenant:

   obclient [SYS]> DELIMITER //
   

   obclient [SYS]> BEGIN 
        DBMS_SCHEDULER.DISABLE('SCHEDULED_TRIGGER_PARTITION_BALANCE'); 
   END;//
   

   obclient [SYS]> DELIMITER ;
   

   For more information about the DISABLE subprogram, see DISABLE.

Modify the scheduling time of the scheduled partition balancing job

After the scheduled partition balancing job is enabled, the system triggers the job at 00:00 every day. You can modify the scheduling time based on your business needs.

1. Log in to a MySQL or Oracle tenant of the cluster as the administrator of the 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. Call the SET_ATTRIBUTE subprogram of the DBMS_SCHEDULER package to modify the next scheduling time of the scheduled partition balancing job. For example, you can set the value of the partition_balance_schedule_interval parameter to 01:00 to enable the job.

   MySQL mode

   Oracle mode

   The following statement sets the scheduling time of the scheduled partition balancing job to 2024-06-12 01:00:00 in a MySQL tenant:

   obclient [oceanbase]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'NEXT_DATE', '2024-06-12 01:00:00');
   

   The following statements set the scheduling time of the scheduled partition balancing job to 12-Jun-2024 01:00:00 in an Oracle tenant:

   obclient [SYS]> DELIMITER //
   

   obclient [SYS]> BEGIN 
        DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'NEXT_DATE', '12-Jun-2024 01:00:00'); 
   END;//
   

   obclient [SYS]> DELIMITER ;
   

   For more information about the SET_ATTRIBUTE subprogram, see SET_ATTRIBUTE.

Modify the scheduling frequency of the scheduled partition balancing job

By default, the system triggers the scheduled partition balancing job once a day. You can modify the scheduling frequency based on your business needs.

1. Log in to a MySQL or Oracle tenant of the cluster as the administrator of the 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. Call the SET_ATTRIBUTE subprogram of the DBMS_SCHEDULER package to modify the scheduling frequency of the scheduled partition balancing job. For example, you can set the scheduled partition balancing job to be triggered once every two days.

   MySQL mode

   Oracle mode

   Here is a sample statement:

   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'REPEAT_INTERVAL', 'FREQ=str_value; INTERVAL=int_value');
   

   The parameters in the statement are described as follows:

   • FREQ: the time unit of the scheduling frequency. Valid values: MINUTELY, HOURLY, DAILY, and WEEKLY.
   • INTERVAL: the frequency at which the scheduled partition balancing job is triggered. It is used in combination with the FREQ parameter. For example, when you set the value of the FREQ parameter to ‘DAILY’ and the value of the INTERVAL parameter to 2, the scheduled partition balancing job is triggered once every two days.

   The following sample statement specifies to trigger the scheduled partition balancing job once every two days in a MySQL tenant:

   obclient [oceanbase]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'REPEAT_INTERVAL', 'FREQ=DAILY; INTERVAL=2');
   

   Here is a sample statement:

   BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'REPEAT_INTERVAL', 'FREQ=str_value; INTERVAL=int_value');
   END;
   

   The parameters in the statement are described as follows:

   • FREQ: the time unit of the scheduling frequency. Valid values: MINUTELY, HOURLY, DAILY, and WEEKLY.
   • INTERVAL: the frequency at which the scheduled partition balancing job is triggered. It is used in combination with the FREQ parameter. For example, when you set the value of the FREQ parameter to ‘DAILY’ and the value of the INTERVAL parameter to 2, the scheduled partition balancing job is triggered once every two days.

   The following sample statements specify to trigger the scheduled partition balancing job once every two days in an Oracle tenant:

   obclient [SYS]> DELIMITER //
   

   obclient [SYS]> BEGIN 
        DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'REPEAT_INTERVAL', 'FREQ=DAILY; INTERVAL=2'); 
   END;//
   

   obclient [SYS]> DELIMITER ;
   

   For more information about the SET_ATTRIBUTE subprogram, see SET_ATTRIBUTE.

Modify the timeout period for a single execution of the scheduled partition balancing job

By default, the timeout period for a single execution of the scheduled partition balancing job is set to 7,200 seconds, namely 2 hours. You can modify the timeout value based on your business needs.

Modifying the timeout period for a single execution of the scheduled partition balancing job actually modifies the execution timeout period for all background partition scheduling jobs triggered by calling the TRIGGER_PARTITION_BALANCE subprogram. Therefore, modifying the timeout value also affects manually triggered partition scheduling jobs. For more information, see Manually trigger a partition balancing job.

1. Log in to a MySQL or Oracle tenant of the cluster as the administrator of the 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. Call the SET_ATTRIBUTE subprogram of the DBMS_SCHEDULER package to modify the timeout period for a single execution of the scheduled partition balancing job.

   MySQL mode

   Oracle mode

   Here is a sample statement:

   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'JOB_ACTION', 'DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(balance_timeout)');
   

   The balance_timeout parameter specifies the timeout period for a single execution of the job, in seconds. If this field is left unspecified, the job will not time out in the background.

   The following statement sets the timeout period for a single execution of the job to 7,200 seconds in a MySQL tenant:

   obclient [oceanbase]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'JOB_ACTION', 'DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(7200)');
   

   Here is a sample statement:

   BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'JOB_ACTION', 'DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(balance_timeout)');
   END;
   

   The balance_timeout parameter specifies the timeout period for a single execution of the job, in seconds. If this field is left unspecified, the job will not time out in the background.

   The following statements set the timeout period for a single execution of the job to 7,200 seconds in an Oracle tenant:

   obclient [SYS]> DELIMITER //
   

   obclient [SYS]> BEGIN 
        DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'JOB_ACTION', 'DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(7200)');
   END;//
   

   obclient [SYS]> DELIMITER ;
   

   For more information about the SET_ATTRIBUTE subprogram, see SET_ATTRIBUTE.

Modify the maximum execution time of a statement that triggers the scheduled partition balancing job

By default, the maximum execution time of a statement that triggers the scheduled partition balancing job is 7,200 seconds, namely 2 hours. We recommend that you do not modify this value.

1. Log in to a MySQL or Oracle tenant of the cluster as the administrator of the 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. Call the SET_ATTRIBUTE subprogram of the DBMS_SCHEDULER package to modify the maximum execution time of a statement that triggers the scheduled partition balancing job.

   MySQL mode

   Oracle mode

   Here is a sample statement:

   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'MAX_RUN_DURATION', 'max_time');
   

   The max_time parameter specifies the maximum execution time of a statement that triggers the scheduled partition balancing job, in seconds.

   The following statement sets the maximum execution time of a statement that triggers the scheduled partition balancing job to 3,600 seconds, namely 1 hour, in a MySQL tenant:

   obclient [oceanbase]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'MAX_RUN_DURATION', '3600');
   

   Here is a sample statement:

   BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'MAX_RUN_DURATION', 'max_time');
   END;
   

   The max_time parameter specifies the maximum execution time of a statement that triggers the scheduled partition balancing job, in seconds.

   The following sample statements set the maximum execution time of a statement that triggers the scheduled partition balancing job to 3,600 seconds, namely 1 hour, in an Oracle tenant:

   obclient [SYS]> DELIMITER //
   

   obclient [SYS]> BEGIN 
        DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'MAX_RUN_DURATION', '3600');
   END;//
   

   obclient [SYS]> DELIMITER ;
   

   For more information about the SET_ATTRIBUTE subprogram, see SET_ATTRIBUTE.

What to do next

You can configure the scheduled partition balancing job only for partition balancing. The scheduled partition balancing job is automatically triggered when specified conditions are met. After the scheduled partition balancing job is triggered, you can:

• Query views for the scheduling history of the partition balancing job. For more information, see Query the scheduling history of the scheduled partition balancing job.

• Query views for the execution records of the scheduled partition balancing job. Partitions are transferred asynchronously by using balancing tasks in the background. For more information, see Query partition balancing jobs and tasks in the background.

  Note

  After the scheduled partition balancing job is triggered, if either of the tenant-level parameters enable_rebalance and enable_transfer is not set to True, the job will fail.

References

• View the parameters of the scheduled partition balancing job

• Manually trigger a partition balancing job