Configure a scheduled partition balance task

When you create a user tenant in OceanBase Database, the system has a built-in scheduled partition balancing task named SCHEDULED_TRIGGER_PARTITION_BALANCE that is triggered at 00:00 every day by default. You can use the subprograms of the DBMS_SCHEDULER system package to configure the parameters of the scheduled partition balance task.

Limitations

You cannot configure a scheduled partition balancing task in the sys tenant.

Enable a scheduled partition balance task

For a user tenant created in the current version, the scheduled partition balance task is enabled by default. You do not need to enable it again. For a user tenant upgraded from V4.2.3 or earlier to the current version, the scheduled partition balance task is disabled by default. You can perform the following steps to enable it.

1. Log in to the MySQL-compatible or Oracle-compatible tenant of the cluster as the tenant administrator.

   The following example shows how to connect to the database. Please adjust the connection details according to your actual environment.

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

2. Check whether the value of the partition_balance_schedule_interval parameter at the tenant level is 0s.

   Before you enable the scheduled partition balance task SCHEDULED_TRIGGER_PARTITION_BALANCE, make sure that the automatic partition balance task is not enabled. This means that the value of the partition_balance_schedule_interval parameter at the tenant level must be 0.

   Check whether the value of the partition_balance_schedule_interval parameter at the tenant level is 0s. If not, set it to 0s by executing the following statement:

   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 balance 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-compatible 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-compatible 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 tenants created in the current version, the scheduled partition balancing task is enabled by default. You can disable the task based on your business needs. After disabling the task, you can still initiate the partition balancing task manually or automatically (by setting the partition_balance_schedule_interval parameter to a value greater than 0s).

1. Log in to the MySQL-compatible or Oracle-compatible tenant of the cluster as the tenant administrator.

   The following example shows how to connect to the database. Please adjust the connection details according to 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_TRIGGER_PARTITION_BALANCE task.

   MySQL-compatible mode

   Oracle-compatible mode

   The following example shows how to disable the scheduled partition balancing task in a MySQL-compatible 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-compatible 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 scheduling time of the scheduled partition balancing task

After you enable the periodic partition balance task, the system triggers the partition balance at 00:00 every day by default. You can modify the scheduling time as needed.

1. Log in to the MySQL-compatible or Oracle-compatible tenant of the cluster as the tenant administrator.

   The following example shows how to connect to the database. Please adjust the connection details according to 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 change the next scheduling time of the partition balance task. For example, you can change the next scheduling time to 01:00.

   MySQL-compatible mode

   Oracle-compatible mode

   The following example shows how to change the execution time of the scheduled partition balancing task in a MySQL-compatible tenant to 2024-06-12 01:00:00:

   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 change the execution time of the scheduled partition balancing task in an Oracle-compatible tenant to 12-Jun-2024 01:00:00:

   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 scheduling frequency of the scheduled partition balancing task

By default, the scheduling frequency of the scheduled partition balancing task is once a day. You can change it as needed.

1. Log in to the MySQL-compatible or Oracle-compatible tenant of the cluster as the tenant administrator.

   The following example shows how to connect to the database. Please adjust the connection details according to 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 change the scheduling frequency of the partition balancing task. For example, you can set the task to be triggered 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');
   

   Where:

   • FREQ: specifies the time unit for the scheduling frequency. Valid values: MINUTELY (minutes), HOURLY (hours), DAILY (days), and WEEKLY (weeks).
   • INTERVAL: specifies the specific value, that is, how often the task is triggered. For example, FREQ=DAILY; INTERVAL=2 indicates that the task is triggered every two days.

   The following example shows how to change the execution frequency of the scheduled partition balancing task in a MySQL-compatible tenant to be triggered every two days:

   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;
   

   Where:

   • FREQ: specifies the time unit for the scheduling frequency. Valid values: MINUTELY (minutes), HOURLY (hours), DAILY (days), and WEEKLY (weeks).
   • INTERVAL: specifies the specific value, that is, how often the task is triggered. For example, FREQ=DAILY; INTERVAL=2 indicates that the task is triggered every two days.

   The following example shows how to change the execution frequency of the scheduled partition balancing task in an Oracle-compatible tenant to be triggered every two days:

   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 for a single scheduled partition balancing task execution

By default, there is no timeout for a single scheduled partition balancing task execution. You need to modify this based on your business requirements.

Actually, adjusting the timeout for a single scheduled partition balancing task execution adjusts the execution timeout for all background partition balancing tasks triggered by calling the TRIGGER_PARTITION_BALANCE procedure. Therefore, adjusting this value also affects manually triggered partition balancing tasks. For more information about how to manually trigger a partition balancing task, see Manually trigger a partition balancing task.

1. Log in to the MySQL-compatible or Oracle-compatible tenant of the cluster as the tenant administrator.

   The following example shows how to connect to the database. Please adjust the connection details according to your actual environment.

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

2. Call the SET_ATTRIBUTE subprogram in the DBMS_SCHEDULER system package to adjust the timeout for a single partition balancing 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 for a single task execution, in seconds. If this parameter is empty, the background balancing task has no timeout.

   The following example shows how to set the timeout for a single task execution to 7200 seconds in a MySQL-compatible 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 for a single task execution, in seconds. If this parameter is empty, the background balancing task has no timeout.

   The following example shows how to set the timeout for a single task execution to 7200 seconds in an Oracle-compatible 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 for the partition balancing trigger statement

The default maximum execution time for the partition balancing trigger statement is 7200 seconds (2 hours). We recommend that you do not change this value.

1. Log in to the MySQL-compatible or Oracle-compatible tenant of the cluster as the tenant administrator.

   The following example shows how to connect to the database. Please adjust the connection details according to 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 for the partition balancing trigger statement.

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

   The following example shows how to adjust the maximum execution time for the partition balancing trigger statement to 3600 seconds (1 hour) in a MySQL-compatible 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 for the partition balancing trigger statement, in seconds.

   The following example shows how to adjust the maximum execution time for the partition balancing trigger statement to 3600 seconds (1 hour) in an Oracle-compatible 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 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 trigger history of the scheduled partition balancing task in a view. For more information, see View the trigger 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 in a view. For more information, see View the background partition balancing task.

  Note

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

References

• View the parameters of a scheduled partition balancing task

• Manually trigger a partition balancing task