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.

The user could use certain conditions when a row should not be expired, this is useful in a feature store. It could happen that the insertion process stalls and this could lead to no rows present any more to calculate aggregates, to avoid this problem it is possible to ensure that those rows are not deleted such that they can be made visible if no other rows are present.

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) will split all TTL tables into two partitions, purging expired rows from different tables in parallel.

  2. High Availability:

    - If one rdrs2 node (e.g., node 5) goes down, another node will take over its purging tasks until it is back online.

    *Note: The heartbeat timeout for detecting node failure is 20
seconds.*

3. Create a Configuration Table to Enable “Keeping the Last Row” Functionality#

By default, the purging program eventually removes all expired rows. If additional conditions are required for purging, such as “keeping the last row” functionality, you can create the configuration table to specify these conditions.

Create the configuration table “ttl_purge_conds”:

USE mysql;
CREATE TABLE ttl_purge_conds (
    db VARCHAR(256) NOT NULL,
    table_name VARCHAR(256) NOT NULL,
    conds_on_cols VARCHAR(512) DEFAULT NULL,
    reserved_int INT DEFAULT 0,
    reserved_varchar VARCHAR(512) DEFAULT NULL,
    PRIMARY KEY (db, table_name)
) ENGINE = NDB DEFAULT CHARSET = latin1;

Example

Suppose you want to configure the purging program for a table tbl in the test database with the following condition:

For each expired row, the purging program must extract the credit_id value and ensure that at least one visible row with the same credit_id exists in the table before deleting the expired row.

Insert the configuration into ttl_purge_conds:

INSERT INTO ttl_purge_conds VALUES ("test", "tbl", "credit_id", DEFAULT, DEFAULT);

Note: multiple condition columns are separated by ,

Behavior

This configuration tells the purging program to:

  1. Double-check the Condition: For the test.tbl TTL table, verify that at least one visible row exists with the same credit_id value before deleting an expired row.

  2. Preserve Rows Meeting the Condition: The purging program will skip deleting expired rows that violate the specified condition.

Retrieving Rows with Ignored TTL in RonDB

If you configure the ttl_purge_conds table to retain the “last row” meeting specified conditions, the purging program will leave these rows intact. To retrieve these rows, you can bypass the TTL functionality using a special flag.

  1. Single-Key Operations: For 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: For scan operations, use the SO_TTL_IGNORE flag in the scan options:

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

  3. Using RonSQL for Pushdown Aggregation Queries

    If you are using RonSQL to execute pushdown aggregation queries, you can:

    1. Enable the Specific Session Variable: Turn on the session variable that tells RonSQL to ignore TTL during query execution.

    2. Re-execute the Pushdown Aggregation SQL: Resend the aggregation query to RonSQL with the session variable enabled to retrieve expired rows.

4. 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 be used as the parent table in a foreign key relationship. Since rows in a TTL table may expire automatically, it won’t fire any cascading DELETE actions to child tables.

  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.