Skip to content

Ring Buffer Tables#

A ring buffer table keeps at most N rows for each primary key prefix. The prefix is the primary key without its last column. The last primary key column is a slot number that RonDB assigns. When a prefix already has N rows, the next INSERT for that prefix overwrites the oldest row of the prefix.

A typical use is a table of events per entity, such as the last 100 events of each user or the last 1000 readings of each sensor. With a plain table such a table grows without limit, and an application has to delete old rows itself. A TTL table, see TTL Service, removes rows after a fixed time, but it does not limit the number of rows of an entity: an active user can have thousands of recent rows. A ring buffer table limits the number of rows per entity: the table holds at most N data rows and one meta row per prefix. The INSERT that overwrites the oldest row, the write of the new row and the update of the ring state happen in one transaction.

A ring buffer table can also have a TTL. Such a table keeps at most N rows per prefix and hides rows whose TTL has passed, see Ring buffer tables with TTL below.

Ring buffer tables are available since RonDB 26.10.

Creating a ring buffer table#

A ring buffer table is created with the table option MAX_ROWS_PER_PK in the COMMENT of the CREATE TABLE statement. The table must have two columns that RonDB manages: a slot column, called ring_idx by default, and a metadata column, called ring_meta by default.

CREATE TABLE user_events (
  user_id INT NOT NULL,
  ring_idx INT NOT NULL DEFAULT 0,
  ring_meta VARBINARY(64),
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  event_type VARCHAR(64),
  payload VARCHAR(256),
  PRIMARY KEY (user_id, ring_idx)
) ENGINE=NDB COMMENT='NDB_TABLE=MAX_ROWS_PER_PK=100';

This table keeps the last 100 events of each user. The prefix is user_id. A prefix can have several columns; with PRIMARY KEY (tenant_id, user_id, ring_idx) the prefix is (tenant_id, user_id).

The option has two forms:

  • MAX_ROWS_PER_PK=SIZE uses the default column names ring_idx and ring_meta.

  • MAX_ROWS_PER_PK=SIZE@idx_col@meta_col names the slot column and the metadata column, for example MAX_ROWS_PER_PK=100@row_seq@row_meta.

SIZE is the number of rows per prefix, from 1 to 2147483647. Other table options can be set in the same comment, separated by commas, for example NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM,MAX_ROWS_PER_PK=200.

Column rules#

  • The table must have a declared PRIMARY KEY.

  • The slot column must be INT or INT UNSIGNED and must have a DEFAULT, normally 0. The default is needed because an INSERT never gives a value for the slot column.

  • The slot column must be the last column of the primary key, and it must be declared after the other primary key columns in the table definition.

  • The metadata column must be a nullable VARBINARY with a length of at least 32.

  • No index other than the primary key can contain the slot column or the metadata column.

  • User columns of type BLOB, TEXT, JSON or GEOMETRY must be nullable. Nullable columns of these types work.

Table rules#

  • A ring buffer table cannot have foreign keys, neither as parent nor as child table.

  • A ring buffer table cannot be fully replicated (FULLY_REPLICATED=1, or the server default ndb_fully_replicated=1 without FULLY_REPLICATED=0).

  • The only partitioning allowed is PARTITION BY KEY or PARTITION BY LINEAR KEY. HASH, RANGE and LIST partitioning are rejected.

  • A conflict function for the table in mysql.ndb_replication is not supported. CREATE TABLE fails with Conflict functions are not supported on ring buffer tables.

  • All data nodes must run a version that supports ring buffer tables. Until then CREATE TABLE fails with MAX_ROWS_PER_PK not supported by current data node versions.

A violated rule makes CREATE TABLE fail with an error that names the rule, for example:

ERROR HY000: Table storage engine 'ndbcluster' does not support the create
option 'Ring index column must be the last column of the PRIMARY KEY'

Partitioning#

By default the partition key is the whole primary key, including the slot column. The rows of one prefix are then spread over all partitions, and a query for one prefix scans all partitions. To keep all rows of a prefix in one partition, partition the table on the prefix columns:

CREATE TABLE user_events (
  ...
  PRIMARY KEY (user_id, ring_idx)
) ENGINE=NDB COMMENT='NDB_TABLE=MAX_ROWS_PER_PK=100'
  PARTITION BY KEY(user_id);

See Table options for more about the partition key.

Inserting rows#

An INSERT gives values for the user columns only. RonDB sets the slot column and the metadata column.

INSERT INTO user_events (user_id, event_type, payload)
VALUES (1, 'click', '{"product_id": 42}');

INSERT INTO user_events (user_id, event_type, payload)
VALUES (1, 'click', '{"product_id": 42}'),
       (1, 'purchase', '{"product_id": 42}'),
       (2, 'click', '{"product_id": 99}');

Each prefix has a hidden meta row with slot number 0. It is created by the first INSERT into the prefix and records the next slot to write, the number of occupied slots and the total number of inserts into the prefix. The data rows use slots 1 to N. Each INSERT writes the slot after the previous one, and after slot N it continues at slot 1, overwriting the oldest row.

With MAX_ROWS_PER_PK=3, five inserts A, B, C, D and E into one prefix give:

After insert Slot 1 Slot 2 Slot 3
A A
B A B
C A B C
D D B C
E D E C

An INSERT that overwrites a slot writes every column of the new row. Columns that the INSERT leaves out get their default value or NULL; they do not keep the values of the overwritten row.

A multi-row INSERT, INSERT ... SELECT and LOAD DATA read the meta row of a prefix once, write consecutive rows with the same prefix in one batch, and update the meta row once at the end of the batch.

The following are rejected on a ring buffer table:

  • An INSERT that gives a value for the slot column or the metadata column.

  • REPLACE, INSERT ... ON DUPLICATE KEY UPDATE and LOAD DATA ... REPLACE. The application does not choose the slot, so these statements have no meaning on a ring buffer table.

Unique indexes#

Unique indexes on user columns work as on other tables. The meta rows are not entered into unique indexes. An INSERT that overwrites a slot frees the unique values of the overwritten row, so they can be used again. An INSERT whose unique value is held by another live row fails with a duplicate key error.

INSERT IGNORE does not turn a duplicate unique value into a warning on a ring buffer table. The statement fails at commit with Got error 125 - 'Transaction has been rolled back' during COMMIT, and nothing is inserted. A plain INSERT gets the usual duplicate key error.

Reading rows#

Queries on a ring buffer table use ordinary SQL. The meta rows are hidden from all reads: full table scans, index scans, primary key lookups and COUNT(*). A lookup with ring_idx = 0 returns no row.

SELECT * FROM user_events WHERE user_id = 1;

SELECT * FROM user_events WHERE user_id = 1 ORDER BY created_at DESC;

Once a ring has wrapped, slot order is not insertion order. To read the rows of a prefix in time order, store a time column, as created_at above, and use ORDER BY on it.

Showing the meta rows#

The session variable ndb_ring_buffer_show_meta makes reads return the meta rows. It is meant for diagnostics. UPDATE and DELETE statements never see meta rows, even with the variable set.

SET ndb_ring_buffer_show_meta = ON;
SELECT user_id, HEX(ring_meta) FROM user_events
WHERE user_id = 1 AND ring_idx = 0;
SET ndb_ring_buffer_show_meta = OFF;

The metadata column of a meta row holds 32 bytes, with integers in little-endian byte order:

Offset Size Field
0 2 Format version, 1
4 4 Next slot to write, 1 to N
8 4 Number of occupied slots
16 8 Total number of inserts into the prefix

The other bytes are reserved. In data rows the metadata column is NULL.

Updating rows#

An UPDATE can change the user columns of data rows. The WHERE clause can use any column, including the slot column.

UPDATE user_events SET event_type = 'corrected'
WHERE user_id = 1 AND ring_idx = 3;

An UPDATE fails if it sets the slot column, the metadata column or another primary key column. Meta rows cannot be updated.

Deleting rows#

A DELETE removes whole prefixes: all data rows of the prefix and its meta row. The next INSERT into the prefix starts a new ring at slot 1. A DELETE cannot remove single rows of a ring.

The WHERE clause must give every primary key column except the slot column with =. One of these columns can use IN (...) instead. The WHERE clause can contain no other condition.

-- Allowed
DELETE FROM user_events WHERE user_id = 1;
DELETE FROM user_events WHERE user_id IN (1, 2, 3);
DELETE FROM tenant_events WHERE tenant_id = 7 AND user_id IN (100, 200);

-- Rejected
DELETE FROM user_events WHERE user_id BETWEEN 1 AND 10;
DELETE FROM user_events WHERE user_id = 1 OR user_id = 2;
DELETE FROM tenant_events WHERE tenant_id = 7;
DELETE FROM user_events WHERE user_id = 1 AND ring_idx = 3;
DELETE FROM user_events WHERE user_id = 1 AND event_type = 'click';
DELETE FROM user_events;

A range, OR, a partial prefix or a DELETE without WHERE fails with DELETE on ring-buffer table must give every PK-prefix column with = or IN; use TRUNCATE TABLE to clear the table. A condition on the slot column or on a column outside the primary key fails with DELETE WHERE on ring-buffer table may only reference PK-prefix columns (excluding ring_idx). To remove all rows of the table, use TRUNCATE TABLE.

The values in the WHERE clause must be fixed for the statement: constants, prepared statement parameters, stored program variables or user variables. They must be written directly, not inside an expression such as CAST(? AS SIGNED), and they must convert exactly to the column type, for example a number for a numeric column and a string for a string column. RAND(), stored functions and user-defined functions are not allowed. A TIMESTAMP prefix column can only be used when the session time zone is UTC or a fixed offset, and a SET column cannot be used.

The following are also rejected:

  • A DELETE with LIMIT or ORDER BY, and a multi-table DELETE.

  • A DELETE on a ring buffer table that has DELETE triggers.

A DELETE locks the meta row of each prefix it deletes before it reads the data rows. A DELETE therefore waits for INSERTs into these prefixes that are in progress and removes all of their rows, and later INSERTs into these prefixes wait until the DELETE commits. A DELETE of a prefix that has no rows yet does not lock anything, so it can race with the first INSERT into the prefix; a row of that INSERT can then remain until the ring overwrites its slot.

Changing a ring buffer table#

These properties are fixed when the table is created:

  • The ring size. ALTER TABLE with another MAX_ROWS_PER_PK size fails with Cannot change ring buffer size; use DROP+CREATE to resize. Neither growing nor shrinking is possible. To resize, create a new table and copy the data.

  • Whether the table is a ring buffer table. MAX_ROWS_PER_PK=off fails with Cannot disable ring buffer via ALTER, and adding MAX_ROWS_PER_PK to an existing table fails with Cannot enable ring buffer via ALTER; use DROP+CREATE.

  • Whether a ring buffer table has a TTL, see Ring buffer tables with TTL.

Other changes work as on other tables, for example adding a column, or adding or dropping an index on user columns. ALTER TABLE ... ADD PARTITION and REORGANIZE PARTITION cannot use ALGORITHM=INPLACE; they run as a copying ALTER. A copying ALTER keeps the meta rows, so the rings continue where they were. The slot column and the metadata column cannot be dropped.

CREATE TABLE ... LIKE creates another ring buffer table with the same size. CREATE TABLE ... AS SELECT copies the visible rows into a plain table.

Concurrency#

  • INSERTs into different prefixes do not wait for each other.

  • An INSERT locks the meta row of its prefix exclusively until the transaction ends. INSERTs into the same prefix therefore run one after the other.

  • Two INSERTs that start a new prefix at the same moment can both find no meta row. One of them then fails with a deadlock error and can be retried. This also applies to INSERT IGNORE.

  • A transaction that inserts into several prefixes can deadlock with a DELETE of several prefixes. One of them fails with a deadlock error and can be retried.

  • Reads use read committed, as on other tables. A scan that runs while an INSERT overwrites rows of the same prefix can return some old and some new rows.

NDB API#

The data nodes reject writes to a ring buffer table that do not follow the ring protocol, with error 940 Direct write to ring-buffer table not allowed. Use SQL, NdbRingBufferWriter or ClusterJ. This applies to inserts, updates and deletes from any NDB API application. Reads work as on other tables, and the meta rows are hidden from them. Changes applied by the replication applier are allowed.

An NDB API application inserts rows with the class NdbRingBufferWriter, declared in NdbRingBufferWriter.hpp and included by NdbApi.hpp.

const NdbDictionary::Table *table = dict->getTable("user_events");
const NdbRecord *record = table->getDefaultRecord();
NdbTransaction *trans = ndb->startTransaction();

NdbRingBufferWriter writer(table, record, trans);
if (writer.getErrorCode() != 0) { /* handle the error */ }

// rowBuffer holds the user columns in NdbRecord layout.
// userMask has a bit for each user column that the row sets,
// not for ring_idx or ring_meta.
const NdbOperation *op = writer.addRow(rowBuffer, userMask);
writer.addRow(rowBuffer2, userMask);

// flush() writes the meta row; it must be called before commit.
if (writer.flush() != 0) { /* roll back */ }
trans->execute(NdbTransaction::Commit);
ndb->closeTransaction(trans);

  • addRow() queues one row. The mask has the same format as the mask argument of NdbTransaction::insertTuple(). Rows with the same prefix as the previous row are batched: the writer reads the meta row once and updates it once. When the prefix changes, the writer sends the previous batch.

  • addRow() returns the NdbOperation of the row, which gives access to BLOB and TEXT columns through getBlobHandle().

  • A row that overwrites an occupied slot is written as an update of that slot. A column that is not in the mask keeps the value of the overwritten row, so set every column whose value matters.

  • flush() sends the last batch and the meta row update. The destructor does not flush.

  • deleteOldest(pkPrefixRow, maxN, &outActual) deletes up to maxN of the oldest rows of one prefix. Later inserts fill the freed slots in order. It is not available on a ring buffer table with TTL.

  • When addRow() returns nullptr, or flush() or deleteOldest() returns -1, read getErrorCode() and getErrorMessage() and roll back the transaction. A writer that has failed cannot be used again; create a new writer on a new transaction.

  • Wrong arguments, such as a table that is not a ring buffer table or an NdbRecord without the primary key or ring columns, give error 4118 Parameter error in API call.

ClusterJ#

ClusterJ writes ring buffer tables through the ring protocol without changes in the application: makePersistent() on an instance of a ring buffer table inserts it as a new row of its prefix. The application does not set the slot column or the metadata column. Session.isRingBufferTable(Class) tells whether the table mapped to a class is a ring buffer table.

  • ClusterJ cannot write ring buffer tables with BLOB or TEXT columns. Use SQL for these tables.

  • Updates and deletes through ClusterJ are rejected by the data nodes with error 940. Use SQL to update rows or delete prefixes.

  • Reads work as on other tables.

Backup, restore and replication#

  • A backup contains the data rows and the meta rows. ndb_restore restores both, and inserts after the restore continue each ring where it was.

  • SQL dumps and mysqlbinlog output of ring buffer tables cannot be replayed through a MySQL client: the INSERTs name the slot column, and row events are applied with REPLACE semantics. Both are rejected. Use ndb_restore to restore ring buffer tables.

  • Ring buffer tables replicate with RonDB replication. An INSERT that overwrites a slot is logged as a row write, also with ndb_log_update_as_write=0. Conflict functions are not supported, see Table rules.

Ring buffer tables with TTL#

A ring buffer table with TTL keeps at most N rows per prefix and hides rows whose TTL has passed. The TTL purge in the REST API server removes the expired rows, as for any TTL table. See TTL Service for TTL in general.

CREATE TABLE user_events (
  user_id INT NOT NULL,
  ring_idx INT NOT NULL DEFAULT 0,
  ring_meta VARBINARY(64),
  ts TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  payload VARCHAR(200),
  PRIMARY KEY (user_id, ring_idx),
  INDEX ttl_index (ts)
) ENGINE=NDB
  COMMENT='NDB_TABLE=TTL=86400@ts,MAX_ROWS_PER_PK=100';

This table keeps the newest 100 events of each user and hides events older than one day.

Rules#

The rules of ring buffer tables and of TTL tables both apply. In addition:

  • The TTL column cannot be a primary key column. CREATE TABLE fails with TTL column cannot be a PRIMARY KEY column of a ring table.

  • TTL is fixed when the table is created. ALTER TABLE can change the TTL seconds, but it cannot add TTL to a ring buffer table or remove it with TTL=OFF. Both fail with Cannot enable/disable TTL on a ring table; use DROP+CREATE.

  • An index named ttl_index with the TTL column first is recommended. Without it each purge round scans the whole table.

  • All data nodes must run a version that supports TTL on ring buffer tables. Until then CREATE TABLE fails with TTL ring buffer not supported by current data node versions. Existing ring buffer tables and TTL tables are not affected during the upgrade.

Behavior#

The visible rows of a prefix are the N newest inserted rows, minus those whose TTL has passed.

Each INSERT writes the next slot and overwrites what is there: a live row, an expired row, or nothing. Expired rows behave as on any TTL table: SELECT does not return them, and UPDATE and DELETE do not find them. The TTL purge deletes expired rows in the background. This does not change what queries return; it only frees memory. A purged slot stays empty until the ring reaches it again.

The following timeline shows one prefix of a table with ring size 3 and a TTL of one hour:

Time Action Slot 1 Slot 2 Slot 3 Visible Data rows in memory
00:00 insert A, B, C A B C A B C 3
00:30 insert D D B C D B C 3
01:01 B and C expire D B (hidden) C (hidden) D 3
01:02 purge runs D empty empty D 1
01:10 insert E D E empty D E 2
01:31 D expires D (hidden) E empty E 2
01:40 insert F D (hidden) E F E F 3
01:50 insert G G E F G E F 3

At 01:50 the INSERT of G overwrites the expired row D, so the purge does not have to delete it. At 01:02 the purge removes B and C.

Other behavior to know:

  • A row whose TTL column is NULL never expires. The ring overwrites it like any other row.

  • The TTL column of a data row can be updated, which moves its expiry.

  • A DELETE of a prefix removes the live rows and the meta row of the prefix. Expired rows that are not purged yet stay until the purge or the ring removes them. With the session variable ttl_expired_rows_visible_in_delete set, the DELETE removes them as well.

  • A prefix whose rows have all expired keeps its meta row until the prefix is deleted or the table is truncated.

  • An expired row keeps its unique index values until the purge deletes it or the ring overwrites its slot. An INSERT with the same unique value fails with a duplicate key error until then.

  • INSERT ... SELECT from the table copies the visible rows only. A copying ALTER TABLE does not copy expired rows.

  • With ndb_ring_buffer_show_meta set, the TTL column of a meta row shows the maximum value of its type, 2038-01-19 03:14:07 UTC for TIMESTAMP and 9999-12-31 23:59:59 for DATETIME. Meta rows never expire.

NDB API and ClusterJ#

  • Every insert through NdbRingBufferWriter or ClusterJ must set every column of the table except the slot column and the metadata column. A column left out would keep the value of the overwritten row, including a TTL value that may have passed. NdbRingBufferWriter fails with error 4359 Inserts on a ring buffer table with TTL must set every column when the NdbRecord or the mask of a row lacks a column. SQL INSERTs always write every column.

  • deleteOldest() fails with error 4358 deleteOldest is not supported on a ring buffer table with TTL and leaves the transaction unchanged. After a purge the oldest slots can be empty, so deleting the oldest slots and deleting the oldest rows are not the same thing. To track a position in a ring, use the slot number and the total number of inserts in the meta row.

What the combination gives#

Property Ring only TTL only Ring with TTL
Rows per prefix limited to N yes no yes
Rows hidden after a fixed age no yes yes
Memory of inactive prefixes freed no yes, by the purge yes, by the purge
Memory limited when the purge is not running yes no yes
deleteOldest() available yes does not apply no
Meta row count equals the number of rows yes does not apply no, upper bound

A ring alone keeps the last N rows of a prefix forever, also when the prefix gets no more inserts. With TTL these rows become invisible and the purge frees them, so the memory of the table follows the number of active prefixes. A TTL table alone can grow without limit when inserts are faster than the purge or the purge is stopped. With a ring each prefix has at most N data rows and one meta row, whatever the purge does.

Trade-offs#

  • The count in the meta row is an upper bound. The purge deletes rows without updating the meta row, so the number of occupied slots in the meta row includes slots whose rows the purge has deleted. It is an upper bound on the number of live rows. The total number of inserts stays exact. To count live rows, use SELECT COUNT(*) for the prefix.

  • Ring order and TTL order are independent. The ring overwrites in insertion order. TTL hides rows by the value of the TTL column. When the TTL column holds the insertion time, as with DEFAULT CURRENT_TIMESTAMP, the oldest row is always the first to expire. When the TTL column holds another value, or is updated, the ring can overwrite a row with a late expiry before a row that is about to expire. Make the TTL column the insertion time unless you want that effect.

  • Purge cost. The purge handles a ring buffer table like any TTL table: one scan per table per round and one delete per expired row. With ttl_index, a round on a table without expired rows costs little. Without it, each round scans all rows of the table. On a busy prefix the ring would soon overwrite the expired rows anyway, so some purge deletes do work the ring would also do. Purge deletes are ordinary deletes: they are written to the REDO log and the binary log and are replicated.

  • Short waits. An INSERT that reaches a slot that the purge has locked waits until the purge batch commits. This happens only on prefixes that have expired rows. A transaction that inserts several rows into one prefix can in rare cases deadlock with a purge scan; the transaction then fails with a deadlock error and can be retried.

  • Purge waits on open INSERTs without ttl_index. Without ttl_index the purge scans the whole table and waits for rows that open INSERT transactions hold. When the wait exceeds the deadlock detection timeout, the purge skips the table until its next round. A user transaction that keeps an expired row locked has the same effect. The purge continues with the other tables.

Operational notes#

  • The TTL purge runs in the REST API server. Its status and per-table counters are available from the REST API server, see TTL Purge REST API.

  • In the worst case a prefix takes N data rows and one meta row, whether the purge runs or not. When the purge is not running, expired rows stay hidden but use memory until the purge runs again or the ring overwrites them.

  • The purge deletes on a replication source are replicated to the replica. An INSERT that later overwrites a purged slot is logged as a row write, which creates the row again on the replica.

  • A backup contains expired rows that are not purged yet, as for any TTL table. After a restore they are hidden, and the purge removes them.

Restrictions#

  • A ring buffer table needs the slot column and the metadata column in its definition.

  • Single rows cannot be deleted with SQL; DELETE removes whole prefixes.

  • The ring size cannot be changed after the table is created.

  • REPLACE, INSERT ... ON DUPLICATE KEY UPDATE and LOAD DATA ... REPLACE are not supported.

  • Foreign keys, fully replicated tables, partitioning other than KEY, and conflict functions are not supported.

  • Indexes on the slot column or the metadata column are not supported.

  • User columns of type BLOB, TEXT, JSON or GEOMETRY must be nullable.

  • SQL dumps and binary log replay through a MySQL client cannot restore a ring buffer table; use ndb_restore.