Skip to content

TTL Service#

TTL stands for Time To Live. This feature makes it possible to automatically remove rows from the database that have lived beyond their expiration date. The TTL is implemented in the RonDB data nodes, thereby the expiration is exact. TTL also is transaction safe, this means that if you have locked a row with TTL, time will stop from this transactions point of view. Other transactions waiting for the lock or reading using a Read Committed will not see the row after expiration.

This ensures that we don’t mess up the transaction logic since an expiration would in principle be a delete. It also means that a row that expires can be expired immediately after commit of the transaction unless the expiration date was changed.

Expired rows are still present in the database and are still part of recovery logic. The rows are removed by the TTL purge service that executes in all the REST API servers. This service will delete all rows that have expired in a timely fashion.

There are special flags available to read rows that have expired, this is required for checkpoint logic, backup logic, but can also be used by an application that so desires.

1. Create table#

Creating a TTL (Time-to-Live) table is straightforward using the CREATE TABLE statement with a specific COMMENT:

"TTL=ttl_seconds@ttl_column"

  • ttl_seconds: Specifies how long (in seconds) a row will remain in the table. [0 - 4294967039]

  • ttl_column: Refers to a DATETIME or TIMESTAMP column used to determine the TTL. It must be a stored column: it cannot be a hidden column, a virtual generated column, or a column stored on disk.

A row whose TTL column is NULL never expires.

Example

The following example creates a TTL table where rows expire 10 seconds after the time specified in the event_time column:

CREATE TABLE tbl (
    credit_id INT,
    event_time DATETIME NOT NULL,
    amount int,
    PRIMARY KEY(credit_id, event_time)
) ENGINE=NDB, COMMENT="NDB_TABLE=TTL=10@event_time";

A TTL table can also be a ring buffer table, which keeps at most a fixed number of rows per key prefix, see Ring Buffer Tables.

2. Alter table#

By using the ALTER TABLE statement to modify the table comment, you can:

  1. Change the TTL Seconds for a Specific TTL Table or enable TTL functionality for a table

    "TTL=new_ttl_seconds@new_ttl_column"

    - new_ttl_seconds: Must adhere to the same rules as in CREATE TABLE.

    - new_ttl_column: The column type must still be DATETIME or TIMESTAMP.

  2. Disable TTL Functionality for a Specific Table

    "TTL=OFF"

Notes

1. If TTL is disabled or modified, expired rows that have not yet been purged may become visible again.

2. Dropping or changing the data type of the TTL column is not allowed unless TTL is first disabled on the table. ALTER TABLE statement will return failure if you do that

Example

The following example disables the TTL functionality on table “tbl”

ALTER TABLE tbl COMMENT="NDB_TABLE=TTL=OFF";

3. TTL Purging program#

The TTL purging program is integrated into rdrs2, which automatically removes expired rows. Additionally, you can perform extra configuration to optimize or control the purging process. The REST endpoints that show and change what the purging program does are described in TTL Purge REST API.

1. Add an Index on the TTL Column to Improve Purging Efficiency#

The purging program performs a table scan (with an adaptive batch size) to locate and delete expired rows. You can enhance this process by adding an index with the name "ttl_index" on the TTL column. This enables index-based scanning, which is more efficient and incurs a lower cost. The TTL column must be the first column of the index; otherwise the purging program does not use the index and scans the table.

Example:

ALTER TABLE tbl ADD INDEX ttl_index(event_time);

2. Create a Configuration Table to Build an “rdrs2 Purging Cluster”#

To distribute purging tasks across multiple rdrs2 nodes in a RonDB cluster, you can create a configuration table mysql.ttl_purge_nodes. By default, all rdrs2 nodes handle purging for all TTL tables. However, this configuration allows you to divide the purging workload among specific nodes.

Create the configuration table:

USE mysql;

CREATE TABLE ttl_purge_nodes (
    node_id INT NOT NULL,
    last_active DATETIME DEFAULT NULL,
    PRIMARY KEY (node_id)
) ENGINE = NDB;

Insert the node IDs for the rdrs2 nodes you want to participate in purging:

INSERT INTO ttl_purge_nodes VALUES (5, DEFAULT), (6, DEFAULT);

Behavior and Features

  1. Task Partitioning:

    - rdrs2 nodes (e.g., node 5 and node 6) split the partitions of every TTL table between them: with n purge nodes, each node purges about one n-th of the partitions of each table, and all nodes purge in parallel. When purge nodes join or leave, the partitions are redistributed automatically.

  2. High Availability:

    - If one rdrs2 node (e.g., node 5) goes down, the other nodes take over its partitions until it is back online.

    *Note: A node that has not refreshed its entry in
`ttl_purge_nodes` for 20 seconds is considered gone.*

3. Control When Rows Are Purged#

By default the purging program runs around the clock and deletes rows as soon as they have expired. You can delay purging with a purge lag, and restrict purging to a daily active window, for example to keep the delete load in off-peak hours. The purge lag and the window only control when expired rows are deleted and their memory is freed. Reads stop seeing a row as soon as its TTL has passed.

The control table

An optional NDB table, mysql.ttl_purge_ctrl, holds cluster-wide settings:

USE mysql;
CREATE TABLE ttl_purge_ctrl (
    ctrl_id INT NOT NULL PRIMARY KEY,
    value INT
) ENGINE = NDB;

-- Purge only between 03:00 and 05:00 UTC (minutes since midnight)
INSERT INTO ttl_purge_ctrl VALUES (2, 180), (3, 300);
-- Purge a row only when it has been expired for at least 600 seconds
INSERT INTO ttl_purge_ctrl VALUES (1, 600);

  • ctrl_id 1: the purge lag in seconds. An expired row is purged only once it has been expired at least this long. A negative value is used as 0.

  • ctrl_id 2 and 3: the start and end of a daily active window, in minutes since midnight UTC (0 to 1439). Purging runs only inside the window, which wraps past midnight when the start is larger than the end. Both rows must be present, and the start must differ from the end.

A missing row or a NULL value means no lag and no window. To convert a time of day to minutes, multiply the hours by 60 and add the minutes: 03:00 is 180 and 23:30 is 1410.

Every purge worker reads the three rows in one transaction at the start of every round, so a change takes effect within about one round on every rdrs2 node. The rdrs2 log shows each change, for example purge window size changed from 0 to 600 seconds, purge active window set to [03:00-05:00) UTC or purge active window cleared, purging around the clock.

Create the table exactly as shown. If a purge worker cannot read the table, for example because a column is missing or has another name, it does not purge in that round and logs Failed to get purge control settings. Retry.... Since all rdrs2 nodes read the same table, every round fails on every node and no rows are purged. After more than 10 failures in a row, the worker logs Pre-round errors exceeded 10 times and is restarted, which repeats about every 15 seconds. GET /0.1.0/ttl-purge/status does not show this reliably: it shows running or paused most of the time and error only for a few seconds per restart, so alert on the log lines instead. To fix it, drop the table and create it again exactly as shown. Purging resumes within seconds, without a restart of rdrs2.

Three ways to set an active window

Method Applies to Takes effect After an rdrs2 restart
Rows 2 and 3 in mysql.ttl_purge_ctrl All rdrs2 nodes Within about one round Kept
TTLPurge.ActiveWindow in the configuration file One rdrs2 node When rdrs2 starts Kept
active_window in PUT /0.1.0/ttl-purge/config One rdrs2 node In the next round Lost

The configuration file and the REST API take the window as "HH:MM-HH:MM" in UTC, for example "03:00-05:00" or "22:00-02:00". The REST API changes the window of the node until the next restart, see TTL Purge REST API.

Use ttl_purge_ctrl when all nodes should purge in the same window, including nodes that are started later. Use the configuration file when nodes need different windows, or when the configuration of rdrs2 is managed by deployment tooling. Use the REST API for a temporary change on one node.

The window that a node uses is:

  1. the window in ttl_purge_ctrl, if both rows are present and valid;

  2. otherwise the window of the node itself, from the configuration file or the last REST API change;

  3. otherwise none, and purging runs around the clock.

An invalid window in ttl_purge_ctrl, for example only one of the two rows, a value outside 0 to 1439, or a start equal to the end, is ignored, and the log shows the warning Invalid purge active window (start=..., end=...). An invalid TTLPurge.ActiveWindow stops rdrs2 at startup with an error, and an invalid window in the REST API is rejected with HTTP status 400.

To check the window that a node uses, read active_window and active_window_source in GET /0.1.0/ttl-purge/status on a purge node with purging enabled. On a disabled node, and on a node that is not in ttl_purge_nodes, these fields keep old values. A node that is not in ttl_purge_nodes logs Not the configured purging node, skip purging.... GET /0.1.0/ttl-purge/config shows only the window of the node itself, and SELECT * FROM mysql.ttl_purge_ctrl shows the cluster-wide window.

With several purge nodes, a node outside its window stops refreshing its entry in ttl_purge_nodes, and after 20 seconds its partitions move to the nodes that are inside their windows. When a node enters its window again, it skips one round so that the partitions are divided among the active nodes before it purges.

Changing the window

Change rows 2 and 3 in one statement:

UPDATE mysql.ttl_purge_ctrl SET value = IF(ctrl_id = 2, 60, 240)
  WHERE ctrl_id IN (2, 3);

With two separate statements, a purge worker can read the table between them and use a window with the new start and the old end for one round. To rule out any round with an unintended window, disable purging while you change the rows:

  1. Send {"enabled": false} to PUT /0.1.0/ttl-purge/config on every purge node, and wait until GET /0.1.0/ttl-purge/status shows disabled.

  2. Change the rows in mysql.ttl_purge_ctrl.

  3. Send {"enabled": true} on every purge node. The first round after that reads the new window before it purges.

Behavior at the edges of the window

  • Purging starts in the first round after the window opens. The log shows Entered the purge active window [03:00-05:00) UTC (from ttl_purge_ctrl), purging resumed.

  • When the window closes, the worker does not start another batch, also in the middle of a round. The batch that is already running is completed. If that batch fails, the worker retries it up to 10 times, one second apart, so while the cluster returns errors, purging can continue for some tens of seconds after the end of the window. A batch that fails with a transaction timeout is not retried. If a period must be kept free of purging, end the window a minute before it starts. The log shows Outside the purge active window [03:00-05:00) UTC (from ttl_purge_ctrl), purging paused.

  • Expired rows that are left when the window closes stay in the table, hidden from reads and using memory, until the next window. A backlog does not extend purging past the end of the window.

  • The log shows the warning The purge active window closed while purging was still saturated; an expired-row backlog likely remains. Consider a wider window or more purge nodes. when the worker was still deleting full batches in its last round, or when the window closed while tables were left in the round. The second case happens without any backlog, and more often with many TTL tables. A real backlog shows as this warning at almost every window close, day after day, and as a current_batch_size equal to max_batch_size for the table near the end of the window, see TTL Purge REST API.

  • If a worker cannot read ttl_purge_ctrl, it skips the round instead of purging with settings it could not read.

  • A node with purging disabled does not purge, whatever the window.

Recommended setup

  1. Create mysql.ttl_purge_ctrl exactly as shown above.

  2. Insert rows 2 and 3 in one statement, for example INSERT INTO mysql.ttl_purge_ctrl VALUES (2, 180), (3, 300);

  3. Check that the log of each purge node shows purge active window set to [03:00-05:00) UTC.

  4. Check that GET /0.1.0/ttl-purge/status on every purge node shows "active_window":"03:00-05:00" and "active_window_source":"ttl_purge_ctrl".

  5. Set up alerts on the log lines below.

Log lines to watch

All lines start with [TTL PWorker].

  • Invalid purge active window: the window in ttl_purge_ctrl is ignored.

  • The purge active window closed while purging was still saturated: a backlog of expired rows likely remains.

  • Failed to get purge control settings. Retry..., repeated: the worker cannot read ttl_purge_ctrl and does not purge.

  • Pre-round errors exceeded 10 times: the worker failed repeatedly before it could start purging, and is restarted. Errors are counted since the last completed round, and no round completes outside the window. With a short window, a few transient errors over many hours can add up and cause one restart, which is harmless. Restarts every 15 seconds or so mean a persistent error.

  • purge active window set to, purge active window cleared, Entered the purge active window and Outside the purge active window: confirm a change of the window and the start and end of each window.

4. Monitor and Control Purging through REST#

Each rdrs2 node reports on and controls its own purge worker through the REST endpoints below /0.1.0/ttl-purge, which exist only under API version 0.1.0. GET requests return the configuration, the status of the worker, and metrics for all TTL tables and for each table. PUT /0.1.0/ttl-purge/config changes the configuration of the node, for example {"enabled": false} or {"active_window": "03:00-05:00"}.

A change made with PUT applies to this node only and lasts until the node restarts. At startup, enabled and active_window come from TTLPurge.Enable and TTLPurge.ActiveWindow in the configuration file of the node. The chapter TTL Purge REST API describes every endpoint, its fields and how to use them to monitor purging.

5. Reading Expired Rows#

Expired rows stay in the table until the purging program deletes them. An NDB API application can still read them by bypassing the TTL check:

  1. Single-Key Operations: set the OO_TTL_IGNORE flag in the operation options:

      NdbOperation::OperationOptions options;
  options.optionsPresent |= NdbOperation::OperationOptions::OO_TTL_IGNORE;

  2. Scan Operations: set the SO_TTL_IGNORE flag in the scan options:

      NdbScanOperation::ScanOptions options;
  options.optionsPresent = NdbScanOperation::ScanOptions::SO_TTL_IGNORE;

In a MySQL Server, the session variable ttl_expired_rows_visible_in_delete makes expired rows visible to DELETE statements, so that a DELETE also removes rows that have expired but are not purged yet. RonSQL always skips expired rows.

6. Restrictions#

  1. Triggers on DELETE: Triggers on DELETE will not be fired when a row expires due to TTL.

  2. Foreign Key Constraints: A TTL table cannot take part in a foreign key relationship, neither as the parent nor as the child table. Since rows in a TTL table expire without a DELETE, expiry cannot fire cascading actions to child tables. RonDB rejects adding TTL to a table with foreign keys, and adding a foreign key to a TTL table.

  3. Timezone Requirement: RonDB uses UTC-0 timezone to determine row expiration. Therefore, the value in the TTL column must be specified in UTC-0 time to ensure correct expiration behavior.

7. Replication and Backup#

  • The purging program removes expired rows with delete operations. A MySQL Server that writes a binary log for the cluster logs these deletes, and they are replicated like other deletes.

  • A backup contains the rows that have expired but are not purged yet, and ndb_restore restores them as they are. After the restore they are hidden from reads and the purging program deletes them.

  • The purging program purges every TTL table. There is no setting that excludes a single TTL table from purging.