Configure a scheduled partition balancing task

When OceanBase Database creates a user tenant, the system creates a built-in scheduled partition balancing task SCHEDULED_TRIGGER_PARTITION_BALANCE, which triggers partition balancing at 00:00 every day by default. You can set various parameters of the scheduled partition balancing task by using the subprograms of the DBMS_SCHEDULER system package.

Limitations

The scheduled partition balancing task cannot be configured in the sys tenant.

Enable the scheduled partition balancing task

For user tenants created in the current version, the scheduled partition balancing task is enabled by default and does not need to be re-enabled. For user tenants upgraded from a version earlier than V4.4.1 to the current version, the scheduled partition balancing task is disabled by default. You can enable the scheduled partition balancing task by referring to the following operations.

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. Confirm whether the value of the tenant-level configuration parameter partition_balance_schedule_interval is 0s.

   Before you enable the scheduled partition balancing task SCHEDULED_TRIGGER_PARTITION_BALANCE, make sure that the automatic partition balancing task is disabled, that is, the value of the tenant-level configuration parameter partition_balance_schedule_interval is 0.

   Execute the following statements to query whether the value of the tenant-level configuration parameter partition_balance_schedule_interval is 0s. If the value is not 0s, set the 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 system package to enable the scheduled partition balancing task SCHEDULED_TRIGGER_PARTITION_BALANCE.

   MySQL-compatible mode

   Oracle-compatible mode

   The following example shows how to enable the scheduled partition balancing task in a MySQL tenant:

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

   The following example shows how to enable the scheduled partition balancing task 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 task

For user tenants created in the current version, the scheduled partition balancing task is enabled by default. You can disable the scheduled partition balancing task based on your business needs. After the scheduled partition balancing task is disabled, you can initiate partition balancing tasks through automatic triggering (by setting the configuration parameter partition_balance_schedule_interval to a value greater than 0s).

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 DISABLE subprogram of the DBMS_SCHEDULER system package to disable the scheduled partition balancing task SCHEDULED_TRIGGER_PARTITION_BALANCE.

   MySQL-compatible mode

   Oracle-compatible mode

   The following example shows how to disable the scheduled partition balancing task in a MySQL tenant:

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

   The following example shows how to disable the scheduled partition balancing task 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.

Adjust the schedule time of the scheduled partition balancing task

After the scheduled partition balancing task is enabled, the system triggers partition balancing at 00:00 every day by default. You can modify the time as needed.

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 SET_ATTRIBUTE subprogram of the DBMS_SCHEDULER system package to adjust the next schedule time of the partition balancing task. For example, adjust the task to be triggered at 01:00 next time.

   MySQL-compatible mode

   Oracle-compatible mode

   The following example shows how to adjust the execution time of the scheduled partition balancing task 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 example shows how to adjust the execution time of the scheduled partition balancing task 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.

Adjust the schedule frequency of the scheduled partition balancing task

By default, the scheduled partition balancing task runs once a day. You can modify the frequency as needed.

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 SET_ATTRIBUTE subprogram of the DBMS_SCHEDULER system package to adjust the schedule frequency of the partition balancing task. For example, adjust the task to be triggered once every two days.

   MySQL-compatible mode

   Oracle-compatible mode

   The statement is as follows:

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

   The parameters are described as follows:

   • FREQ: the time unit of the schedule frequency. Valid values are MINUTELY (minute), HOURLY (hour), DAILY (day), and WEEKLY (week).
   • INTERVAL: the specific value, that is, the interval of time units at which the task is triggered. For example, FREQ=DAILY; INTERVAL=2 indicates that the task is triggered once every two days.

   The following example shows how to adjust the execution frequency of the scheduled partition balancing task to 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');
   

   The statement is as follows:

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

   The parameters are described as follows:

   • FREQ: the time unit of the schedule frequency. Valid values are MINUTELY (minute), HOURLY (hour), DAILY (day), and WEEKLY (week).
   • INTERVAL: the specific value, that is, the interval of time units at which the task is triggered. For example, FREQ=DAILY; INTERVAL=2 indicates that the task is triggered once every two days.

   The following example shows how to adjust the execution frequency of the scheduled partition balancing task to 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.

Adjust the timeout period for a single execution of the scheduled partition balancing task

By default, a single execution of the scheduled partition balancing task has no timeout limit. You need to modify the timeout period based on your actual business requirements.

Adjusting the timeout period for a single execution of the scheduled partition balancing task actually adjusts the execution timeout period of all background partition balancing tasks triggered by calling the TRIGGER_PARTITION_BALANCE subprogram. Therefore, adjusting this value also affects the partition balancing tasks triggered manually. For more information about how to manually trigger partition balancing, see Manually trigger 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 SET_ATTRIBUTE subprogram of the DBMS_SCHEDULER system package to adjust the timeout period for a single execution of the task.

   MySQL-compatible mode

   Oracle-compatible mode

   The statement is as follows:

   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 task, in seconds. If the value is empty, the background balancing task has no timeout limit.

   The following example shows how to adjust the timeout period for a single execution of the task to 7200 seconds in a MySQL tenant:

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

   The statement is as follows:

   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 task, in seconds. If the value is empty, the background balancing task has no timeout limit.

   The following example shows how to adjust the timeout period for a single execution of the task to 7200 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.

Adjust the maximum execution time of the statement that triggers partition balancing

By default, the maximum execution time of the statement that triggers partition balancing is 7200 seconds, that is, 2 hours. We recommend that you do not modify this value.

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 SET_ATTRIBUTE subprogram of the DBMS_SCHEDULER system package to adjust the maximum execution time of the statement that triggers partition balancing.

   MySQL-compatible mode

   Oracle-compatible mode

   The statement is as follows:

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

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

   The following example shows how to adjust the maximum execution time of the partition balancing statement to 3600 seconds (1 hour) in a MySQL tenant:

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

   The statement is as follows:

   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 the statement that triggers partition balancing, in seconds.

   The following example shows how to adjust the maximum execution time of the partition balancing statement to 3600 seconds (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

The scheduled task is used only to trigger the partition balancing task. When the trigger conditions are met, the system automatically triggers the partition balancing task. After the partition balancing task is triggered:

• You can view the triggering history of the scheduled partition balancing task through a view. For more information, see View the triggering history of the scheduled partition balancing task.

• 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.

  Note

  After the partition balancing task is triggered, if the values of the tenant-level configuration parameters enable_rebalance and enable_transfer are not both True, the scheduled task fails to execute.

References

• View the configurations of the scheduled partition balancing task

• Manually trigger partition balancing