When you create a user tenant in OceanBase AI Database, the system provides a built-in scheduled partition balancing task named SCHEDULED_TRIGGER_PARTITION_BALANCE, which triggers partition balancing by default at 00:00 every day. You can use the subprograms of the DBMS_SCHEDULER system package to set parameters for the scheduled partition balancing task.
Limitations
Scheduled partition balancing tasks cannot be configured in the sys tenant.
Enable a scheduled partition balancing task
For a newly created user tenant, the scheduled partition balancing task is enabled by default and does not need to be re-enabled.
Log in to the cluster as a tenant administrator.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -ACheck whether the value of the tenant-level parameter
partition_balance_schedule_intervalis0s.Before enabling the scheduled partition balancing task
SCHEDULED_TRIGGER_PARTITION_BALANCE, you must ensure that automatic partition balancing is disabled. That is, the value of the tenant-level parameterpartition_balance_schedule_intervalmust be0.Run the following statements to check whether the value of the tenant-level parameter
partition_balance_schedule_intervalis0s. If it is not0s, set its value to0s.obclient> SHOW PARAMETERS LIKE '%partition_balance_schedule_interval%';obclient> ALTER SYSTEM SET partition_balance_schedule_interval='0s';Call the
ENABLEsubprogram of theDBMS_SCHEDULERsystem package to enable the scheduled partition balancing taskSCHEDULED_TRIGGER_PARTITION_BALANCE.The following example shows how to enable the scheduled partition balancing task for a user tenant:
obclient [oceanbase]> CALL DBMS_SCHEDULER.ENABLE('SCHEDULED_TRIGGER_PARTITION_BALANCE');
Disable a scheduled partition balancing task
For a newly created user tenant in the current version, the scheduled partition balancing task is enabled by default. You can also disable it based on your business requirements. After disabling the scheduled partition balancing task, you can still initiate a partition balancing task through automatic triggering (by setting the parameter partition_balance_schedule_interval to a value greater than 0s) or manual triggering.
Log in to the user tenant of the cluster as a tenant administrator.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -ACall the
DISABLEsubprogram of theDBMS_SCHEDULERsystem package to disable the scheduled partition balancing taskSCHEDULED_TRIGGER_PARTITION_BALANCE.The following example shows how to disable the scheduled partition balancing task for a user tenant:
obclient [oceanbase]> CALL DBMS_SCHEDULER.DISABLE('SCHEDULED_TRIGGER_PARTITION_BALANCE');
Adjust the scheduling time of a scheduled partition balancing task
After enabling a scheduled partition balancing task, the system triggers partition balancing by default at 00:00 every day. You can modify this schedule as needed.
Log in to the user tenant of the cluster as the tenant administrator.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -ACall the
SET_ATTRIBUTEsubprogram of theDBMS_SCHEDULERsystem package to adjust the next scheduled time for the partition balancing task. For example, you can set it to trigger partition balancing at 01:00 next time.The following example shows how to adjust the execution time of a scheduled partition balancing task 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');
Adjust the scheduling frequency of a scheduled partition balancing task
By default, a scheduled partition balancing task runs once a day. You can modify this frequency as needed.
Log in to the user tenant of the cluster as the tenant administrator.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -ACall the
SET_ATTRIBUTEsubprogram of theDBMS_SCHEDULERsystem package to adjust the scheduling frequency of the partition balancing task. For example, you can set it to trigger partition balancing every two days.The statement is as follows:
CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'REPEAT_INTERVAL', 'FREQ=str_value; INTERVAL=int_value');where:
FREQ: the unit of time for the scheduling frequency. Valid values areMINUTELY(minutes),HOURLY(hours),DAILY(days), andWEEKLY(weeks).INTERVAL: the interval, that is, how often the task is triggered. For example,FREQ=DAILY; INTERVAL=2indicates that the task is triggered every two days.
The following example shows how to adjust the execution frequency of a scheduled partition balancing task to trigger every two days:
obclient [oceanbase]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'REPEAT_INTERVAL', 'FREQ=DAILY; INTERVAL=2');
Adjust the timeout period for a single execution of a scheduled partition balancing task
By default, there is no timeout period for a single execution of a scheduled partition balancing task. You need to modify this setting based on your business requirements.
Adjusting the timeout period for a single execution of a scheduled partition balancing task actually adjusts the execution timeout for all background partition balancing tasks triggered by calling the TRIGGER_PARTITION_BALANCE subprogram. Therefore, modifying this value also affects partition balancing tasks triggered manually. For more information about how to manually trigger partition balancing, see Manually trigger partition balancing.
Log in to the user tenant of the cluster as the tenant administrator.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -ACall the
SET_ATTRIBUTEsubprogram of theDBMS_SCHEDULERpackage to adjust the timeout period for a single task execution.The syntax is as follows:
CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'JOB_ACTION', 'DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(balance_timeout)');Here,
balance_timeoutspecifies the timeout period for a single task execution, in seconds. If this parameter is left empty, the background balancing task will not have a timeout period.The following example shows how to set the timeout period for a single task execution to 7,200 seconds in a user tenant:
obclient [oceanbase]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'JOB_ACTION', 'DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(7200)');
Adjust the maximum execution time of the partition balancing trigger statement
The default maximum execution time for the partition balancing trigger statement is 7,200 seconds, which is 2 hours. It is not recommended to modify this value.
Log in to the user tenant of the cluster as the tenant administrator.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -ACall the
SET_ATTRIBUTEsubprogram of theDBMS_SCHEDULERpackage to adjust the maximum execution time of the partition balancing trigger statement.The syntax is as follows:
CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'MAX_RUN_DURATION', 'max_time');Here,
max_timespecifies the maximum execution time for the partition balancing trigger statement, in seconds.The following example shows how to set the maximum execution time for the partition balancing trigger statement to 3,600 seconds (1 hour) in a user tenant:
obclient [oceanbase]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'MAX_RUN_DURATION', '3600');
What to do next
Scheduled tasks are only used to trigger partition balancing tasks. The system automatically triggers a partition balancing task when the triggering conditions are met. After a partition balancing task is triggered:
You can view the trigger history of scheduled partition balancing tasks by querying a view. For more information, see View the trigger history of scheduled partition balancing tasks.
The actual partition transfer is performed asynchronously by the background balancing task. You can view the execution process of the partition balancing task by querying a view. For more information, see View background partition balancing tasks.
Note
After a partition balancing task is successfully triggered, if either the tenant-level configuration item
enable_rebalanceorenable_transferis not set toTrue, the scheduled task will fail.