docs/design/2022-11-17-ttl-table.md
The rows in a TTL table will be deleted automatically when they are expired. It is useful for some scenes, for example, delete the expired verification codes. A TTL table will have a column with the type DATE/DATETIME/TIMESTAMP which will be compared with the current time, if the interval between them exceeds some threshold, the corresponding row will be deleted.
The following example shows how to create a TTL table. The column create_at is used to specify the creation time of the rows which will be deleted 3 months after that.
CREATE TABLE t1 (
id int PRIMARY KEY,
created_at TIMESTAMP
) TTL = `created_at` + INTERVAL 3 MONTH;
We can use another TTL_ENABLE option to disable/enable the TTL job for the table. For example:
CREATE TABLE t1 (
id int PRIMARY KEY,
created_at TIMESTAMP
) TTL = `created_at` + INTERVAL 3 MONTH TTL_ENABLE = 'OFF';
The above table will not delete expired rows automatically because TTL_ENABLE is set to OFF. When the TTL_ENABLE is omitted, it uses value ON by default.
To make it compatible with mysql, TTL options also support the comment format. For example:
CREATE TABLE t1 (
id int PRIMARY KEY,
created_at TIMESTAMP
) /*T![ttl] TTL = `created_at` + INTERVAL 3 MONTH TTL_ENABLE = 'OFF'*/;
We can alter an exist table with TTL options, for example:
ALTER TABLE t1 TTL = `created_at` + INTERVAL 3 MONTH;
TTL job is scheduled every 1 hour by default, if we want to custom the schedule interval, we can do it like this:
ALTER TABLE t1 TTL_JOB_INTERVAL='1d';
This alters the TTL job's schedule interval of table t1 to 1 day.
We should allow to update the existed TTL options. When it is updated, the running background job for this table should stop or restart according to the newest settings.
Similar with mysql syntax "ALTER TABLE t REMOVE PARTITIONING", if we want to remove a table's TTL options, we can just do:
ALTER TABLE t1 REMOVE TTL;
TTL table supports a generated column as the time column, but currently, there maybe a performance degradation if you use it. The main reason is that generated column does not support pushing down its expression to TiKV side and the scan phase will have more performance cost we'll discuss later. Another reason is that most date functions are not push down supported too. So, if we want to solve this problem, we must:
We use a SQL-layer approach to delete expired rows. The "SQL-layer approach" means that the background jobs are using SQL protol to scan and delete rows. It is simple to implement and has a good compatibility with the tools such as BR and TiCDC.
In the current design, we'll schedule a job for each TTL table when needed. We will try to schedule the jobs from different tables to different TiDB nodes to reduce the performance affect. For one physical table, the job will be running in one TiDB node and the partition table will be recognized as several physical tables, so there can be multiple jobs running in different TiDB nodes at one time.
The TTL table status will be record in a new system table mysql.tidb_ttl_table_status with the definition:
CREATE TABLE `tidb_ttl_table_status` (
`table_id` bigint(64) PRIMARY KEY,
`parent_table_id` bigint(64),
`table_statistics` TEXT DEFAULT NULL,
`last_job_id` varchar(64) DEFAULT NULL,
`last_job_start_time` timestamp NULL DEFAULT NULL,
`last_job_finish_time` timestamp NULL DEFAULT NULL,
`last_job_ttl_expire` timestamp NULL DEFAULT NULL,
`last_job_summary` text DEFAULT NULL,
`current_job_id` varchar(64) DEFAULT NULL,
`current_job_owner_id` varchar(64) DEFAULT NULL,
`current_job_owner_addr` varchar(256) DEFAULT NULL,
`current_job_owner_hb_time` timestamp,
`current_job_start_time` timestamp NULL DEFAULT NULL,
`current_job_ttl_expire` timestamp NULL DEFAULT NULL,
`current_job_state` text DEFAULT NULL,
`current_job_status` varchar(64) DEFAULT NULL,
`current_job_status_update_time` timestamp NULL DEFAULT NULL
);
It stores some information for each TTL table. The fields prefix last_job_ present the information of the last job which is successfully executed, and the fields with prefix current_job_ present the current job which has not been finished yet.
The explanation of the fields:
table_id: The id of the TTL table. If the table is a partitioned table, it stands for the physical table id of each partition.parent_table_id: If the current row is a for table partition, it is the table id of its parent table. Otherwise, it equals to table_id.table_statistics: some statistics of the table.last_job_id: The id of the last success job.last_job_start_time: The start time of the last job.last_job_finish_time: The finish time of the last job.last_job_ttl_expire: The expired time used by the last job for TTL works.last_job_summary: The summary info for the last job.current_job_id: The id of the current job that is not finished. It not only includes the running job, but also includes the job that is failed or cancelled by user.current_job_owner_id: The id of the owner (a TiDB node) that runs the job.current_job_owner_addr: The network address of the owner that runs the job.current_job_owner_hb_time: The owner of the job updates this field with the current timestamp periodically. If it is not updated for a long time, it means the previous owner is offline and this job should be taken over by other node later.current_job_start_time: The start time of the current job.current_job_ttl_expire: The expired time used by the current job for TTL works.current_job_state: Some inner state for the current job. It can be used for the job's fail over.current_job_status: A enum with one of values: waiting, running, cancelling, cancelled, errorcurrent_job_status_update_time: The update time of the current statusTTL job for each table runs periodically according to the configuration of the system variable tidb_ttl_job_run_interval . For example, if we configure set @@global.tidb_ttl_job_run_interval='1h', the cluster will schedule TTL jobs for each table every one hour to delete expired rows.
If you want to cancel a running job, you can execute a statement like this:
ADMIN CANCEL TTL JOB 123456789
In the above example, the status of the TTL job with ID 123456789 will first become cancelling and then finally be updated to cancelled.
TiDB schedules TTL jobs to delete expired rows. One job is related to a TTL table and runs in one TiDB node. One TiDB node servers multiple workers where tasks from the TTL jobs are running.
A running job contains two kinds of tasks: scan tasks and delete tasks. Scan tasks are used to filter out expired rows from the table and then send them to delete tasks which will do delete operations in batch. When all expired rows are deleted, the job will be finished. Let's talk about scan and delete tasks in detail.
When a job starts to run, it first splits the table to N (N >= 1) ranges according to their primary key. Each range will be assigned to a scan task and each scan task performs a range scan for the specified range. The pseudocode below shows how it works:
func doScanTask(tbl, range, expire, ch) {
var lastRow
for {
selectSQL := buildSelect(tbl, range lastRow, expire, LIMIT)
rows := execute(selectSQL)
ch <- deleteTask{tbl, expire, rows}
if len(rows) < LIMIT {
break
}
lastRow := rows[len(rows)-1]
}
}
As we see above, it builds some select queries in a loop. The first query is built like this:
SELECT LOW_PRIORITY id FROM t1
WHERE create_time < '2022-01-01 00:00:00' AND id >= 12345 AND id < 45678
ORDER BY id ASC
LIMIT 500;
In above example, the expired time is '2022-01-01 00:00:00' which is computed when the job started and the key range for the current scan task is [12345, 45678). We also limit the max count of return rows as 500 which you can set the system variable tidb_ttl_scan_batch_size to change it.
For most cases, we cannot get all expired rows in one query. So if the return row count equals to the limit we set, that means there are still some rows not read yet. Suppose the latest id we just queried is 23456, we should schedule the next query like this:
SELECT LOW_PRIORITY id FROM t1
WHERE create_time < '2022-01-01 00:00:00' AND id >= 23456 AND id < 45678
ORDER BY id ASC
LIMIT 500;
The only difference with the first query is that the second one uses 23456 as the read start to skip the rows just read. This procedure will continue until all expired records are read.
The expired rows will be wrapped as deleteTask and then be sent to the delete workers simultaneously. Before we talk about delete works, there are still some things we should mention:
tidb_ttl_job_run_interval to a longer value to reduce the resource cost in one day._tidb_rowid instead as the row id.There are several delete workers running in one TiDB node, and they consume tasks sent from the scan phase to delete expired rows. The following pseudocode shows how it works:
func doDelTask(ch) {
for _, task := range ch {
batches := splitRowsToDeleteBatches(task.rows)
for _, batch := range batches {
deleteBatch(task.tbl, task.batch, task.expire)
}
}
}
A delete worker receives tasks from a channel (a chan object in goland) and then splits the rows in it to several batches according to the system variable tidb_ttl_delete_batch_size. After that, each batch expired rows will be deleted by a multi row DELETE query. For example:
DELETE LOW_PRIORITY FROM t
WHERE id in (1, 2, 3, ...) AND create_time < '2022-01-01 00:00:00';
Notice that we are still using the condition create_time < '2022-01-01 00:00:00' to avoid deleting some "not expired" rows by mistake. For example, a row is expired when scanning, but updated to a not expired value before deleting it.
If there is no secondary index in a TTL table, we can assume that most of the delete operations can do a commit with 1PC. That is because the incoming rows are in the same region in most cases. So if you have a big table, keep no secondary indexes in this table may achieve a better performance for TTL jobs.
As we mentioned above, a TTL job will be split to several scan tasks. In order to maximize the use of cluster resources, we can distribute these tasks to all TiDB nodes. Every time a new job is created, some rows will be inserted to a table named mysql.tidb_ttl_task. Each row in this table stands for a scan task. Every TiDB node will scan the table periodically and once a new task is discovered, the TiDB node will set the owner of the task to itself and then execute it.
Currently, we support three field types as the time column for TTL: Date, DateTime and TimeStamp . However, for Date and DateTime, they are not an absolute time, and we need another "time zone" information to determine what their accurate time point is. We have two options to select the time zone:
Use the system time zone which can be fetched from global variable system_time_zone and is determined when the cluster bootstraps. Because the system_time_zone will never changes, the row will be deleted at a certain time. However, it will have some problem when the user sets the cluster time zone time_zone to a different value other than SYSTEM. For example, if the system_time_zone is +08:00 and time_zone is set to UTC, the record will be seen as expired immediately when the tidb_ttl_job_run_interval is less than 8 hours.
Use the cluster time zone time_zone. It is fine when cluster time zone time_zone does not change. But if user changes the time_zone settings, the TTL job should be aware of it in time to forbid deleting some unexpected rows. In this case, whether one row is expired or not can not be determined without knowing the time_zone at that time.
Some new system variables will be introduced:
tidb_ttl_job_enable
OFF, the cluster will stop to schedule TTL jobs and the running jobs will be cancelled.tidb_ttl_job_schedule_window_start_time
tidb_ttl_job_schedule_window_end_time
tidb_ttl_scan_worker_count
tidb_ttl_scan_batch_size
tidb_ttl_delete_worker_count
tidb_ttl_delete_batch_size
tidb_ttl_delete_rate_limit
tidb_ttl_running_tasks
We'll introduce some new metrics to monitor the TTL jobs:
ttl_queries
ttl_processed_expired_rows
ttl_query_duration
ttl_job_status
ttl_phase_time
ttl_insert_rows
In the above metrics, the optional values for sql_type label is 'select' and 'delete' and the optional values for result label is ok and error
We'll do some performance optimizations in the future to reduce the execution time and performance cost of the TTL job:
There are also some features we can support in the future:
TiKV supports TTL on RawKV, so we may get question that whether we can implement TiDB TTL in same concept. IMO, this method has some downsides:
There is another proposal to implement TTL by pushing configurations to TiKV: https://github.com/pingcap/tidb/pull/22763 . However, there are still some unresolved problems. For example, it may break the constraint of snapshot isolation.
CockroachDB also supports row-level TTL feature, and it is very similar with our design, for example:
CREATE TABLE events (
id UUID PRIMARY KEY default gen_random_uuid(),
description TEXT,
inserted_at TIMESTAMP default current_timestamp()
) WITH (ttl_expire_after = '3 months');
The difference is that the CockroachDB is using a hidden column to store the "expire" time instead of "create_time". So when altering a table's TTL options, it will result in some data change and affect the performance depending on the table size, see: https://www.cockroachlabs.com/docs/stable/row-level-ttl.html#add-or-update-the-row-level-ttl-for-an-existing-table
TTL_ENABLE or @@global.tidb_ttl_job_enable is OFF