RonDB training · module 10 of 25 · 16 min

Query execution

Key lookups, parallel scans, a small virtual machine on every row, and joins and aggregates that run inside the data nodes.

Access · what the data nodes execute

Every query becomes key lookups, scans, or a tree of both

Key lookup

One row by primary or unique key. One partition, a few microseconds.

Scan

Full table or ordered index range, in parallel over partitions, or pruned to one.

Pushed query

Lookups and scans linked into a tree, run by DBSPJ: joins, and in RonSQL aggregates and CTEs.

On each row any of them can run a small interpreted program: a filter, a computation, an update in place.

docs: rondb_parallel_query.md · rondb_generic_interpreter.md

Transaction coordinator · which data node runs it

Choosing the TC: a hint puts the transaction on the node holding its first row

  1. The NDB API takes a hint at start of transaction: a table and a primary key, hashed to the node holding that row.
  2. The MySQL server uses the first statement of the transaction as the hint, if it names a primary key.
  3. Without a hint: round robin over the data nodes, or the nearest one set with ndb-data-node-neighbour.
  4. A stale hint costs a hop, never correctness: the data nodes always know where rows are.

docs: rondb_ndb_api.md (Selection of data node to communicate with) · rondb_advanced_mysql_config.md (Setting data node neighbor)

Key lookup · read committed, Read Backup on

Key lookup: the row goes from the ldm thread straight to the application

APINDB API DBTCtc thread TCKEYREQ DBDIH: which nodes? ldm thread with a replica DBLQH → DBACC: find the element→ DBTUP: read the row, run any program LQHKEYREQ TRANSID_AI · the row, directly to the API

The API sends the operation to a TC thread in the chosen data node.

DBTC looks up the partition's nodes in DBDIH and sends LQHKEYREQ to the ldm thread owning a replica: any replica for read committed with Read Backup.

The row goes back to the API in TRANSID_AI, without passing TC again. A locked read also confirms to TC (module 09).

docs: rondb_signal_arch.md (Basic read transaction) · code: dblqh/DblqhMain.cpp (execLQHKEYREQ, c_acc->execACCKEYREQ, c_tup->execTUPKEYREQ)

PART 1

Scans

Parallel over partitions, a few rows at a time.

Scan protocol · one request, many fragments

Scan protocol: TC scans every partition at once; rows go straight to the API

API DBTC SCAN_TABREQ partition 0 · ldm partition 1 · ldm partition 2 · ldm partition 3 · ldm SCAN_FRAGREQ to all 4 → rows in TRANSID_AIstraight to the API batch done: SCAN_FRAGCONF to TC · SCAN_TABCONF to the API · SCAN_NEXTREQ asks for more

The API asks DBTC for a scan of the table, with an optional filter program.

DBTC sends a fragment scan to every partition in parallel: with 8 partitions, 1 million rows are scanned as 8 × 125 000.

Each ldm thread filters its rows and sends the matches directly to the API.

After each batch the fragment reports to TC, TC to the API, and the API asks for the next batch: flow control from the reader.

docs: rondb_parallel_query.md (Full table scan in RonDB) · code: include/kernel/GlobalSignalNumbers.h (SCAN_TABREQ, SCAN_FRAGREQ, SCAN_FRAGCONF, SCAN_TABCONF, SCAN_NEXTREQ)

Scans · what each one reads

Scan kinds: full, range, multi-range, pruned

ScanReadsPartitions
Full table scanEvery row, in row order, in DBTUPall, in parallel
Range scanRows between two bounds of an ordered index, in DBTUXall, in parallel
Multi-range scanSeveral ranges in one scan operationall, in parallel
Partition-pruned scanAny of the above with the partition key fully givenone

Pruned scans are how sharded applications stay fast: HopsFS lists a directory by scanning one partition.

docs: rondb_parallel_query.md (Range scan in RonDB, Partition pruned range scans)

Scheduling · scans share the thread

A scan reads at most 16 rows per signal, fewer if they are large

The rule in DBLQH

  • Up to 16 rows directly, then a new signal to itself.
  • A row costs about 250 ns plus 8 ns per word sent, by benchmarks; the code needs no estimate.
  • So 16 rows of a normal table stay well below the 10 microseconds a signal may run; only large rows come close.

The effect

  • Key lookups wait at most a few microseconds behind a scan.
  • A long analytic scan and thousands of lookups share an ldm thread with stable latency.
  • Module 06: run to completion, keep it short.

code: dblqh/Dblqh.hpp (ZMAX_SCAN_DIRECT_COUNT 16) · dblqh/DblqhMain.cpp ("never execute for more than about 5-10 microseconds"; its "750 ns of fixed cost per row" dates from 2015 hardware; 250 ns is from benchmarks)

Batches · how much a scan sends at once

Scan batches: each fragment sends a batch, then waits for the reader

LimitValueWhy
Rows per batch384 defaultConfigurable per scan
Rows per batch, at most4000 · 992 with locksA locked row holds a lock and an operation record
Bytes per batch, per fragment32 KB defaultDivided by the row size, it caps the rows
Bytes per batch, all fragments512 KB defaultProtects the API from overload
  • A locking scan asks the hash index for a lock on each row it returns (module 08).
  • A slow reader slows the scan, not the data node: the next batch waits for SCAN_NEXTREQ.

code: include/kernel/ndb_limits.h (DEF_BATCH_SIZE 384, MAX_PARALLEL_OP_PER_SCAN_RC 4000, _WITH_LOCK 992, SCAN_BATCH_SIZE 32768, MAX_SCAN_BATCH_SIZE 524288) · docs: rondb_concurrency_control.md

Condition pushdown · filter where the data is

Condition pushdown: the WHERE clause runs in the data nodes, in parallel

Without

Every row travels to the MySQL server, whose one user thread filters them all.

With

The condition becomes an interpreted program; each ldm thread filters its partitions and returns only matches.

Also for lookups

A key lookup can carry a condition too: the row is returned only if it matches.

The MySQL server pushes conditions automatically; EXPLAIN shows "Using pushed condition".

docs: rondb_parallel_query.md (Full table scan in RonDB) · rondb_generic_interpreter.md

PART 2

The interpreter

Ship the function to the data, not the data to the function.

Interpreter · a virtual machine in DBTUP

The interpreter: a small virtual machine runs on each row a lookup or scan touches

  • 8 registers of 64-bit signed integers, NULL at start.
  • 64 KB of memory since RonDB 24.10, to read whole or partial column values into.
  • Input parameters from the request; output values back to the API.
  • Reads and writes columns of the row it runs on: one row, no other.
  • Built in the NDB API with NdbInterpretedCode and sent with the operation.
  • Used by MySQL (pushed conditions), the REST server, Rondis and RonSQL.
  • Low-level by design: use it where shipping data would cost more.

docs: rondb_generic_interpreter.md · new_features_2410.md (Added many new interpreter functions) · code: include/ndbapi/NdbInterpretedCode.hpp

Instruction set · grouped

Interpreter instructions: arithmetic, columns, memory, branches, library calls

FamilyExamples
Arithmeticadd, subtract, multiply, divide, shifts, AND, OR, XOR, NOT
Load constantNULL, or a 16, 32 or 64-bit constant, into a register
Read and write columnsread_attr, write_attr: a column to or from a register
MemoryColumn data into memory, memory into registers, length conversions
BranchesCompare registers or a column with a value, jump to a label
Library functions and outputReady-made helpers; send results back to the API
Exitinterpret_exit_ok keeps the row; exit_nok skips it in a scan, aborts a key operation

docs: rondb_generic_interpreter.md (Interpreter instructions and its subsections) · code: NdbInterpretedCode.hpp

Example · an increment in one round trip

Interpreted increment: one operation instead of read, compute and write back

// NdbInterpretedCode code(table);
code.read_attr(REG1, counter_col);   // R1 = counter
code.load_const_u32(REG2, 1);         // R2 = 1
code.add_reg(REG1, REG1, REG2);    // R1 += R2
code.write_attr(counter_col, REG1);  // counter = R1
code.interpret_exit_ok();
code.finalise();
// sent with updateTuple() as OO_INTERPRETED
  • Runs under the row's exclusive lock, inside the update: no race between read and write.
  • One round trip instead of two, and no lock held across the network.
  • Rondis builds commands such as SETRANGE this way.

code: include/ndbapi/NdbInterpretedCode.hpp (read_attr, load_const_u32, add_reg, write_attr, interpret_exit_ok) · docs: rondb_generic_interpreter.md (SETRANGE example from Rondis) · the program is an illustration

PART 3

Pushed joins and aggregates

Whole query trees executed inside the data nodes.

Pushed joins · DBSPJ

Pushed joins: each row of one table drives lookups in the next

MySQL / APIsends one query tree DBSPJtc thread scan t110 000 rows t2 by join key30 000 rows t3 by keyone per t2 row joined rows to the API

The MySQL server plans the join, then pushes the whole tree to DBSPJ. The first table is scanned in parallel over its partitions.

Each t1 row's join key starts a lookup or scan in t2, pruned to one partition when the key allows, all in the data nodes.

And so on down the tree. In one test a query fell from 1.2 s to 0.2 s with pushdown, and to 50 ms with 4 ldm threads.

docs: rondb_parallel_query.md (Pushdown joins: the 10 000 and 30 000 rows and the timings come from there)

Pushed joins · conditions

Pushed join rules: equality joins on identical types, read-only, without BLOBs

Required

  • Join conditions are equalities on columns of exactly the same type.
  • No BLOB columns in the result.
  • Read-only, without locks: no FOR UPDATE, no FOR SHARE.

Checking

  • ndb_join_pushdown is on by default.
  • EXPLAIN shows which tables are pushed; SHOW WARNINGS explains why not.
  • Parts of a query can be pushed even when the whole cannot.

Fully replicated tables, or one node group with Read Backup, let a whole join run inside one data node.

docs: rondb_parallel_query.md (Limitations of pushdown joins, Pushdown of joins and fully replicated tables) · code: ha_ndbcluster.cc (join_pushdown, default true)

Newer · aggregation and CTEs in the data nodes

Since 24.10 aggregates run in the data nodes; since 26.10 joins and CTEs too

ReleasePushed downUsed from
24.10Aggregation on a single table, by an aggregation interpreter: TPC-H Q1 over 20× fasterRonSQL (experimental), NDB API
26.10Joins with aggregation, and CTEs: query nodes LOOKUP, SCAN_FRAG, CTE_SUBTREE, CTE_LOOKUP, CTE_SCANRonSQL
26.10Aggregation of pushed joins from MySQL as wellndb_join_pushdown_aggregate, off by default

An aggregate leaf node sends only results to the API. Module 22 shows RonSQL using it.

docs: new_features_2410.md (Pushdown aggregation) · release_notes_26041.md (Pushdown Join Aggregation) · code: signaldata/QueryTree.hpp (QN_* types, NI_AGGREGATE) · ha_ndbcluster.cc (join_pushdown_aggregate, default false)

Recap · module 10

What to take away

  • A hint puts the TC next to the data; rows return from the ldm thread directly.
  • Scans fan out to every partition, unless pruned by the partition key.
  • At most 16 rows per signal, batches of rows with flow control from the reader.
  • The interpreter runs filters and updates on each row: 8 registers, 64 KB memory.
  • DBSPJ runs joins, and in RonSQL aggregates and CTEs, inside the data nodes.

Next · 11 · The RonDB thread model

RonDB training · 25 modules · you are at 10

Next · 11 · Thread model