Add TTL table support

[lcwangchao]

Feature Request

Feature Dev

• RFC for TTL table support #39263
• Add parser support for TTL syntax #39268
• Support TTL options in DDL CREATE TABLE ... #39269
• Support show TTL options in SHOW CREATE TABLE ... #39270
• Support altering TTL options in DDL ALTER TABLE ... #39271
• Add system table mysql.tidb_ttl_table_status #39272
• TTL Table should not be reference as a parent table by a foreign key #39351
• Forbid creating/altering a temporary table with TTL options #39364
• Add tools to generate TTL SELECT/DELETE SQLs #39339
• introduce ttl.PhysicalTable to provide some common methods for TTL #39428
• Implement TTL job scheduling. #39465
• TTL job fails to finish after the workers are shrinked #39859
• Implement TTL scan task #39480
• Implement TTL delTask #39554
• Add method to split TTL scan tasks from one table #39626
• Support time window for TTL schedule
• Add monitor metrics and Grafana charts for TTL #39848
• The TTL options should use comment format when show create table ... #39852
• Update tidb_ttl_delete_batch_size default value to match doc #39877
• Support split TTL table when primary key has multiple columns and the first column is int or binary #39895
• TTL delete rows with 1pc #39957
• Support to trigger a TTL job manually #40345
• Turn off TTL_ENABLE when restore TTL tables with lighting or BR #40215
• Make TTL compatible with RECOVER and FLASHBACK #40265
• TTL distributed execution #40361
• forbid to create/alter a table with TTL options when pk contains float/double column #40486
• TTL delete sql should retry when write conflict happens #40502
• Add table to store TTL job history #40654
• Support telemetry for TTL #40519
• GC history rows in mysql.tidb_ttl_job_history #40807
• Add metrics about TTL tasks #40810
• mysql.tidb_ttl_job_history should show running job #41419
• optimize bazel configurations for TTL #41276
• Add a http api to trigger TTL job manually for test #41597
• CDC support TTL table tiflow#7997
• the concurrency of running TTL tasks should be limited #41585
• Add resource limitation for TTL queries. #40928
• Save the TTL scan task checkpoint and recover from it when failover
• TTL job manager should stop a job manually when it finds some schema change.
• Support more metrics: TTL delay distribution and insert/delete rows by day #42466
• Add some TTL charts in DBaaS / TiDB Cloud
• Check clock drifting in TiDB
• Delete dropped/truncated table id in mysql.tidb_ttl_table_status
• Support to cancel a running TTL job
• Add TiDB launch option to disable running TTL jobs in current node
• Support split table when the first column of primary key is utf8 and utf8mb4 #42393

Timer Framework integration

• Add TTLTimersSyncer to sync TTL meta to timers #45164
• implement timer for TTL #45268
• refactor ttl to separate submit new job and take a timeout job codes #45285
• Migrate TTL job schedule to timer framework #45468
• Add metrics to timer framework #45610

Bug Fix

• fix data race in ttlDeleteWorker #39633
• tidb panic when getSession in TTL #39864
• TTL txn will not return error when rolling back #39917
• TTL task fails when primary key contains column with type ENUM #40456
• TTL will delete unexpected rows when timezone changed #41043
• import data to a TTL table with lightning will panic #42012
• Error about updating tidb_ttl_table_status #40231
• ttl job's start time will be override by mistake after failover #44767
• there are some memory leak in TTL's implement #45510

[YangKeao]

In 2022 / 12 / 16, the basic function of TTL works on master TiDB (and we are doing some performance test). We could try to cherry-pick these PRs to the 6.5. Here is a list of PRs:

• ttl,ddl: disable ttl for fk and temp table (#39581) #39598 (DONE)
• ttl: Implement scan and delete task for TTL (#39481) #39615 (DONE)
• ttl: add system table for TTL status (#39315) #39619 (DONE)
• ttl: add info schema cache and ttl table status table cache for ttl scheduler (#39467) #39628 (DONE), depend on 39619
• ttl: fix data race in ttlDeleteWorker (#39635) #39646 (DONE)
• ttl: add methods to split ttl scan tasks (#39627) #39775 (DONE)
• ttl: ouput TTL comment format when show create table (#39853) #39862 (DONE)
• *: fix slow query nil pointer panic when get session for TTL (#39865) #39874 (DONE)
• ttl: implement ttl job schedule framework (#39472) #39830 (DONE), depend on 39775
• variable: fix outdated time shift for variable with TypeTime (#39720) #39767 (DONE)
• ttl, variables: add system variables for ttl job schedule (#39727) #39869 (DONE) This PR will enable the TTL syntax / variables
• ttl: Add metrics for TTL (#39849) #39875 (DONE)
• ttl: fix scan workers shrink (#39860) #39897 (DONE)
• ttl: reschedule scan tasks after update task state (#39891) #39901 (DONE)
• ttl: fix ttl txn will not return error when rolling back (#39918) #39921 (DONE)
• ttl: initialize notifyStateCh in ttl job manager (#39956) #39959 (DONE)
• ttl: TTL support split scan ranges for some primary key with more than one columns (#39896) #39908 (DONE)
• ttl: use optimistic transaction with 0 retry limit for ttl (#39924) #39943 (DONE)
• ttl: modify default value of tidb_ttl_delete_batch_size to 100 (#39878) #39889 (DONE)
• ttl: add log in the whole lifecycle of a job (#39931) #39942 (DONE)
• ttl: periodically update state for a job in heartbeat (#39939) #39944 (DONE)
• ttl: enable 1pc for ttl delete (#39958) #39960 (DONE)

[bb7133]

Should we close this issue now?