RonDB training · module 23 of 25 · 15 min

TTL and ring buffer tables

Two ways to keep event tables small: rows that expire after a time, and at most N rows per key.

Event tables · for fresh features

Event tables: fresh features need recent events, and only recent events

Windowed features

Last 10 minutes, last hour, last 12 hours: each a RonSQL query over recent rows (module 22).

TTL

Rows expire a fixed time after their event time: the table holds a time window.

Ring buffer

At most N rows per key: a busy customer's newest event replaces its oldest.

Both are table options in the comment (module 04), enforced inside RonDB, for every API.

docs: rondb_ttl.md · FSS 2026 talk (2026-10/rondb26 #windows) · code: ha_ndbcluster.cc (TTL, MAX_ROWS_PER_PK table modifiers)

PART 1

TTL: time to live

Rows that expire, exactly, inside the data nodes.

TTL · seconds after a time column

A TTL table expires each row a fixed time after its event time

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

-- change it, or turn it off
ALTER TABLE tbl
  COMMENT="NDB_TABLE=TTL=3600@event_time";
ALTER TABLE tbl
  COMMENT="NDB_TABLE=TTL=OFF";
  • TTL=seconds@column: 0 to 4 294 967 039 seconds, on a DATETIME or TIMESTAMP column.
  • The column holds UTC time.
  • TTL=OFF: expired rows not yet purged become visible again.
  • The TTL column cannot be dropped or changed while TTL is on.

docs: rondb_ttl.md (Create table, Alter table, Restrictions)

Semantics · exact, and transaction safe

TTL expiry: a row disappears at once for readers, and later from memory

Exact

Expiry is implemented in the data nodes: a row is invisible from the moment it expires.

Transaction safe

For a transaction holding a lock on the row, time stops. Others waiting for the lock, or reading committed, no longer see it.

Then purged

Expired rows stay stored, and in recovery, until the purge service deletes them.

Checkpoints and backups still read expired rows. An NDB API application can too, with OO_TTL_IGNORE on a key operation or SO_TTL_IGNORE on a scan.

docs: rondb_ttl.md (introduction) · code: include/ndbapi/NdbOperation.hpp (OO_TTL_IGNORE) · NdbScanOperation.hpp (SO_TTL_IGNORE)

Purging · in the REST API servers

TTL purging: every rdrs2 runs a purge worker; an index on the TTL column helps

-- index scans instead of table scans
ALTER TABLE tbl ADD INDEX ttl_index(event_time);

-- only these rdrs2 nodes purge, sharing the work
CREATE TABLE mysql.ttl_purge_nodes (
  node_id     INT NOT NULL,
  last_active DATETIME DEFAULT NULL,
  PRIMARY KEY (node_id)) ENGINE=NDB;
INSERT INTO mysql.ttl_purge_nodes
  VALUES (5, DEFAULT), (6, DEFAULT);
  • The worker scans with an adaptive batch size, 5 to 50 rows, and deletes expired rows.
  • An index named ttl_index on the TTL column turns that into a cheap range scan.
  • The listed nodes split the partitions; a node silent for 20 s loses its share to the others.
  • Purging can be limited to an active window of the day, in UTC.

docs: rondb_ttl.md (TTL Purging program) · code: rest-server2/server/src/ttl_purge.hpp (kTTLPurgeIndexName, kLeaseSeconds 20, batch 5–50, active window in UTC)

Restrictions · what expiry does not do

TTL restrictions: expiry is not a DELETE statement

RestrictionWhy
DELETE triggers do not fire on expiryExpiry is a time rule, not a statement
A TTL table cannot take part in foreign keys, as parent or as childExpiry fires no cascades
Expiry is computed in UTCStore the TTL column in UTC
TTL and a ring buffer on one tableRejected in 26.10; coming shortly

docs: rondb_ttl.md (Restrictions) · code: ha_ndb_ddl_fk.cc ("Can not use foreign key on TTL table", parent or child) · ha_ndbcluster.cc ("A table cannot be both TTL and MAX_ROWS_PER_PK")

PART 2

Ring buffer tables

At most N rows per key, the newest replacing the oldest.

Ring buffer · MAX_ROWS_PER_PK

Ring buffer tables: the last key column is a slot number that RonDB assigns

CREATE TABLE test.rb (
  client_id  INT NOT NULL,
  ring_idx   INT NOT NULL DEFAULT 0,
  ring_meta  VARBINARY(64),
  event_data VARCHAR(100),
  PRIMARY KEY (client_id, ring_idx)
) ENGINE = NDB,
  COMMENT="NDB_TABLE=MAX_ROWS_PER_PK=4@ring_idx@ring_meta";
  • 4 rows per client_id: the size, 1 to 2 147 483 647. ALTER can grow it, not shrink it.
  • ring_idx: the slot, 1 to 4, chosen by RonDB on insert.
  • ring_meta: bookkeeping, used only in the meta row.
  • MAX_ROWS_PER_PK=4 alone uses these two default names.

code: mysql-test/suite/ndb_ring_buffer/t/insert.test (the table) · create.test (Cases 3, 4, 12, 13, 22) · alter.test (Cases 1, 4, 5)

Wrapping · real output

Ring buffer wrap: the fifth insert overwrites the oldest row, in slot 1

mysql real output · ndb_ring_buffer/insert
INSERT INTO test.rb (client_id, event_data) VALUES (1, 'event_A');
-- … event_B, event_C, event_D …
INSERT INTO test.rb (client_id, event_data) VALUES (1, 'event_E');
SELECT client_id, ring_idx, event_data FROM test.rb
WHERE client_id = 1 ORDER BY ring_idx;
client_id ring_idx event_data
1 1 event_E
1 2 event_B
1 3 event_C
1 4 event_D
  • Inserts never name ring_idx; RonDB picks the next slot.
  • event_A in slot 1 was the oldest: event_E replaced it.
  • The table never holds more than 4 rows for client 1, however busy it is.

code: mysql-test/suite/ndb_ring_buffer/r/insert.result (Case 3 and the inserts before it; lines picked)

Meta row · slot 0

The meta row: a hidden slot 0 per key remembers where the next row goes

ring_idx = 0 · meta row 32 bytes in ring_meta:version · next_pos · counttotal_inserts (never wraps) 1 · event_Enewest 2 · event_Bnext to replace 3 · event_C 4 · event_D after 5 inserts: next_pos 2 · count 4 · total_inserts 5

Each key has a meta row at ring_idx = 0, stored as an ordinary row. Reads skip it unless ndb_ring_buffer_show_meta is ON.

An insert reads the meta row with an exclusive lock, writes the next slot and updates the counters, in one transaction: inserts for one key queue up.

code: storage/ndb/plugin/ha_ndbcluster_ring_buffer.cc (meta row format, 32 bytes; LM_Exclusive read of ring_idx 0) · ha_ndbcluster.cc (ndb_ring_buffer_show_meta, default OFF) · counters follow from the insert test

Rules · what the table accepts

Ring buffer rules: applications delete whole rings, not single rows

StatementOn a ring buffer table
INSERT without ring_idxAllowed: takes the next slot
INSERT naming ring_idx or ring_metaRejected: "Cannot specify ring_idx column in INSERT"
REPLACE · INSERT … ON DUPLICATE KEY UPDATERejected
UPDATE of other columnsAllowed
UPDATE of ring_idx, ring_meta or the meta rowRejected
DELETE on the other key columns, or with no WHEREAllowed: removes whole rings; a new insert starts afresh
DELETE naming ring_idx or a non-key columnRejected

From the NDB API, NdbRingBufferWriter adds rows in batches; ClusterJ handles ring buffer tables too.

code: mysql-test/suite/ndb_ring_buffer: insert.test (Cases 8, 9), update_delete.test (Cases 1–5, 10–21), clusterj_ring_buffer.test · include/ndbapi/NdbRingBufferWriter.hpp

PART 3

Why both

Time windows and count limits, for statistics.

Storage for statistics · active and inactive entities

TTL and ring buffer: each alone keeps too many rows, together just enough

  • TTL only stores too many rows for active entities: every event within the TTL is kept, far more rows than the statistics need.
  • Ring Buffer only keeps too many rows for non-active entities: their last rows stay, long after they stopped being relevant.
  • The combination minimises the storage volume needed to get the proper statistics.

FSS 2026 talk (2026-10/rondb26 #ring-why)

Status · the 26.10 code, and what comes next

TTL and ring buffer on one table: rejected in 26.10, coming shortly

In the 26.10 code

  • CREATE or ALTER with both TTL and MAX_ROWS_PER_PK fails: "A table cannot be both TTL and MAX_ROWS_PER_PK".
  • Choose per table: TTL for time windows, a ring buffer for count limits.

Coming shortly

  • One table with both TTL and MAX_ROWS_PER_PK.
  • Per key: the last N rows, none older than the TTL. For example the last 500 rows, at most 12 hours.

code: storage/ndb/plugin/ha_ndbcluster.cc (lines 10400 and 17425) · mysql-test/suite/ndb_ring_buffer/t/create.test (Case 17) · FSS 2026 talk (2026-10/rondb26 #windows) · the combination: RonDB team, October 2026

Recap · module 23

What to take away

  • TTL=seconds@column: rows expire exactly, in UTC, purged later by rdrs2.
  • A ttl_index and ttl_purge_nodes make purging cheap and shared.
  • MAX_ROWS_PER_PK=N@ring_idx@ring_meta: the newest row replaces the oldest.
  • RonDB owns the slots and the meta row; applications delete whole rings, not single rows.
  • Each alone keeps too much somewhere; one table with both is coming shortly.

Next · 24 · Global replication

RonDB training · 25 modules · you are at 23

Next · 24 · Global replication