Window compaction

Window compaction is a new compaction mode introduced in OceanBase Database. It is automatically scheduled and executed by the system within a specified time window. Window compaction is particularly suitable for scenarios with a large number of partitions, unevenly distributed incremental data, distinct business peak and off-peak periods, and the need for fine-grained control over compaction resources to avoid impacting business performance.

Starting from OceanBase Database V4.6.0, window compaction is supported for V4.6.x versions. Compared to existing tenant-level compactions, window compaction does not initiate compactions for all tables. Instead, it initiates compactions on partitions with incremental data or those meeting specific compaction conditions. Window compaction tasks are only initiated within the specified time window. After reaching the preset execution duration, the system automatically switches back to the adaptive compaction mode, ensuring controllable compaction time and resource usage.

Furthermore, in OceanBase clusters with cgroups enabled, users can configure dedicated resource plans for window compaction. This allows for allocating more CPU/IO resources to compaction tasks during the specified window period, maximizing the use of idle time for data organization.

Background information

Window compaction essentially schedules partition-level compactions. During window compaction, the system collects statistics for each partition and scores each partition. The relevant scoring factors include:

• Incremental row count (inc_row_cnt)

• Days since the last compaction

• Table query frequency (hotness)

• Number of SSTables

When scheduling window compactions, the system prioritizes high-priority tasks over low-priority ones and processes them in batches. It also limits the maximum number of unexecuted compaction tasks per tenant to ensure that the number of compaction tasks and their completion time are under control.

Limitations and considerations

• Window compaction does not support data verification for the main table and index table.

• At any given time, only one of tenant compaction or window compaction can be in progress. Tenant compaction can interrupt window compaction, but window compaction cannot be scheduled while tenant compaction is ongoing.

• During window compaction, if you set the enable_window_compaction parameter to False (enable_window_compaction = False), window compaction will stop immediately.

• During tenant compaction, if you set the enable_window_compaction parameter to True (enable_window_compaction = True), window compaction will be initiated only after daily compaction is completed.

• During window compaction, you cannot use the ALTER SYSTEM SET statement to modify the tenant-level compaction_low_thread_score parameter. Otherwise, an error will occur. If you need to adjust the number of threads for window compaction, use the DBMS_DAILY_MAINTENANCE.SET_THREAD_COUNT() function.

Prerequisites

• Before triggering a compaction, ensure that the global compaction switch is enabled. The global compaction switch is controlled by the cluster-level enable_major_freeze parameter, which defaults to True, indicating that the compaction switch is enabled.

  If the global compaction switch is not enabled for your cluster, follow these steps to enable it:

  1. Log in to the sys tenant of the cluster as the root user.

  2. Check whether the global compaction switch is enabled.

     obclient(root@sys)[(none)]> SHOW PARAMETERS LIKE 'enable_major_freeze';
     

  3. If it is not enabled, execute the following statement to enable the compaction switch.

     obclient(root@sys)[(none)]> ALTER SYSTEM SET enable_major_freeze='True';
     

• To trigger window compaction, ensure that the tenant-level enable_window_compaction parameter is set to True, indicating that window compaction is enabled.

  Here are the steps:

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

  2. Check the value of the tenant-level enable_window_compaction parameter.

     obclient> SHOW PARAMETERS LIKE 'enable_window_compaction';
     

  3. If the value is False, execute the following statement to enable window compaction.

     obclient> ALTER SYSTEM SET enable_window_compaction = True;
     

     In the system tenant, you can specify the tenant name to set the parameter, for example: ALTER SYSTEM SET enable_window_compaction = True TENANT = tenant_name;.

Configure periodic trigger window compaction

Similar to the collection of daily statistics, for each user tenant in the current version, the DAILY_MAINTENANCE_WINDOW task is registered in the DBMS_SCHEDULER task scheduler. Users can flexibly define the start and end times, duration, and other parameters of the window compaction task based on their business needs.

Enable window compaction

To trigger window compaction, you must manually enable the window compaction task.

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

   The following example shows how to connect to the database. Please replace the actual environment variables with the correct values.

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

2. Call the ENABLE subprogram of the DBMS_SCHEDULER system package to enable window compaction.

   The execution statement is the same in MySQL mode and Oracle mode. Here is an example:

   obclient> CALL DBMS_SCHEDULER.ENABLE('DAILY_MAINTENANCE_WINDOW');
   

After window compaction is enabled, the default start time is 02:00 in the early morning, the duration is 6 hours, and the cycle is daily.

Modify the next start time of the window compaction task

After window compaction is enabled, the default next start time of the window compaction task is 02:00 in the early morning. You can configure the next start time of the window compaction task based on your business needs.

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

   The following example shows how to connect to the database. Please replace the actual environment variables with the correct values.

   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 set the next start time of the window compaction task.

   The statement is as follows:

   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('DAILY_MAINTENANCE_WINDOW','NEXT_DATE', 'next_time');
   

   In the statement, next_time specifies the next start time of the window compaction task.

   Here is an example:

   MySQL mode

   Oracle mode

   For example, in MySQL mode, set the next start time of the window compaction task to 2026-03-10 01:00:00.

   obclient(root@mysql001)[(none)]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('DAILY_MAINTENANCE_WINDOW', 'NEXT_DATE', '2026-03-10 01:00:00');
   

   In Oracle mode, set the next start time of the window compaction task to 2026-03-10 01:00:00.

   obclient(SYS@oracle001)[SYS]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('DAILY_MAINTENANCE_WINDOW', 'NEXT_DATE', '10-MAR-2026 01:00:00 AM');
   

Configure the duration of the window compaction task

After window compaction is enabled, the default duration of the window compaction task is 6 hours. You can adjust the duration based on your business needs.

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

   The following example shows how to connect to the database. Please replace the actual environment variables with the correct values.

   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 set the duration of the window compaction task.

   The statement is as follows:

   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('DAILY_MAINTENANCE_WINDOW', 'DURATION', duration);
   

   In the statement, duration specifies the duration of the window compaction task, in seconds.

   Notice

   • Modifying the duration of the current task will affect the ongoing task. For example, reducing the value of duration will cause the ongoing window compaction task to end earlier.
   • Since window compaction is scheduled once a day, the value of duration must be less than 24 hours (86400).

   For example, set the duration of the window compaction task to 10 hours, which is 36000 seconds. The execution statement is the same in MySQL mode and Oracle mode. Here is an example:

   obclient> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('DAILY_MAINTENANCE_WINDOW', 'DURATION', 36000);
   

Adjust the number of threads for window compaction

During window compaction, the number of compaction threads is not controlled by the tenant-level configuration item compaction_low_thread_score, but by the thread_count parameter in the DBMS_DAILY_MAINTENANCE.SET_THREAD_COUNT subprogram. The default value is 0, which means that the system calculates the number of compaction threads based on tenant-related configurations. You can also adjust the number of threads for window compaction based on your business needs.

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

   The following example shows how to connect to the database. Please replace the actual environment variables with the correct values.

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

2. Call the SET_THREAD_COUNT subprogram of the DBMS_DAILY_MAINTENANCE system package to set the number of compaction threads for window compaction.

   The statement is as follows:

   DBMS_DAILY_MAINTENANCE.SET_THREAD_COUNT(thread_count);
   

   For example, set the number of threads for the COMPACTION_LOW background compaction thread to 8. The execution statement is the same in MySQL mode and Oracle mode. Here is an example:

   obclient> CALL DBMS_DAILY_MAINTENANCE.SET_THREAD_COUNT(8);
   

(Optional) Configure a resource management plan for window compaction

In a cluster with cgroup enabled, window compaction supports switching to a specific resource management plan during compaction. This provides more compaction resources to the compaction task, allowing it to execute faster and cover more partitions, ensuring efficient partition operations during peak business hours.

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

2. Call the CREATE_CONSUMER_GROUP subprogram in the DBMS_RESOURCE_MANAGER system package to create a resource group for window compaction.

   Notice

   The resource group name for window compaction, INNER_DAILY_WINDOW_COMPACTION_LOW_GROUP, is fixed and cannot be changed at will.

   The execution statements are the same in MySQL and Oracle modes. Here is an example:

   obclient> CALL DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
     CONSUMER_GROUP => 'INNER_DAILY_WINDOW_COMPACTION_LOW_GROUP',
     COMMENT => 'consumer group for daily window maintenance job'
   );
   

3. Call the CREATE_PLAN subprogram in the DBMS_RESOURCE_MANAGER system package to create a resource management plan for window compaction.

   Notice

   The resource management plan name for window compaction, INNER_DAILY_WINDOW_PLAN, is fixed and cannot be changed at will.

   The execution statements are the same in MySQL and Oracle modes. Here is an example:

   obclient> CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN(
     PLAN => 'INNER_DAILY_WINDOW_PLAN',
     COMMENT => 'plan for daily window maintenance job'
   );
   

4. Call the CREATE_PLAN_DIRECTIVE subprogram in the DBMS_RESOURCE_MANAGER system package to create the content of the resource management plan.

   The execution statements are the same in MySQL and Oracle modes. Here is an example:

   obclient> CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
     PLAN => 'INNER_DAILY_WINDOW_PLAN',
     GROUP_OR_SUBPLAN => 'INNER_DAILY_WINDOW_COMPACTION_LOW_GROUP',
     COMMENT => 'directive for window compaction',
     MAX_IOPS => 100,
     MIN_IOPS => 30,
     WEIGHT_IOPS => 50,
     UTILIZATION_LIMIT => 100,
     MGMT_P1 => 70
   );
   

After the configuration is successful, when a window compaction task starts, the system automatically switches the current resource management plan to the window compaction-specific plan INNER_DAILY_WINDOW_PLAN and maps the current COMPACTION_LOW function threads (background compaction task threads) to the window compaction-specific resource group INNER_DAILY_WINDOW_COMPACTION_LOW_GROUP.

Manually trigger window compaction

After window compaction is enabled, if the scheduled execution time has not yet arrived, you can manually trigger window compaction based on business needs.

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

   Here is an example of connecting to the database. Please replace the placeholders with your actual environment details.

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

2. Call the TRIGGER_WINDOW_COMPACTION_PROC subprogram in the DBMS_DAILY_MAINTENANCE system package to manually trigger window compaction.

   The execution statements are the same in MySQL and Oracle modes. Here is an example:

   obclient> CALL DBMS_DAILY_MAINTENANCE.TRIGGER_WINDOW_COMPACTION_PROC();
   

Disable the window compaction task

If you do not want to trigger window compaction again, you can call the DISABLE subprogram of the DBMS_SCHEDULER system package to disable the window compaction task.

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

   The following example shows how to connect to the database. Replace the actual environment variables as needed.

   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 window compaction.

   The execution statements in MySQL mode and Oracle mode are the same, as shown in the following example:

   obclient> CALL DBMS_SCHEDULER.DISABLE('DAILY_MAINTENANCE_WINDOW');
   

After you disable the window compaction task, the system will not trigger window compaction (including periodic window compaction and manual window compaction). Additionally, if the value of the tenant-level parameter enable_window_compaction is still True, the system will not trigger window compaction or daily tenant compaction. If you want to trigger daily tenant compaction after disabling the window compaction task, you need to set the value of the tenant-level parameter enable_window_compaction to False. For more information about how to enable daily tenant compaction, see Trigger daily tenant compaction periodically.

The following table describes the relationship between the values of the parameter enable_window_compaction and the window compaction task DAILY_MAINTENANCE_WINDOW and the daily compaction behavior:

References

• Trigger daily tenant compaction periodically

• Manually trigger a major compaction

• Manually control a major compaction

• View the major compaction information