RonDB training · module 04 of 25 · 18 min

Table options, partitioning, disk columns and BLOBs

The choices made when a table is created: how it replicates, how it splits into partitions, and where its columns are stored.

Table options · in the table comment

RonDB options ride in the COMMENT of CREATE TABLE and ALTER TABLE

CREATE TABLE t1 (
  a INT PRIMARY KEY,
  b INT
) ENGINE=NDB
COMMENT="NDB_TABLE=READ_BACKUP=1,
         PARTITION_BALANCE=FOR_RA_BY_NODE";

NDB_TABLE=	Covered in
`READ_BACKUP`	this module
`FULLY_REPLICATED`	this module
`NOLOGGING`	this module
`PARTITION_BALANCE`	this module
`PARTITION_HASH`	this module
`TTL`	module 23 · TTL
`MAX_ROWS_PER_PK`	module 23 · ring buffer

Several options are separated by commas. An ALTER TABLE changes only the options it names; the others keep their values.

docs: table_options.md · release_notes_251019.md (RONDB-1074, PARTITION_HASH) · code: storage/ndb/plugin/ha_ndbcluster.cc (ndb_table_modifiers: these seven)

READ_BACKUP · on by default

READ_BACKUP: read any replica, or answer writes one step sooner

READ_BACKUP=1 · the default

The write is acknowledged after the complete phase.
Every replica returns the new value at once: reads go to the closest one.
Reads stay local in a colocated MySQL server and data node.

READ_BACKUP=0

The write is acknowledged after the commit phase: slightly lower write latency.
Only the primary replica is sure to have the new value.
For tables with many writes and few reads.

Write throughput is the same either way. Changing only this option in ALTER TABLE is an online operation; ndb-read-backup sets it for new tables.

docs: table_options.md (Read Backup Feature) · code: ha_ndbcluster.cc (ndb_read_backup, default 1)

FULLY_REPLICATED · the whole table in every data node

A fully replicated table has a replica in every node, not only in one node group

ndb_desc real output · 4 data nodes, 1 replica

ndb_desc -dtest t1

PartitionCount: 4

FragmentCount: 16

PartitionBalance: FOR_RP_BY_LDM

Table options: readbackup, fullyreplicated

HashMap: DEFAULT-HASHMAP-3840-4

Created with COMMENT="NDB_TABLE=FULLY_REPLICATED=1".
4 partitions, copied into each of the 4 node groups: 16 fragments.
A write goes to the first node group; triggers copy it to the others in the same transaction.
Read Backup is implied: any node can serve a read.
Made for small, read-mostly tables used in joins.

docs: table_options.md (Fully replicated tables) · code: mysql-test/suite/ndb/r/ndb_fully_replicated.result (ndbinfo.dictionary_tables printed in ndb_desc format; lines picked, values unchanged) · ndb_fully_replicated.cnf (NoOfReplicas=1)

NOLOGGING · for data that is stale after a restart anyway

NOLOGGING: a table without REDO log and checkpoints

CREATE TABLE quotes (
  symbol CHAR(8) PRIMARY KEY,
  price  DECIMAL(12,4)
) ENGINE=NDB
COMMENT="NDB_TABLE=NOLOGGING=1";

No REDO log writes, no local checkpoints: about 30% less work per update.
The replicas still protect it against node failures; after a restart of the whole cluster the data is gone.
Example: stock prices changing hundreds of thousands of times per second.
Cannot be changed online. ndb-table-no-logging sets it for new tables.

docs: table_options.md (No REDO logging) · the quotes table is an illustration

PART 1

Partitioning

Every table is partitioned. You choose the key and how many partitions.

Partition key · a subset of the primary key

Rows with the same partition key live in the same partition

CREATE TABLE inodes (
  parent_id BIGINT,
  name      VARCHAR(255),
  id        BIGINT,
  PRIMARY KEY (parent_id, name)
) ENGINE=NDB
PARTITION BY KEY (parent_id);

Default: the primary key is the partition key.
PARTITION BY KEY (cols) picks some primary key columns instead; KEY() means the primary key.
Here every entry of one directory lands in one partition, as HopsFS does with the parent inode.

docs: table_options.md (Partition Key) · the inodes table is an illustration

Partition key · the same question as a sharding key

Give related tables the same partition key

TPC-C: the warehouse id

Customers, districts, orders, order lines and stock all belong to a warehouse. Partition them all by warehouse id, and one order touches one partition per table.

HopsFS: the parent inode

Inodes are partitioned by their directory's id. Listing a directory is a scan of one partition.

Unlike separate shards, RonDB still runs transactions and queries across partitions. A good key only makes the common case cheaper.

docs: table_options.md (Partition Key)

Scans · one partition, or all of them

Pruned scans: a scan that fixes the partition key visits one partition

A table with 8 partitions. Each partition has its own primary key index and its own ordered indexes.

With the partition key fixed, the scan is pruned to one partition: it scales with any number of partitions.

Without it, the scan starts in every partition. Each start costs an index traversal and messages, so the startup cost grows with the partition count.

docs: table_options.md (Partitioning of tables, Considerations for selecting number of partitions)

Partition hash · new in 25.10.19

Partition hash: one entity's rows in a few partitions, not one or all

A table of events with the primary key (entity_id, event_time) and 16 partitions. A common query scans one entity's last few hundred events.

Partition key entity_id: the scan visits one partition. Cheap to start, but one thread reads every row of the entity, one after the other.

The whole key: the rows spread evenly, but the scan starts in all 16 partitions, a cost that grows with the cluster.

PARTITION_HASH=1:1:4: the entity's rows spread over 4 partitions, scanned in parallel. Real tables can have hundreds of partitions, and the scan still visits 4.

code: Ndb.cpp (compute_partition_hash_fanout_value) · DbtcMain.cpp (a pruned scan covers fanout fragments) · release_notes_251019.md (RONDB-1074) · which 4 partitions depends on the hashmap

PARTITION_HASH · base keys, detail keys, fanout

PARTITION_HASH=base:detail:fanout splits the primary key in two

CREATE TABLE events (
  entity_id  BIGINT NOT NULL,
  event_time DATETIME(3) NOT NULL,
  amount     INT,
  PRIMARY KEY (entity_id, event_time)
) ENGINE=NDB,
  COMMENT="NDB_TABLE=PARTITION_HASH=1:1:4";

-- hash = (base hash / 4) × 4
--      + detail hash % 4

1 base key column, entity_id, and 1 detail key column, event_time: together the whole primary key.
4 is the fanout: one entity's rows fill a block of 4 hash values, so 4 partitions.
A key lookup still hashes the whole key and finds its one partition.
WHERE entity_id = 42, on an ordered index that starts with entity_id such as PRIMARY, scans only those 4 partitions.

code: ha_ndbcluster.cc (parsePartitionHash: base_keys:detail_keys:fanout) · Ndb.cpp (the hash) · NdbScanOperation.cpp (pruning needs equality on the base key, on an index that starts with it)

PARTITION_HASH · real output and rules

PARTITION_HASH at work: a scan for one entity is pruned

mysql real output · ndb_partition_hash_fanout

-- tf: PRIMARY KEY (entity_id, ts, id), 8 partitions,

-- PARTITION_HASH=1:2:4, 20 entities x 25 rows

SELECT * FROM tf FORCE INDEX (PRIMARY)

WHERE entity_id = 7 AND ts < 5;

5 rows · ndb_scan_count 1 · ndb_pruned_scan_count 1

SELECT * FROM tf WHERE ts = 24 AND entity_id < 3;

3 rows · ndb_scan_count 1 · ndb_pruned_scan_count 0

SELECT * FROM tf FORCE INDEX (PRIMARY)

WHERE entity_id IN (3, 11) AND ts = 2;

2 rows · ndb_scan_count 1 · ndb_pruned_scan_count 0

SELECT * FROM tf WHERE entity_id = 7

AND ts = 3 AND id = 7003;

1 row · ndb_scan_count 0: a key lookup

The fanout must divide 3840 (7 is rejected) and be at most the partition count.
Not with PARTITION BY KEY on part of the key, nor with FULLY_REPLICATED.
Several entities in one scan (IN) are not pruned yet.
Changing it takes a copying ALTER TABLE. It works with TTL.

code: mysql-test/suite/ndb/t/ndb_partition_hash_fanout.test and r/…result (rows and counters condensed to one line each) · ha_ndbcluster.cc (validatePartitionHashCreate)

Partition count · a balance

Partition count: more partitions give more writes per second, dearer unpruned scans

≈100 000

writes per second per partition: more with small rows, fewer with large ones

× N

the startup cost of a scan that does not fix the partition key

+ CPUs

at a restart, more partitions let more CPUs rebuild indexes

The default, PartitionsPerNode = 2 in config.ini (1 to 32), gives 2 partitions per data node. Applications that always use the partition key can afford many more.

docs: table_options.md (Number of partitions per table, Considerations for selecting number of partitions) · code: ConfigInfo.cpp (PartitionsPerNode default 2, 1–32)

PARTITION_BALANCE · RP = read primary, RA = read any replica

Partition balance sets the count per table, balanced as node groups are added

Balance	Partitions	2 node groups × 2 replicas, PartitionsPerNode 2
FOR_RP_BY_LDM · default	node groups × replicas × PartitionsPerNode	8
FOR_RA_BY_LDM	node groups × PartitionsPerNode	4
FOR_RP_BY_NODE	node groups × replicas: one per node	4
FOR_RA_BY_NODE	node groups: the fewest	2
FOR_RP_BY_LDM_X_2 … _X_16	the default × 2, 4, 6, 8 or 16	16 to 128
FOR_RA_BY_LDM_X_2 … _X_4	FOR_RA_BY_LDM × 2, 3 or 4	8 to 16

code: storage/ndb/src/kernel/blocks/dbdih/DbdihMain.cpp (Dbdih::getFragmentCount) · include/ndbapi/NdbDictionary.hpp · docs: table_options.md (Partition Balance)

Partition count · set it, change it

Growing the partition count is online; shrinking it copies the table

-- few partitions for a small table
CREATE TABLE countries (...)
ENGINE=NDB COMMENT=
  "NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";

-- or an explicit count
CREATE TABLE t2 (...) ENGINE=NDB
PARTITION BY KEY() PARTITIONS 4;

more partitionsOnline ALTER TABLE.

fewer partitionsA copying ALTER TABLE.

PARTITIONS nNo balance guarantee; no REORGANIZE after adding node groups; can only grow online.

fully replicatedIts balance cannot change online.

Prefer PARTITION_BALANCE: it keeps the count right as the cluster grows. Use PartitionsPerNode for the cluster-wide default.

docs: table_options.md (Syntax for Partition Balance, Setting explicit number of partitions)

Schema design · what recovery writes

Hot columns: checkpoints write whole rows, so keep busy columns apart from quiet ones

REDO log

Logical: logs only the columns a write changed.

Checkpoints

Write the full row of every changed row, plus a share of the unchanged rows that grows with the write activity (module 14).

Disk columns

The UNDO log grows with the size of the disk columns.

A counter updated every second in a row of 2 KB of read-mostly text makes every checkpoint write the text again. Put the counter in its own table, after measuring that it matters.

docs: table_options.md (Schema Considerations) · module 14 shows the partial checkpoint

PART 2

Disk columns

Larger databases than memory, for the columns that are never indexed.

Disk columns · memory for keys, disk for the rest

Disk columns: non-indexed columns can live on SSD or NVMe, cached while used

Primary key and indexed columns always stay in memory; there are no disk indexes.
Large values, BLOBs and statistics columns are the natural candidates.
Disk columns are checkpointed and recoverable, like memory columns.
Since RonDB 22.10 variable-size disk columns take only the space they need.

Device	Random 32 KB reads per second
Hard drive	about 200
SSD	tens of thousands
NVMe	hundreds of thousands

Disk data reads one 32 KB page at a time, so access is random: use SSD or NVMe for tablespaces. Hard drives still suit the sequential REDO and UNDO logs, checkpoints and backups.

docs: disk_columns.md · code: storage/ndb/src/kernel/blocks/diskpage.hpp (NDB_PAGE_SIZE = 32768)

Disk layout · tablespace, extents, pages, UNDO log

Disk layout: the row in memory points to its disk columns in a tablespace page

A tablespace hands out extents (1 MB by default) to tables; an extent stays with its table until the table is dropped.

The row in memory points to its disk part, and the disk part points back, so scans can read the disk in page order.

Pages are cached in memory. Only committed changes reach a page, and the UNDO log is written first.

docs: disk_columns.md (Row format for disk columns, Tablespaces, UNDO log) · code: lgman.cpp (one logfile group per data node, OneLogfileGroupLimit)

Disk data · three statements

Disk data in SQL: the UNDO log, then a tablespace, then a table that uses it

CREATE LOGFILE GROUP lg_1
  ADD UNDOFILE 'lg_1.dat'
  INITIAL_SIZE 4G UNDO_BUFFER_SIZE 32M
  ENGINE=NDB;

CREATE TABLESPACE ts_1
  ADD DATAFILE 'ts_1.dat'
  USE LOGFILE GROUP lg_1
  INITIAL_SIZE 16G EXTENT_SIZE 16M
  ENGINE=NDB;

CREATE TABLE t1 (a INT, b INT STORAGE MEMORY)
  ENGINE=NDB STORAGE DISK TABLESPACE ts_1;

One logfile group per cluster: every data node gets its own UNDO log files. Add UNDO files online with ALTER LOGFILE GROUP.
Data files cannot grow; add new ones online with ALTER TABLESPACE.
STORAGE DISK on the table applies to every non-indexed column; STORAGE MEMORY keeps b in memory.
This t1 has no primary key: the hidden key stays in memory, a goes to disk.

docs: disk_columns.md (Syntax for disk data, Controlling which columns are stored on disk)

Disk data · created with the cluster

Disk data in config.ini: the cluster creates the files at its first start

# config.ini, [ndbd default]
InitialLogfileGroup=undo_buffer_size=32M;lg_1.dat:4G;lg2.dat:4G
InitialTablespace=name=ts_1;extent_size=16M;ts_1.dat:16G;ts2.dat:16G

Without a name they are called DEFAULT-LG and DEFAULT-TS; the initial tablespace always uses DEFAULT-LG.
With AutomaticMemoryConfig, the default, UndoBuffer sizes the UNDO buffer (24 MB per LDM thread) and undo_buffer_size is ignored.
Files are written in full when created or added: online, but it takes time and disk bandwidth.
Managed RonDB creates lg_1 (4 GB) and ts_1 (8 GB, 16 MB extents): add files before storing much on disk.

docs: disk_columns.md (UNDO log syntax, Tablespace syntax, Initialisation of files) · code: storage/ndb/src/kernel/vm/Configuration.cpp (get_and_set_undo_buffer) · lgman.cpp (the configured UNDO buffer replaces the one in the command)

Disk data · the settings that matter

Disk data tuning: the page cache, the IO threads and the file locations

config.ini	What it does	Value
DiskPageBufferMemory	The page cache for disk pages	a few GB or more for real use
DiskPageBufferEntries	Records of recently evicted pages, per cached page	10, about 100 bytes each
DiskIOThreadPool	OS threads reading and writing disk data files	8
FileSystemPathDD	Directory for disk data files	…DataFiles and …UndoFiles split them

Extent descriptions stay locked in the cache: 32 bytes per 1 MB extent, so a 4 TB tablespace of 1 MB extents keeps about 128 MB of it.

code: storage/ndb/src/common/mgmcommon/ConfigInfo.cpp (DiskIOThreadPool, DiskPageBufferEntries) · storage/ndb/src/kernel/blocks/diskpage.hpp (extent_header_words: 4 words + 4 bits per page) · tsman.cpp (load_extent_pages) · docs: disk_columns.md (Placement of disk data files)

PART 3

BLOBs

Large values, cut into parts that the data nodes handle one at a time.

BLOB and TEXT · a real-time engine never moves a large value at once

BLOB and TEXT: the first bytes stay in the row, the rest goes to a part table

A BLOB or TEXT column keeps up to 256 bytes inline in the row. A small value needs nothing more.

The rest goes in parts of 2000 bytes, one row each, keyed by the base row's primary key plus a part number: 256 + 6 × 2000 + 544 = 12 800. Seven part rows are seven more operations in the transaction.

docs: blobs.md (the 12.8 KB example) · code: ha_ndbcluster.cc (part sizes) · storage/ndb/src/ndbapi/NdbBlob.cpp (part table: base key columns + NDB$PART)

ndb_desc · inline size, part size, part table

Each BLOB type has its own part size

ndb_desc real output · mysql-test/suite/ndb

ndb_desc -pb -dtest t2

-- Attributes --

b Blob(256,0,0) ... BT=<none> tinyblob

c Blob(256,4000,0) ... BT=NDB$BLOB_XX_2 mediumblob

d Blob(256,2000,0) ... BT=NDB$BLOB_XX_3 blob

e Blob(256,13948,0) ... BT=NDB$BLOB_XX_4 longblob

g Text(256,2000,0;utf8mb4_0900_ai_ci) ... text

i Text(256,13948,0;utf8mb4_0900_ai_ci) ... longtext

Type	Inline	Part
TINYBLOB	256	none
BLOB, TEXT	256	2000
MEDIUM…	256	4000
LONG…	256	13 948
JSON	4000	8100

code: mysql-test/suite/ndb/r/ndb_desc_blob.result (table t2, columns picked, middle fields cut) · ha_ndbcluster.cc (part sizes; JSON inline 4000, part 8100)

BLOB costs · tables, operations and memory

BLOBs are fine for large objects, not for small ones

A table per column

Each BLOB column adds a part table, partitioned like its base table: more metadata.

Memory per part

Parts can be on disk, but each part's key stays in memory: about 50–60 bytes. 1 TB of BLOBs needs about 30 GB of memory.

Whole objects

SQL always reads or writes the entire value, part by part.

Values of about 1 KB? Use VARBINARY(2048), not a BLOB. Column comments tune BLOBs: NDB_COLUMN=MAX_BLOB_PART_SIZE uses 13 948-byte parts, NDB_COLUMN=BLOB_INLINE_SIZE=n moves the inline limit.

docs: blobs.md · code: ha_ndbcluster.cc (ndb_column_modifiers)

Recap · module 04

What to take away

Table options go in COMMENT="NDB_TABLE=…": READ_BACKUP (on), FULLY_REPLICATED, NOLOGGING.
Pick a partition key shared by related tables, so scans prune to one partition.
PARTITION_HASH spreads one entity's rows over a few partitions, so its scans run in parallel.
PARTITION_BALANCE sets how many partitions; growing is online, shrinking copies.
Disk columns: logfile group, tablespace, extents, 32 KB pages; indexes stay in memory.
BLOBs keep 256 bytes inline and the rest in a part table; small values belong in VARBINARY.

Next · 05 · Changing schema online

RonDB training · 25 modules · you are at 04