PolarDB-X Enterprise Edition cold data cleanup-PolarDB(PolarDB)-阿里云帮助中心

This topic describes the cold data cleanup algorithm for TTL tables and how to manage cleanup tasks.

Scope

Row-based archiving

For engine version MySQL 5.7, the instance version must be polardb-2.4.0_5.4.19-20240927_xcluster5.4.19-20240920 or later.
For engine version MySQL 8.0, the instance version must be polardb-2.4.0_5.4.19-20240927_xcluster8.4.19-20240924 or later.

Partition-based archiving

For engine version MySQL 5.7, the instance version must be polardb-2.5.0_5.4.20-20250328_xcluster5.4.20-20250221 or later.
For engine version MySQL 8.0, the instance version must be polardb-2.5.0_5.4.20-20250328_xcluster8.4.20-20250304 or later.

Note

For information about the instance version naming rules, see Release notes.
For information about how to view the version of an instance, see View and update the engine version of a PolarDB for Xscale instance.

Start a cleanup task

Before you start a data cleanup task, you must enable data cleanup for the TTL table:

ALTER TABLE `my_ttl_tbl` 
MODIFY TTL 
SET
TTL_CLEANUP = 'ON';

You can trigger a cleanup task for expired data in a TTL table in two ways:

Automatically: The system automatically triggers cleanup tasks for expired data based on the schedule and frequency defined by TTL_JOB.
Manually: Run the ALTER CLEANUP EXPIRED DATA command to trigger a cleanup task for expired data.

Note

To set the TTL_CLEANUP parameter, the instance version must be polardb-2.5.0_5.4.20-20250328_xcluster5.4.20-20250221 or polardb-2.5.0_5.4.20-20250328_xcluster8.4.20-20250304, or a later version. Earlier versions do not require this parameter and perform expired data cleanup by default.
Because the default value of the TTL_CLEANUP parameter is OFF, you typically need to set both the TTL_ENABLE and TTL_CLEANUP parameters to ON to enable scheduled cleanup of expired data.

Automatic background cleanup

To enable the scheduled cleanup task, set both TTL_ENABLE and TTL_CLEANUP to ON:

ALTER TABLE `my_ttl_tbl` 
MODIFY TTL 
SET
TTL_ENABLE = 'ON',
TTL_CLEANUP = 'ON';

After you enable the scheduled cleanup task, you can also adjust the schedule frequency (TTL_JOB).

Manually trigger a cleanup task

Add the suffix ASYNC = TRUE to run the cleanup task asynchronously:

ALTER TABLE `my_ttl_tbl` CLEANUP EXPIRED DATA ASYNC = TRUE;

Note

The ALTER TABLE CLEANUP EXPIRED DATA statement is supported only for TTL tables.
By default, a maximum of two TTL table cleanup tasks can run concurrently across the system. The default maximum cleanup speed for each DN is 1,000 rows per second. The more DNs you have, the faster the cleanup. The total speed is N * 1,000 rows/s, where N is the number of DNs. You can also adjust the row-based cleanup speed limit as needed.
Because the cleanup speed is limited, the time defined by TTL_JOB is only the expected execution time. The task must wait for preceding tasks in the queue to complete.
By default, the cleanup task treats data rows with a NULL value in the TTL column as having the minimum value and cleans them up. You can set TTL_FILTER = COND_EXPR( <ttl_col_name> IS NOT NULL )) to prevent the cleanup task from automatically cleaning up data rows with a NULL value in the TTL column. This setting is supported only on instance versions polardb-2.5.0_5.4.20-20250328_xcluster5.4.20-20250221 (MySQL 5.7) and later or polardb-2.5.0_5.4.20-20250328_xcluster8.4.20-20250304 (MySQL 8.0) and later.

Cold data cleanup algorithm for TTL tables

TTL tables have the following two data cleanup strategies:

Row-based cleanup: Uses DELETE DML operations to clear expired data.
Partition-based cleanup: Uses DDL operations that delete RANGE partitions to clear expired data.

Row-based cleanup

By default, an algorithm that gradually cleans up data from oldest to newest is used. The cleanup process always starts by clearing the oldest data in the table. After multiple rounds of cleanup, the most recent data is dynamically retained.

Because row-based cleanup uses DML statements and distributed transactions, it generates row locks and binary logs. To avoid creating large-range row locks or consuming excessive CPU and I/O resources during data cleanup, the daily scheduled TTL task automatically calculates a small, appropriate time range for cleanup based on the minimum value of the TTL column.

Note

Row-based cleanup generates many binary logs when using DELETE statements to clear expired data. If a downstream application needs to subscribe to binary logs through PolarDB-X CDC, you can execute the following SQL statement on the primary instance. This makes CDC automatically filter the DELETE binary log events generated during the cleanup process:

SET CDC GLOBAL TASK_EXTRACT_FILTER_ARCHIVE_ENABLED = TRUE;
STOP MASTER;
START MASTER;

This SQL statement must be executed by a privileged account and is supported only for TTL tables that do not have a regular columnstore index. Do not execute this SQL statement on a TTL table that has a columnstore index. After the statement takes effect, binary logs no longer generate DELETE events caused by the TTL table. Execute this SQL statement only after you confirm that no downstream systems need to synchronize these DELETE events.

Cleanup scope algorithm

Assume a TTL definition is TTL_EXPR = `time` EXPIRE AFTER <NUM> MONTH TIMEZONE '+08:00'. This definition means that the TTL table retains data from the most recent NUM months. The upper bound for each cleanup task is calculated as follows:

// Target upper bound of the cleanup time range
CleanupMaxUpperBound = Now - ExpiredDataInterval

// Upper bound of the current cleanup time range
CleanupUpperBound = MIN((MinValue + CleanupDataInterval), (Now - ExpiredDataInterval))

Description:

Parameter	Meaning	Details
MinValue	The value generated after the minimum value of the TTL time column is adjusted for time granularity.	Time granularity adjustment refers to truncating and rounding down the time based on the `IntervalTimeUnit` of the TTL definition's expiration interval. For more information, see Other definitions. For example, if the time is `'2023-03-02 01:02:04'`, the results of time granularity processing with different `IntervalTimeUnit` values are as follows: Truncated by DAY, the value is `'2023-03-02 00:00:00'`. Truncated by MONTH, the value is `'2023-03-01 00:00:00'`. Truncated by YEAR, the value is `'2023-01-01 00:00:00'`. Note that when `TTL_EXPR` uses different `IntervalTimeUnit` values, the cleanup scope for cold data varies, even with the same current time and expiration interval. For example, assume the current time is `'2023-03-02 01:02:04'`. Different `IntervalTimeUnit` values result in the following cleanup scopes: `EXPIRE AFTER 12 MONTH` (retains data from the last 12 months): The actual maximum cleanup scope is `ttl_col < '2023-03-01 00:00:00'`. `EXPIRE AFTER 1 YEAR` (retains data from the last year): The actual maximum cleanup scope is `ttl_col < '2022-01-01 00:00:00'`.
Now	The value generated after the current time is adjusted for time granularity.
CleanupDataInterval	The length of the time range for each data cleanup round.	The calculation method is `TTL_CLEANUP_BOUND_INTERVAL_COUNT*IntervalTimeUnit`. The larger the TTL_CLEANUP_BOUND_INTERVAL_COUNT value, the more data is cleared in a single task, and the longer the cleanup takes.
ExpiredDataInterval	The retention period for the TTL table data.	For example: `EXPIRE AFTER 12 MONTH` (retains data from the last 12 months).

Data cleanup example

The following example shows a TTL definition. Assume the current time is 2023-10-01:

CREATE TABLE `tbl` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`time` datetime DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`id`)
PARTITIONS 8;

ALTER TABLE `tbl`
MODIFY TTL
SET
TTL_EXPR = `time` EXPIRE AFTER 1 MONTH TIMEZONE '+08:00';

The cleanup process is shown in the following figure:

Note

Day 1:
The minimum time value of the time column in the TTL definition is 2022-10-05 (MinValue). Based on the cleanup time range (CleanupDataInterval), which is 3 months in this example, the cleanup scope for this round is 2022-10-05 ≤ Time < 2023-01-01. The other cleanup scopes are 2023-01-01 ≤ Time < 2023-04-01, 2023-04-01 ≤ Time < 2023-07-01, and 2023-07-01 ≤ Time < 2023-09-01.
Day 2:
Same as Day 1. Data that meets the condition 2023-01-01 ≤ Time < 2023-04-01 is cleared. In this case, 2023-04-01 is the upper bound for this cleanup task (CleanupUpperBound).
Day 3:
Same as Day 1. Data that meets the condition 2023-04-01 ≤ Time < 2023-07-01 is cleared. In this case, 2023-07-01 is the upper bound for this cleanup task (CleanupUpperBound).
Day 4:
This is slightly different. Data that meets the condition 2023-07-01 ≤ Time < 2023-09-01 is cleared. The time range is 2 months. This is because the assumed current time in the figure is 2023-10-01 (Now), and the TTL table is set to retain data for the last month (ExpiredDataInterval is the TTL for the table data). Therefore, this round can only clear data before 2023-09-01. In this case, 2023-09-01 is the upper bound for this cleanup task (CleanupUpperBound).

Partition-based cleanup

For partition-based archiving scenarios, different cleanup logic is used based on the partition expiration policy:

Expiration by fixed time interval: The TTL task calculates the target time range for the current cleanup based on the data expiration interval set in the TTL definition and the current time. It then scans all RANGE partitions. If a partition's data falls completely within the target time range, the partition is considered expired and is deleted.
Expiration by fixed number of partitions: The TTL task determines which partitions are expired based on the number of RANGE partitions to retain, as set in the TTL definition. It starts from the smallest RANGE partition and checks each one until the number of remaining partitions equals the number specified in the TTL definition.

Example of expiration by fixed time interval

Create a range-partitioned table named tbl:

CREATE TABLE `tbl_range` (`time` DATETIME)
PARTITION BY RANGE (`time`)
(
  PARTITION p20231001 VALUES LESS THAN('2023-10-01'),
  PARTITION p20231101 VALUES LESS THAN('2023-11-01'),
  PARTITION p20231201 VALUES LESS THAN('2023-12-01'),
  PARTITION p20240101 VALUES LESS THAN('2024-01-01')
);

The tbl table has the following TTL definition:

ALTER TABLE `tbl_range` 
MODIFY TTL 
SET 
TTL_EXPR = `time` EXPIRE AFTER 1 MONTH TIMEZONE '+08:00',
TTL_PART_INTERVAL = INTERVAL(1, MONTH),
ARCHIVE_TYPE = 'PARTITION';

Note

The expiration interval for tbl_range is 1 month, the partition interval is one month, and it is a hash partition.

The cleanup process is shown in the following figure:
Note
As shown in the figure, when a new TTL task runs, if the current time is 2023-12-03, the current time is first truncated to 2023-12-01 based on the partition interval unit MONTH. Then, the expiration interval of 1 month is subtracted. The final expiration time point is 2023-11-01. Therefore, partitions p20231101 and p20231001 are identified as expired and deleted because all their data meets the expiration condition time < '2023-11-01'. In addition, before deleting the expired partitions p20231101 and p20231001, the TTL task checks and automatically adds a new RANGE partition p20240201 as needed.

Example of expiration by fixed number of partitions

This expiration policy is typically used when the TTL column is of an integer type.

Create the table tbl, partitioned into 4 partitions by an integer column:

CREATE TABLE `tbl_int` (`uid` BIGINT)
PARTITION BY RANGE (`uid`)
(
  PARTITION p2000000 VALUES LESS THAN(2000000),
  PARTITION p3000000 VALUES LESS THAN(3000000),
  PARTITION p4000000 VALUES LESS THAN(4000000),
  PARTITION p5000000 VALUES LESS THAN(5000000)
);

TTL definition for tbl:
```
ALTER TABLE `tbl_int` 
MODIFY TTL 
SET 
TTL_EXPR = `uid` EXPIRE OVER 2 PARTITIONS,
TTL_PART_INTERVAL = INTERVAL(1000000, NUMBER),
ARCHIVE_TYPE = 'PARTITION';
```
Note
For the TTL table tbl, the range partition interval is 1,000,000. A maximum of two partitions are retained, and archiving is performed based on the hash partition.
The cleanup process is shown in the following figure:
Note
As shown in the figure, p2000000, p3000000, p4000000, and p5000000 are the currently created RANGE partitions of the TTL table tbl_int. When a new TTL task runs, based on the maximum number of partitions to retain, the TTL task starts from the smallest partition, p2000000, and checks each partition to determine if it is expired. This continues until the number of remaining partitions is 2 (p4000000 and p5000000). This process calculates the set of expired partitions to be cleared (p2000000 and p3000000). Then, before deleting the expired partitions, the TTL task first automatically adds a new partition (p6000000) and finally deletes the expired partitions.

View task progress

TTL table definition

You can query the INFORMATION_SCHEMA.TTL_INFO view to get the definitions of all TTL tables in the database and the status of their cleanup task schedules:

SELECT * 
FROM INFORMATION_SCHEMA.TTL_INFO 
WHERE TABLE_SCHEMA=<database_name> AND TABLE_NAME = 'my_ttl_tbl';

Sample result:

+--------------+------------+------------+------------+-----------------------------------------------------+-----------------+--------------+----------------------+--------------------+----------------------------+-----------------------------+
| TABLE_SCHEMA | TABLE_NAME | TTL_ENABLE | TTL_COL    | TTL_EXPR                                            | TTL_CRON        | ARCHIVE_TYPE | ARCHIVE_TABLE_SCHEMA | ARCHIVE_TABLE_NAME | ARCHIVE_TABLE_PRE_ALLOCATE | ARCHIVE_TABLE_POST_ALLOCATE |
+--------------+------------+------------+------------+-----------------------------------------------------+-----------------+--------------+----------------------+--------------------+----------------------------+-----------------------------+
| test_db      | my_ttl_tbl | ON         | date_field | `date_field` EXPIRE AFTER 1 MONTH TIMEZONE '+08:00' | 0 0 2 */1 * ? * | ROW          | test_db              | my_ttl_tbl_arc     | 3                          | 3                           |
+--------------+------------+------------+------------+-----------------------------------------------------+-----------------+--------------+----------------------+--------------------+----------------------------+-----------------------------+

Field descriptions:

Field	Description
TABLE_SCHEMA	The name of the logical database that contains the TTL table.
TABLE_NAME	The logical table name of the TTL table.
TTL_ENABLE	Indicates whether automatic cleanup is enabled for the TTL table.
TTL_COL	The time column defined for TTL.
TTL_EXPR	The data retention period defined for TTL.
TTL_CRON	The schedule interval definition for the TTL task. The format is a QUARTZ framework CRON expression.
ARCHIVE_TYPE	The archive type. Valid values: Row: Archive by row. PARTITION: Archive by partition. SUBPARTITION: Archive by subpartition.
ARCHIVE_TABLE_SCHEMA	The name of the logical database where the corresponding archive table resides. If the TTL table is not bound to an archive table, this value is NULL.
ARCHIVE_TABLE_NAME	The logical table name of the corresponding archive table. If the TTL table is not bound to an archive table, this value is NULL.
ARCHIVE_TABLE_PRE_ALLOCATE	The number of partitions pre-created for the TTL table based on the partitioning rule.
ARCHIVE_TABLE_POST_ALLOCATE	The number of partitions already created for the TTL table.

TTL task status

You can query the INFORMATION_SCHEMA.TTL_SCHEDULE view to view the real-time status of the cleanup task for a specific TTL table.

-- Manually trigger the cleanup task
ALTER TABLE `my_ttl_tbl` CLEANUP EXPIRED DATA;  

SELECT  * 
FROM INFORMATION_SCHEMA.TTL_SCHEDULE 
WHERE TABLE_SCHEMA = <database_name> AND TABLE_NAME = 'my_ttl_tbl';

Sample result:

+--------------+------------+--------------------------------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | METRIC_KEY                           | METRIC_VAL          |
+--------------+------------+--------------------------------------+---------------------+
| test_db      | my_ttl_tbl | SCHEDULE_STATUS                      | ENABLED             |
| test_db      | my_ttl_tbl | SCHEDULE_EXPR                        | 0 0 2 * * ? *       |
| test_db      | my_ttl_tbl | SCHEDULE_COMMENT                     | at 02:00            |
| test_db      | my_ttl_tbl | SCHEDULE_TIMEZONE                    | +08:00              |
| test_db      | my_ttl_tbl | SCHEDULE_LAST_FIRE_TIME              | 1970-01-01 08:00:00 |
| test_db      | my_ttl_tbl | SCHEDULE_NEXT_FIRE_TIME              | 2025-06-14 02:00:00 |
| test_db      | my_ttl_tbl | TTL_CURR_TTL_COL_MIN_VAL             | 2025-06-13 17:29:08 |
| test_db      | my_ttl_tbl | TTL_CURR_CLEANUP_BOUND               | 2025-04-01 00:00:00 |
| test_db      | my_ttl_tbl | TTL_CURR_CLEANUP_UPPER_BOUND         | 2025-04-01 00:00:00 |
| test_db      | my_ttl_tbl | TTL_CURR_NEW_DATETIME_VAL            | null                |
| test_db      | my_ttl_tbl | TTL_CURR_JOB_STAGE                   | Finished            |
| test_db      | my_ttl_tbl | TTL_CURR_JOB_BEGIN_TS                | 1749807929131       |
| test_db      | my_ttl_tbl | TTL_CURR_JOB_END_TS                  | 1749807931234       |
| test_db      | my_ttl_tbl | TTL_CURR_JOB_FROM_SCHEDULER          | false               |
| test_db      | my_ttl_tbl | TTL_CURR_JOB_STOP_BY_MAINTAIN_WINDOW | false               |
| test_db      | my_ttl_tbl | TTL_CURR_DN_ROWS_SPEED_LIMIT         | 1024                |
| test_db      | my_ttl_tbl | TTL_CURR_CLEANED_PHY_PART_COUNT      | 8                   |
| test_db      | my_ttl_tbl | TTL_CURR_TOTAL_PHY_PART_COUNT        | 8                   |
| test_db      | my_ttl_tbl | TTL_CURR_JOB_PERCENT                 | 100                 |
| test_db      | my_ttl_tbl | TTL_ACQUIRE_PERMITS_AVG_RT_NANO      | 11127               |
| test_db      | my_ttl_tbl | TTL_CURR_CLEANUP_TIMECOST            | 2014                |
| test_db      | my_ttl_tbl | TTL_CURR_CLEANUP_ROWS                | 0                   |
| test_db      | my_ttl_tbl | TTL_CURR_CLEANUP_ROWS_SPEED          | 0                   |
| test_db      | my_ttl_tbl | TTL_CURR_CLEANUP_DATA_LENGTH         | 0                   |
| test_db      | my_ttl_tbl | TTL_CURR_CLEANUP_SPEED               | 0                   |
| test_db      | my_ttl_tbl | TTL_CURR_SELECT_SQL_AVG_RT           | 8                   |
| test_db      | my_ttl_tbl | TTL_CURR_DELETE_SQL_AVG_RT           | 5                   |
| test_db      | my_ttl_tbl | TTL_CURR_OPTIMIZE_SQL_AVG_RT         | 0                   |
| test_db      | my_ttl_tbl | TTL_CURR_ADD_PART_AVG_RT             | 0                   |
| test_db      | my_ttl_tbl | TTL_CURR_DROP_PART_AVG_RT            | 0                   |
| test_db      | my_ttl_tbl | TTL_CURR_DATA_FREE_PERCENT_LIMIT     | 40                  |
| test_db      | my_ttl_tbl | TTL_CURR_TTL_TBL_DATA_FREE_PERCENT   | 0.00                |
| test_db      | my_ttl_tbl | TTL_CURR_OPTIMIZE_TTL_TBL_PROGRESS   | 0                   |
| test_db      | my_ttl_tbl | TTL_LAST_JOB_FROM_SCHEDULER          | false               |
| test_db      | my_ttl_tbl | TTL_LAST_TTL_COL_MIN_VAL             | 2025-06-13 17:29:08 |
| test_db      | my_ttl_tbl | TTL_LAST_CLEANUP_BOUND               | 2025-04-01 00:00:00 |
| test_db      | my_ttl_tbl | TTL_LAST_CLEANUP_UPPER_BOUND         | 2025-04-01 00:00:00 |
| test_db      | my_ttl_tbl | TTL_LAST_JOB_BEGIN_TS                | 1749807881814       |
| test_db      | my_ttl_tbl | TTL_LAST_JOB_END_TS                  | 1749807929080       |
| test_db      | my_ttl_tbl | TTL_LAST_SELECT_SQL_AVG_RT           | 15                  |
| test_db      | my_ttl_tbl | TTL_LAST_DELETE_SQL_AVG_RT           | 8                   |
| test_db      | my_ttl_tbl | TTL_LAST_OPTIMIZE_SQL_AVG_RT         | 0                   |
| test_db      | my_ttl_tbl | TTL_LAST_ADD_PARTS_SQL_AVG_RT        | 0                   |
| test_db      | my_ttl_tbl | TTL_LAST_CLEANUP_TIMECOST            | 2022                |
| test_db      | my_ttl_tbl | TTL_LAST_CLEANUP_ROWS                | 0                   |
| test_db      | my_ttl_tbl | TTL_LAST_CLEANUP_ROWS_SPEED          | 0                   |
| test_db      | my_ttl_tbl | TTL_LAST_CLEANUP_DATA_LENGTH         | 0                   |
| test_db      | my_ttl_tbl | TTL_LAST_CLEANUP_SPEED               | 0                   |
| test_db      | my_ttl_tbl | TTL_LAST_TTL_TBL_DATA_FREE_PERCENT   | 0.00                |
+--------------+------------+--------------------------------------+---------------------+

Field descriptions:

Metric	Description
SCHEDULE_STATUS	The status of the TTL scheduled task: DISABLED: Automatic scheduling is not enabled. ENABLED: Automatic scheduling is enabled.
SCHEDULE_EXPR	The schedule interval for the TTL task. You can adjust this value by modifying the TTL_JOB expression in the TTL definition.
SCHEDULE_COMMENT	A semantic interpretation of the TTL task schedule interval.
SCHEDULE_TIMEZONE	The time zone used for TTL task scheduling.
SCHEDULE_LAST_FIRE_TIME	The last time automatic scheduling was triggered. The initial value is '1970-01-01 08:00:00', indicating that no automatic scheduling has occurred yet.
SCHEDULE_NEXT_FIRE_TIME	The next time automatic scheduling will be triggered. The initial value is '1970-01-01 08:00:00', indicating that no automatic scheduling has occurred yet.
TTL_CURR_TTL_COL_MIN_VAL	The minimum value in the TTL time column for the current task.
TTL_CURR_JOB_STAGE	The current stage of the TTL task.
TTL_CURR_CLEANUP_BOUND	The cleanup scope of the current TTL task.
TTL_CURR_CLEANUP_UPPER_BOUND	The cleanup upper bound for the current TTL task. Data to be cleaned up must have a timestamp less than this value.
TTL_CURR_NEW_DATETIME_VAL	The latest current time.
TTL_CURR_DN_ROWS_SPEED_LIMIT	The cleanup rate limit for the current TTL scheduled task (the execution rate of `DELETE` statements on each storage node), measured in rows/s.
TTL_CURR_CLEANUP_ROWS	The total number of rows cleaned up by the current TTL task so far.
TTL_CURR_CLEANUP_ROWS_SPEED	The real-time cleanup speed of the current TTL task, measured in rows per second.
TTL_CURR_CLEANUP_DATA_LENGTH	The total size of data cleaned up by the current TTL task so far (estimated).
TTL_CURR_CLEANUP_SPEED	The real-time cleanup speed of the current TTL task, measured in bytes per second (estimated).
TTL_CURR_SELECT_SQL_AVG_RT	The average response time for `SELECT` statements executed by the current TTL task, measured in milliseconds.
TTL_CURR_DELETE_SQL_AVG_RT	The average response time for `DELETE` statements executed by the current TTL task, measured in milliseconds.
TTL_CURR_JOB_PERCENT	The completion percentage of the current TTL task.
TTL_CURR_CLEANED_PHY_PART_COUNT	The number of physical partitions that the current TTL task has finished cleaning up.
TTL_CURR_TOTAL_PHY_PART_COUNT	The total number of physical partitions that the current TTL task needs to clean up.

Manage cleanup tasks

To minimize the impact on your business workloads, you can manage when and how fast these tasks run.

Adjust the maintenance window

The default maintenance window for PolarDB-X 2.0 background tasks is from 02:00 to 05:00 daily. You can use the following statements to adjust the maintenance window to be from 01:00 to 06:00 daily:

SET GLOBAL MAINTENANCE_TIME_START = '01:00';
SET GLOBAL MAINTENANCE_TIME_END = '06:00';

The TTL_JOB definition specifies the submission time and frequency of a TTL task, whereas the maintenance window defined by MAINTENANCE_TIME_START and MAINTENANCE_TIME_END specifies the time range when the TTL task is allowed to run. For example, assume TTL_JOB = CRON '0 0 0 */7 * ? *' TIMEZONE '+08:00', which means the TTL task is submitted every 7 days at 00:00. Also assume the maintenance window is 02:00~05:00 (UTC+8). Although the TTL task is submitted at 00:00, it will be queued and will not start executing until 02:00. If the task is not complete by 05:00, it will be automatically paused and will resume during the next maintenance window.

Adjust the row-based cleanup speed limit

During execution, a scheduled TTL cleanup task limits the cleanup speed on each DN by default. This limit prevents the cleanup task from consuming excessive CPU and IOPS resources on the DNs, which could affect online business. The default cleanup speed limit for each DN is 1,000 rows/s. This means the total cleanup speed for all TTL tables on a single DN is at most 1,000 rows per second. The more DNs you have, the faster the cleanup. The total speed is N * 1,000 rows/s, where N is the number of DNs.

Use the following SQL statement to adjust the DN cleanup speed to 10,000 rows per second:

SET GLOBAL TTL_ENABLE_CLEANUP_ROWS_SPEED_LIMIT=10000;

Note

If a DN's CPU or IOPS is already saturated, increasing the cleanup speed limit with the preceding SQL statement may not improve the cleanup speed.

Adjust the row-based cleanup time range

By default, the time range of the cleanup task for a TTL table is primarily controlled by the TTL_CLEANUP_BOUND_INTERVAL_COUNT parameter.

Set TTL_CLEANUP_BOUND_INTERVAL_COUNT to 6, as shown in the following example:

SET GLOBAL TTL_CLEANUP_BOUND_INTERVAL_COUNT = 6;

Note

The default value of TTL_CLEANUP_BOUND_INTERVAL_COUNT is 3, which represents three time units. For example:

The time unit for TTL is specified in days: time EXPIRE AFTER num DAY TIMEZONE '+08:00'.
The default cleanup scope is a 3-day interval: [MinValue, MinValue + 3 Day).
The time unit for TTL is specified in months: time EXPIRE AFTER num MONTH TIMEZONE '+08:00'.
The default cleanup range is a 3-month interval: [MinValue, MinValue + 3 Month).
The time unit for TTL is specified in years: `time` EXPIRE AFTER num YEAR TIMEZONE '+08:00'.
The default cleanup range is a 3-year interval: [MinValue, MinValue + 3 Year).

Pause a cleanup task

Run SHOW FULL DDL to view the JOB_ID of all cleanup tasks:

SHOW FULL DDL\G

Sample result:

*************************** 1. row ***************************
                   JOB_ID: 1771694362409848832
            OBJECT_SCHEMA: ttldb
              OBJECT_NAME: my_ttl_tbl
                   ENGINE: DAG
                 DDL_TYPE: ALTER_TABLE
                    STATE: RUNNING
  TOTAL_BACKFILL_PROGRESS: --
 CURRENT_PHY_DDL_PROGRESS: 0%
                 PROGRESS: 33%
     FASTCHECKER_TASK_NUM: 0
FASTCHECKER_TASK_FINISHED: 0
               START_TIME: 2024-09-14 15:55:12.991
                 END_TIME: 2024-09-14 15:55:16.959
         ELAPSED_TIME(MS): 3968
              PHY_PROCESS:
               CANCELABLE: true
            PARENT_JOB_ID: --
            RESPONSE_NODE: 10.57.104.154:3067
           EXECUTION_NODE: 10.57.104.154:3067
                 TRACE_ID: 189652a2bc805000
                 DDL_STMT: alter table my_ttl_tbl cleanup expired data
                   REMARK: --
       LEGACY_ENGINE_INFO: --

Use the JOB_ID from the preceding step to pause the cleanup task:
```
PAUSE DDL 1771694362409848832;
```

Resume a cleanup task

Run the following statement to resume a paused cleanup task:

CONTINUE DDL 1771694362409848832;