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.

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";

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:

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.

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. An optional NDB table, mysql.ttl_purge_ctrl, holds cluster-wide settings that change this:

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.

• 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.

Every purge worker reads the table once per round, so an UPDATE takes effect within about one round on every rdrs2 node.

Each rdrs2 node can also have a window of its own, set with TTLPurge.ActiveWindow in its configuration file, for example "03:00-05:00", or at runtime through the REST endpoint /0.1.0/ttl-purge/config. A valid window in ttl_purge_ctrl takes precedence over the node’s own 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.

4. 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.

5. 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.