🎃

TiDBのTTL（Time to Live）

2024/05/08に公開

TiDB

tech

はじめに

MySQLと互換性のあるTiDBですが、TiDB独自の機能も存在します。
本記事ではそのひとつである（少なくとも執筆時点においては）TTL機能について見ていきます。

参考：https://docs.pingcap.com/tidb/stable/time-to-live

TTLの機能概要

使い所としては、DBに永続的に保存し続ける必要のないログデータなど、ストレージ観点で一定の条件に基づいてデータを削除したいような要件がある場合などとなります。

TiDBのTTLは

テーブルのTTL属性の定義に基づくレコードごとの有効期間を定め
バックグラウンドジョブで有効期間をチェックし、期限切れのレコードは削除する
という機能です。

TTLを利用する場合、具体的には各テーブルごとに以下の設定を定義します。

TTL：TTLによる削除対象データの条件を定義します。条件は日時によるものやもう少し複雑なものも定義できます（ドキュメント参照）
TTL_ENABLE：当該テーブルのTTLジョブ実行の有効/無効を定義します（デフォルト：ON）OFFの場合、TTLジョブは実行されません（≒削除されません）。
TTL_JOB_INTERVAL：当該テーブルにおけるバックグラウンドTTLジョブ実行間隔を定義します（デフォルト：1h）

つまり、イメージは以下の図のようになります。

削除対象の条件をcreated_atで有効期間60分とし、1時間ごとにTTLバックグラウンドジョブが実行される場合の例です。※2024/4/1 14:00よりTTLジョブが実行している
色分けした削除対象データが、同色のTTLジョブ実行のタイミングで削除されていくイメージです。

使ってみる

それでは、実際にTiDBのTTL機能を使ってみましょう。
TiDB（ローカル ※tiup playground）で確認します。

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.5.1
Edition: Community
Git Commit Hash: 7d16cc79e81bbf573124df3fd9351c26963f3e70
Git Branch: heads/refs/tags/v7.5.1
UTC Build Time: 2024-02-27 14:21:44
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.01 sec)

まず、created_atが5分が有効期間として定義したテーブルを作成します。
TTL = created_at + INTERVAL 5 MINUTE

CREATE TABLE t1
(   
    `id` bigint NOT NULL AUTO_INCREMENT,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) TTL = `created_at` + INTERVAL 5 MINUTE;

デフォルトだとTTLジョブ実行間隔（TTL_JOB_INTERVAL）が1hであるため、動作確認としては長いので短くします。

-- TTL_JOB_INTERVALを変更
ALTER TABLE t1 TTL_JOB_INTERVAL = '5m';

-- TTL_JOB_INTERVALを確認
mysql> SHOW CREATE TABLE t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![ttl] TTL=`created_at` + INTERVAL 5 MINUTE */ /*T![ttl] TTL_ENABLE='ON' */ /*T![ttl] TTL_JOB_INTERVAL='5m' */ |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

何レコードかテキトーにINSERTします。

INSERT INTO t1 VALUES ();

レコードを確認します。まだ消えていません。

mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2024-05-07 16:56:39 |
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t1;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  1 | 2024-05-07 16:54:07 |
|  2 | 2024-05-07 16:54:14 |
|  3 | 2024-05-07 16:54:26 |
|  4 | 2024-05-07 16:55:48 |
|  5 | 2024-05-07 16:55:55 |
|  6 | 2024-05-07 16:56:12 |
+----+---------------------+
6 rows in set (0.01 sec)

一定の時間経過後、改めてレコードを確認します。

mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2024-05-07 17:00:42 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  4 | 2024-05-07 16:55:48 |
|  5 | 2024-05-07 16:55:55 |
|  6 | 2024-05-07 16:56:12 |
+----+---------------------+
3 rows in set (0.00 sec)

定義した有効期間を越えた３レコードは削除されていることが確認できました。

バックグラウンドのTTLジョブ実行履歴の確認

ちなみにTTLジョブの実行履歴はテーブルmysql.tidb_ttl_table_statusで確認できます。

SELECT * FROM mysql.tidb_ttl_table_status ORDER BY last_job_start_time DESC LIMIT 1\G;

以下のように、ジョブの実行時間や削除されたレコード数などが確認できます。

mysql> SELECT * FROM mysql.tidb_ttl_table_status ORDER BY last_job_start_time DESC LIMIT 1\G;
*************************** 1. row ***************************
                      table_id: 102
               parent_table_id: 102
              table_statistics: NULL
                   last_job_id: 4a6cce1643c846edb02aab77feaecf86
           last_job_start_time: 2024-05-07 17:00:02
          last_job_finish_time: 2024-05-07 17:00:08
           last_job_ttl_expire: 2024-05-07 16:55:02
              last_job_summary: {"total_rows":3,"success_rows":3,"error_rows":0,"total_scan_task":1,"scheduled_scan_task":1,"finished_scan_task":1}
                current_job_id: NULL
          current_job_owner_id: NULL
        current_job_owner_addr: NULL
     current_job_owner_hb_time: NULL
        current_job_start_time: NULL
        current_job_ttl_expire: NULL
             current_job_state: NULL
            current_job_status: NULL
current_job_status_update_time: NULL
1 row in set (0.00 sec)

last_job_ttl_expireに記載されている2024-05-07 16:55:02以前のレコードが削除されています。

留意事項

ドキュメントにも記載ありますが、以下には留意必要です。

TiDB Serverlessでは使えない（執筆時点において。今後サポート予定があるそうです）
一時テーブルでは利用できない（そもそも一時テーブルなので一般的な使い方の範疇ではTTL不要かと）
TTLが設定されているテーブルは、外部キー制約の主テーブルとして他のテーブルから参照されることをサポートしていない（≒以下の例のように親テーブルではTTLが使えない）

-- 外部キー制約の参照元（親テーブル）を作成
CREATE TABLE parent(   
    `id` bigint NOT NULL AUTO_INCREMENT,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`));

-- 子テーブルを作成
CREATE TABLE child (
    `id` BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `pid` BIGINT NOT NULL, 
    `name` VARCHAR(64) NOT NULL,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pid (pid),
    FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE);

-- 親テーブルにTTL属性を追加 -> NG
mysql> ALTER TABLE parent TTL = `created_at` + INTERVAL 5 MINUTE;
ERROR 8152 (HY000): Set TTL for a table referenced by foreign key is not allowed

-- 子テーブルにTTL属性を追加 -> OK
mysql> ALTER TABLE child TTL = `created_at` + INTERVAL 5 MINUTE;
Query OK, 0 rows affected (0.08 sec)

以下の条件を満たすテーブルの場合、TTLジョブの実行が遅くなる可能性あり（単一のTiDBノード上で順次実行されるため）
- Cluseterd IndexのテーブルでPrimary Keyがintでもバイナリでもない場合
TiDBツールとの兼ね合いで、以下のTiDBのデータはTTL_ENABLEがOFFに設定される
- BR（バックアップ＆リストア用のツール）によるバックアップからリストアされたTiDBのデータ
- Lightning（TiDBへのデータインポートツール）を利用してデータをImportしたTiDBのデータ
  ※ TiDB CloudだとImport機能が該当
- TiCDC（TiDBから非同期レプリケーションするためのツール）を使ってレプリケーションしているダウンストリーム側のTiDBのデータ
  ※アップストリーム側でTTLにより削除されたデータは、レプリケーションによりダウンストリーム側でも削除されるため
FLASHBACK TABLE (DATABASE / CLUSTER)機能を利用した場合、TTL_ENABLEがOFFに設定される

補足：以前のバージョンでの仕様

TTL機能はTiDB（LTSだと）v6.5.0から有効になりました。

TiDB v6.5.xのバージョンにおいては、TTL定義はテーブル単位ではなくSystem Variablesによるデータベース単位で共有の設定だったようです。
※ドキュメント確認したらminimumのインターバルは10分でした。
参考：tidb_ttl_job_run_interval

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v6.5.5
Edition: Community
Git Commit Hash: 71bcc44f77a37cfb0a6dc3660e092c78c1e46acb
Git Branch: heads/refs/tags/v6.5.5
UTC Build Time: 2023-09-18 10:13:45
GoVersion: go1.19.12
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
1 row in set (0.00 sec)

参考：関連パラメータ

-- TTL_ENABLEに相当
mysql> SHOW VARIABLES LIKE 'tidb_ttl_job_enable';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| tidb_ttl_job_enable | ON    |
+---------------------+-------+
1 row in set (0.00 sec)

-- TTL_JOB_INTERVALに相当
mysql> SHOW VARIABLES LIKE 'tidb_ttl_job_run_interval';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| tidb_ttl_job_run_interval | 1h0m0s |
+---------------------------+--------+
1 row in set (0.00 sec)