pingcap / pingcap/tidb

TTL: allow custom start time

Open
#62,018 2 comments 0 reactions 0 assignees View on GitHub
type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Feature Request

**Is your feature request related to a problem? Please describe:**
For some large tables, I would like to run nightly job, once per day, during off peak hours. I am able to set `TTL_JOB_INTERVAL=24h` but have not way to configure when the TTL job should start.

**Describe the feature you'd like:**
The ability to specify approximate time when TTL job should start, for example TTL_JOB_INTERVAL=24h `TTL_JOB_START='02h00'`

**Describe alternatives you've considered:**
I considered directly manipulating internal TiDB tables, but this is not supported and I am not 100% sure how the whole thing works. There might be caching or other internal mechanisms that I'm not aware of. I don't want to risk breaking TTL or causing inconsistencies.
```
TiDB root@127.0.0.1:mysql> SELECT NOW(); ALTER TABLE test.modal_poll_entry TTL=`created` + INTERVAL 30 DAY TTL_JOB_INTERVAL='10m';
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
+---------------------+
| NOW() |
+---------------------+
| 2025-06-25 18:22:18 |
+---------------------+
1 row in set

Query OK, 0 rows affected

TiDB root@127.0.0.1:mysql> select * from tidb_timers\G
***************************[ 1. row ]***************************
ID | 1
NAMESPACE | default
TIMER_KEY | /tidb/ttl/physical_table/110/110
TIMER_DATA | {"table_id":110,"physical_id":110}
TIMEZONE |
SCHED_POLICY_TYPE | INTERVAL
SCHED_POLICY_EXPR | 10m
HOOK_CLASS | tidb.ttl
WATERMARK | 2025-06-25 17:53:41
ENABLE | 1
TIMER_EXT | {"tags": ["db=test", "table=modal_poll_entry"]}
EVENT_STATUS | IDLE
EVENT_ID |
EVENT_DATA |
EVENT_START |
SUMMARY_DATA | {"last_job_request_id":"a6e8ea23dd3b4a5999b461e3819a07f0","last_job_summary":{"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":1,"scheduled_scan_task":1,"finished_scan_task":1}}
CREATE_TIME | 2025-06-25 17:53:40
UPDATE_TIME | 2025-06-25 18:22:19
VERSION | 7
1 row in set
TiDB root@127.0.0.1:mysql> UPDATE mysql.tidb_timers SET UPDATE_TIME='2025-06-25 18:23:00', VERSION=8 WHERE ID=1;
Query OK, 1 row affected
TiDB root@127.0.0.1:mysql> select * from tidb_timers\G
***************************[ 1. row ]***************************
ID | 1
NAMESPACE | default
TIMER_KEY | /tidb/ttl/physical_table/110/110
TIMER_DATA | {"table_id":110,"physical_id":110}
TIMEZONE |
SCHED_POLICY_TYPE | INTERVAL
SCHED_POLICY_EXPR | 10m
HOOK_CLASS | tidb.ttl
WATERMARK | 2025-06-25 18:22:20
ENABLE | 1
TIMER_EXT | {"tags": ["db=test", "table=modal_poll_entry"]}
EVENT_STATUS | IDLE
EVENT_ID |
EVENT_DATA |
EVENT_START |
SUMMARY_DATA | {"last_job_request_id":"55ca210912c8472b9b726d37c309b600","last_job_summary":{"total_rows":0,"success_rows":0,"error_rows":0,"total_scan_task":1,"scheduled_scan_task":1,"finished_scan_task":1}}
CREATE_TIME | 2025-06-25 17:53:40
UPDATE_TIME | 2025-06-25 18:23:00
VERSION | 8
1 row in set
```

**Teachability, Documentation, Adoption, Migration Strategy:**
This would allow tables that have heavy TTL jobs to run during the night, when workload is low.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.