PolarDB-X TTL表过期数据清理_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

PolarDB-X 2.0支持管理TTL表过期数据清理任务，您可以通过查看清理任务进度、状态，并根据实际情况调整清理任务的速度、清理范围等，以达到对清理任务和在线业务的平衡。

前提条件

实例版本要求如下：
- 引擎版本为MySQL 5.7时，实例版本必须是polardb-2.4.0_5.4.19-20240927_xcluster5.4.19-20240920及以上。
- 引擎版本为MySQL 8.0时，实例版本必须是polardb-2.4.0_5.4.19-20240927_xcluster8.4.19-20240924及以上。
说明
- 实例版本的规则说明，请参见版本说明。
- 实例版本的查看方式，请参见查看实例版本。
TTL定义仅支持AUTO模式数据库的分区表（不包括使用Local Partition的分区表）。

启动清理任务

启动TTL表过期数据清理任务，有以下两种方式：

定时自动启动：通过设置TTL_JOB，系统按时自动启动过期数据清理任务。
手动启动：手动执行ALTER TABLE CLEANUP EXPIRED DATA语句，即可启动过期数据清理任务。

重要

系统最多支持2张TTL表同时执行数据清理任务，其余清理任务排队等待执行。

使用说明

定时自动启动
示例：
```
ALTER TABLE `my_ttl_tbl` MODIFY TTL SET TTL_ENABLE = 'ON';
```
说明
为TTL表my_ttl_tbl打开自动清理过期数据任务的执行开关（该开关默认为关闭状态）。
```
ALTER TABLE `my_ttl_tbl` MODIFY TTL SET TTL_JOB = CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00';
```
说明
- 调整TTL表my_ttl_tbl的自动清理过期数据任务执行计划（执行时间点和频率）。
- TTL_JOB定义的调度时间是预期时间值，实际执行时间需要依赖它的前置任务是否完成（如果有多张TTL表正在执行清理，则需要等待其他任务完成）。
手动启动
示例：
```
/*+TDDL:cmd_extra(ENABLE_ASYNC_DDL=true, PURE_ASYNC_DDL_MODE=true)*/
ALTER TABLE `my_ttl_tbl` CLEANUP EXPIRED DATA;
```
说明
- /*+TDDL:cmd_extra(ENABLE_ASYNC_DDL=true, PURE_ASYNC_DDL_MODE=true)*/用于设置该DDL后台异步执行，无须在当前会话同步等待返回结果。
- ALTER TABLE CLEANUP EXPIRED DATA只支持TTL表。

计算清理范围

TTL表采用的是“由远及近分批清理”的清理算法，从最早的数据开始清理，每次只清理固定时间范围的数据。

如下代码修改表my_ttl_tbl的TTL定义：

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

说明

该表的数据存活时间为1个月。
每次清理任务清理的时间范围是数据存活的3倍，本例中为3个月。

假设当前时间为2023-10-01，在线表只需要保存最近1个月的数据，清理流程示例如下图所示：

说明

Day 1：
TTL定义的时间列其最小时间值为2022-10-05（MinValue），再根据清理的时间范围（CleanupDataInterval，本例中为3个月。更多信息，请参见调整清理时间范围。）得出本次清理的范围为2022-10-05≤Time＜2023-01-01。以此类推其他清理范围分别是2023-01-01≤Time＜2023-04-01、2023-04-01≤Time＜2023-07-01、2023-07-01≤Time＜2023-09-01。
Day 2：
同Day 1，清理时间列满足2023-01-01≤Time＜2023-04-01条件的数据，即2023-04-01为本次清理任务的上边界（CleanupUpperBound）。
Day 3：
同Day 1，清理时间列满足2023-04-01≤Time＜2023-07-01条件的数据，即2023-07-01为本次清理任务的上边界（CleanupUpperBound）。
Day 4：
与之前略有不同，清理时间列满足2023-07-01≤Time＜2023-09-01条件的数据，时间范围为2个月。因为上图中假设的当前时间为2023-10-01（Now），且TTL表被设置为保留最近1个月（ExpiredDataInterval，TTL表的数据存活时间）的数据，所以本次只能清理2023-09-01之前的数据，即2023-09-01为本次清理任务的上边界（CleanupUpperBound）。

综上所述，可以得出每次清理任务的上边界（CleanupUpperBound）的计算公式为CleanupUpperBound=Min(MinValue+CleanupDataInterval,Now−ExpiredDataInterval)。

查看清理任务进度

查看TTL表的定义

您可以通过如下代码查询视图INFORMATION_SCHEMA.TTL_INFO，以获取数据库中所有TTL表的定义及其清理任务的调度开关状态：

SELECT  * 
FROM INFORMATION_SCHEMA.TTL_INFO 
WHERE TABLE_SCHEMA='ttldb' AND TABLE_NAME = 'my_ttl_tbl';

*************************** 1. row ***************************
               TABLE_SCHEMA: ttldb
                 TABLE_NAME: my_ttl_tbl
                 TTL_ENABLE: OFF
                    TTL_COL: date_field
                   TTL_EXPR: `date_field` EXPIRE AFTER 2 MONTH TIMEZONE '+08:00'
                   TTL_CRON: 0 0 1 * * ? *
               ARCHIVE_TYPE: 
       ARCHIVE_TABLE_SCHEMA: NULL
         ARCHIVE_TABLE_NAME: NULL
 ARCHIVE_TABLE_PRE_ALLOCATE: 12
ARCHIVE_TABLE_POST_ALLOCATE: 64
1 row in set (0.07 sec)

字段说明：

字段名	说明
TABLE_SCHEMA	TTL表的逻辑库名。
TABLE_NAME	TTL表的逻辑表名。
TTL_ENABLE	TTL表是否打开自动清理。
TTL_COL	TTL定义的时间列。
TTL_EXPR	TTL定义的数据存活时间。
TTL_CRON	TTL定时任务的调度间隔的定义。格式为QUARTZ框架的CRON表达式。
ARCHIVE_TYPE	TTL定义的归档类型。默认值Columnar，表示按行归档。
ARCHIVE_TABLE_SCHEMA	TTL表所对应归档表所在逻辑库名。若该TTL表没有绑定归档表，则该值为NULL。
ARCHIVE_TABLE_NAME	TTL表所对应归档表所在逻辑表名。若该TTL表没有绑定归档表，则该值为NULL。
ARCHIVE_TABLE_PRE_ALLOCATE	TTL表按照分区规则提前预建的分区数。
ARCHIVE_TABLE_POST_ALLOCATE	TTL表已创建的分区数。

查看TTL任务状态

您可以通过查询INFORMATION_SCHEMA.TTL_SCHEDULE视图，来获取指定TTL表的清理任务的实时运行信息。

如下代码查看TTL表my_ttl_tbl正在运行的清理任务：

// 手动调起清理任务
ALTER TABLE `my_ttl_tbl` CLEANUP EXPIRED DATA;  
SELECT  * 
FROM INFORMATION_SCHEMA.TTL_SCHEDULE 
WHERE TABLE_SCHEMA='ttldb' AND TABLE_NAME = 'my_ttl_tbl';

+--------------+------------+--------------------------------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | METRIC_KEY                           | METRIC_VAL          |
+--------------+------------+--------------------------------------+---------------------+
| ttldb        | my_ttl_tbl | SCHEDULE_STATUS                      | DISABLED            |
| ttldb        | my_ttl_tbl | SCHEDULE_EXPR                        | 0 0 1 * * ? *       |
| ttldb        | my_ttl_tbl | SCHEDULE_COMMENT                     | at 01:00            |
| ttldb        | my_ttl_tbl | SCHEDULE_TIMEZONE                    | +08:00              |
| ttldb        | my_ttl_tbl | SCHEDULE_LAST_FIRE_TIME              | 1970-01-01 08:00:00 |
| ttldb        | my_ttl_tbl | SCHEDULE_NEXT_FIRE_TIME              | 1970-01-01 08:00:00 |
| ttldb        | my_ttl_tbl | TTL_CURR_TTL_COL_MIN_VAL             | null                |
| ttldb        | my_ttl_tbl | TTL_CURR_CLEANUP_BOUND               | 2024-05-01 00:00:00 |
| ttldb        | my_ttl_tbl | TTL_CURR_CLEANUP_UPPER_BOUND         | 2024-05-01 00:00:00 |
| ttldb        | my_ttl_tbl | TTL_CURR_NEW_DATETIME_VAL            | 2024-07-18 18:22:54 |
| ttldb        | my_ttl_tbl | TTL_CURR_JOB_STAGE                   | Finished            |
| ttldb        | my_ttl_tbl | TTL_CURR_JOB_BEGIN_TS                | 1721298173321       |
| ttldb        | my_ttl_tbl | TTL_CURR_JOB_END_TS                  | 0                   |
| ttldb        | my_ttl_tbl | TTL_CURR_JOB_FROM_SCHEDULER          | false               |
| ttldb        | my_ttl_tbl | TTL_CURR_JOB_STOP_BY_MAINTAIN_WINDOW | false               |
| ttldb        | my_ttl_tbl | TTL_CURR_DN_ROWS_SPEED_LIMIT         | 0                   |
| ttldb        | my_ttl_tbl | TTL_CURR_CLEANED_PHY_PART_COUNT      | 8                   |
| ttldb        | my_ttl_tbl | TTL_CURR_TOTAL_PHY_PART_COUNT        | 8                   |
| ttldb        | my_ttl_tbl | TTL_CURR_JOB_PERCENT                 | 100                 |
| ttldb        | my_ttl_tbl | TTL_ACQUIRE_PERMITS_AVG_RT_NANO      | 0                   |
| ttldb        | my_ttl_tbl | TTL_CURR_CLEANUP_TIMECOST            | 6044                |
| ttldb        | my_ttl_tbl | TTL_CURR_CLEANUP_ROWS                | 0                   |
| ttldb        | my_ttl_tbl | TTL_CURR_CLEANUP_ROWS_SPEED          | 0                   |
| ttldb        | my_ttl_tbl | TTL_CURR_CLEANUP_DATA_LENGTH         | 0                   |
| ttldb        | my_ttl_tbl | TTL_CURR_CLEANUP_SPEED               | 0                   |
| ttldb        | my_ttl_tbl | TTL_CURR_SELECT_SQL_AVG_RT           | 844                 |
| ttldb        | my_ttl_tbl | TTL_CURR_DELETE_SQL_AVG_RT           | 2857                |
| ttldb        | my_ttl_tbl | TTL_CURR_OPTIMIZE_SQL_AVG_RT         | 0                   |
| ttldb        | my_ttl_tbl | TTL_CURR_ADD_PART_AVG_RT             | 0                   |
| ttldb        | my_ttl_tbl | TTL_CURR_DATA_FREE_PERCENT_LIMIT     | -1                  |
| ttldb        | my_ttl_tbl | TTL_CURR_TTL_TBL_DATA_FREE_PERCENT   | 0.00                |
| ttldb        | my_ttl_tbl | TTL_CURR_OPTIMIZE_TTL_TBL_PROGRESS   | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_JOB_FROM_SCHEDULER          | false               |
| ttldb        | my_ttl_tbl | TTL_LAST_TTL_COL_MIN_VAL             |                     |
| ttldb        | my_ttl_tbl | TTL_LAST_CLEANUP_BOUND               |                     |
| ttldb        | my_ttl_tbl | TTL_LAST_CLEANUP_UPPER_BOUND         |                     |
| ttldb        | my_ttl_tbl | TTL_LAST_JOB_BEGIN_TS                | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_JOB_END_TS                  | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_SELECT_SQL_AVG_RT           | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_DELETE_SQL_AVG_RT           | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_OPTIMIZE_SQL_AVG_RT         | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_ADD_PARTS_SQL_AVG_RT        | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_CLEANUP_TIMECOST            | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_CLEANUP_ROWS                | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_CLEANUP_ROWS_SPEED          | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_CLEANUP_DATA_LENGTH         | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_CLEANUP_SPEED               | 0                   |
| ttldb        | my_ttl_tbl | TTL_LAST_TTL_TBL_DATA_FREE_PERCENT   | 0                   |
+--------------+------------+--------------------------------------+---------------------+
48 rows in set (0.03 sec)

字段说明：

部分指标名	含义
SCHEDULE_STATUS	定时任务的调度开关： DISABLED：没有开启自动调度。 ENABLED：已经开启自动调度。
SCHEDULE_EXPR	定时清理任务调度时间间隔，该值可调整。具体操作，请参见调整定时清理任务的执行计划。
SCHEDULE_COMMENT	SCHEDULE_EXPR字段的语义解析。
SCHEDULE_TIMEZONE	定时任务调度的时区。
SCHEDULE_LAST_FIRE_TIME	上一次触发自动调度的时间值（初始值为 '1970-01-01 08:00:00'，表示没有触发自动调度）。
SCHEDULE_NEXT_FIRE_TIME	下一次触发自动调度的时间值（初始值为 '1970-01-01 08:00:00'，表示没有触发自动调度）。
TTL_CURR_TTL_COL_MIN_VAL	当前清理任务的TTL定义的时间列的最小值。
TTL_CURR_JOB_STAGE	当前清理任务所处的阶段。
TTL_CURR_CLEANUP_BOUND	当前清理任务正在清理的时间列的值。
TTL_CURR_CLEANUP_UPPER_BOUND	当前清理任务的清理上界，即本次清理历史数据的范围小于该值。
TTL_CURR_NEW_DATETIME_VAL	当前时间。
TTL_CURR_DN_ROWS_SPEED_LIMIT	当前清理任务的清理限速（每个存储节点执行`DELETE`语句的速度），单位是每秒清理的行数。
TTL_CURR_CLEANUP_ROWS	当前清理任务已经清理的总行数。
TTL_CURR_CLEANUP_ROWS_SPEED	当前清理任务的实时清理速度，单位是每秒清理的行数。
TTL_CURR_CLEANUP_DATA_LENGTH	当前清理任务已经完成清理的总字节数（估算值）。
TTL_CURR_CLEANUP_SPEED	当前清理任务的实时清理速度，单位是每秒清理的字节数（估算值）。
TTL_CURR_SELECT_SQL_AVG_RT	当前清理任务执行`SELECT`语句的平均响应时间，单位：毫秒。
TTL_CURR_DELETE_SQL_AVG_RT	当前清理任务执行`DELETE`语句的平均响应时间，单位：毫秒。
TTL_CURR_JOB_PERCENT	当前清理任务的完成百分比。
TTL_CURR_CLEANED_PHY_PART_COUNT	当前清理任务已完成清理的物理分区数。
TTL_CURR_TOTAL_PHY_PART_COUNT	当前清理任务总共需要完成的物理分区数。

管理清理任务

您可以通过管理清理任务，使其在合适时间以合适的速度进行数据清理，使清理任务对业务的影响达到最低。

调整可运维时间窗口

PolarDB-X 2.0后台任务默认的可运维窗口是每天02:00 ~ 05:00，您可以通过如下代码将可运维的时间窗口调整至每天01:00 ~ 06:00：

set global MAINTENANCE_TIME_START = '01:00';
set global MAINTENANCE_TIME_END = '06:00';

调整清理速度限制

单个存储节点的清理速度限制默认为1000行每秒，即该节点上所有清理任务速度的总和不超过1000行每秒，实例中存储节点数越多总清理速度越快。

您可以通过如下代码调整单个存储节点的清理速度限制为1万行每秒：

set global TTL_ENABLE_CLEANUP_ROWS_SPEED_LIMIT=10000

重要

调大清理速度限制会占用更多的CPU和IOPS资源，请您根据存储节点的实际性能，设置合适的清理速度限制，避免清理任务过度占用存储节点的CPU和IOPS资源，从而影响业务。
如果该存储节点的CPU和IOPS资源已经处于满负荷状态时，即使通过上述代码提高清理速度限制也不一定能提升清理速度。

调整清理时间范围

默认情况下，TTL表的一次清理任务的时间范围主要是由 TTL_CLEANUP_BOUND_INTERVAL_COUNT 这个参数所控制：

您可以通过如下代码，调整清理数据的时间范围：

set global TTL_CLEANUP_BOUND_INTERVAL_COUNT = 6;

说明

TTL_CLEANUP_BOUND_INTERVAL_COUNT默认值为3，即清理数据的时间范围是TTL定义中存活时间单位的3倍。执行上述SQL将其调整为6，即清理数据的时间范围是TTL定义中存活时间单位的6倍，示例如下：

TTL定义的时间单位是天：`time` EXPIRE AFTER NUM DAY TIMEZONE '+08:00'，即默认的清理范围为6天（MinValue≤Time＜(MinValue+6 Day)）。
TTL定义的时间单位是月：`time` EXPIRE AFTER NUM MONTH TIMEZONE '+08:00'，即默认的清理范围为6月（MinValue≤Time＜(MinValue+6 Month)）。
TTL定义的时间单位是年：`time` EXPIRE AFTER NUM YEAR TIMEZONE '+08:00'，即默认的清理范围为6年（MinValue≤Time＜(MinValue+6 Year)）。

暂停清理任务

通过SHOW FULL DDL查看所有清理任务的JOB_ID：

show full ddl\G;

*************************** 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: --
1 row in set (0.01 sec)

使用第一步得到的JOB_ID，执行如下代码，即可暂停对应的清理任务：
```
pause ddl 1771694362409848832;
```

说明

清理任务的DDL JOB可以实时暂停或回滚，但清理任务的回滚仅仅会回滚清理任务本身，并不会回滚已经被清理的数据。更多信息，请参见DDL管理语句。

重启清理任务

您可以通过执行如下代码来重启被暂停的清理任务：

CONTINUE DDL 1771694362409848832;