Lumosql

LumoSQL benchmark result database schema
Login

LumoSQL benchmark result database schema

This document describes the schema of the SQLite databases produced by make benchmarks-...sqlite (i.e. the files generated by tool/build.tcl when invoked with the database, benchmark or test operation).

The schema is created in tool/build.tcl and has two narrow key/value tables.

run_data - one row per (run, key)

CREATE TABLE run_data (
    run_id VARCHAR(128),
    key    VARCHAR(256),
    value  TEXT
);
CREATE UNIQUE INDEX run_data_index ON run_data (run_id, key);

Each run of the benchmark suite (one full pass over all individual benchmarks for one target) gets a unique run_id (a hex hash). Per-run scalar facts are stored as (key, value) rows for example target string, when it ran, host metadata, build options, end-of-run counters, etc.

test_data - one row per (run, test, key)

CREATE TABLE test_data (
    run_id      VARCHAR(128),
    test_number INTEGER,
    key         VARCHAR(256),
    value       TEXT
);
CREATE UNIQUE INDEX test_data_index_1
    ON test_data (run_id, test_number, key);
CREATE UNIQUE INDEX test_data_index_2
    ON test_data (run_id, key, test_number);

Within each run, individual benchmarks (the speedtest1-style steps such as "1000 INSERTs", "5000 SELECTs with an index", etc.) are numbered sequentially in test_number. Per-test scalar facts (timings, status, output size, the test name itself) are stored as (key, value) rows.

run_data keys

These are the keys that tool/build.tcl actually emits. All values are stored as TEXT; the "Type" column below records what is conceptually inside the string.

Identity / target

Key Type Description
target string Build target string, e.g. 3.53.0, 3.53.0+lmdb-0.9.33
title string Human-readable title of the benchmark configuration

Build / source provenance

Key Type Description
sqlite-name string Upstream package name for SQLite
sqlite-version string SQLite version used to build the binary
sqlite-id string VCS commit ID of the SQLite source
sqlite-date string Date associated with the SQLite source revision
backend string Backend name (e.g. lmdb, empty for vanilla SQLite)
backend-name string Upstream package name for the backend
backend-version string Backend version
backend-id string VCS commit ID of the backend source
backend-date string Date associated with the backend source revision
notforking-id string not-forking version that prepared the sources
notforking-date string not-forking commit date

Build options (one row per option)

All option keys are stored with the prefix option-. Values are whatever the user (or the default) set them to.

Key Description
option-datasize data-size multiplier (1 is the default)
option-debug on / off
option-discard_output on / off
option-lmdb_debug on / off
option-lmdb_fixed_rowid on / off
option-lmdb_transaction e.g. optimistic
option-rowsum on / off
option-rowsum_algorithm e.g. siphash_64 (default), blake3_256, sha3_256
option-sqlite3_journal e.g. default

This list is not closed: any option declared in Makefile.options will appear here as option-<name>.

Host environment

Key Type Description
cpu-type string CPU identifier from tool/hardware-detect.tcl
cpu-comment string Free-form CPU description (CPU_COMMENT in Makefile)
disk-comment string Free-form disk description (DISK_COMMENT)
os-type string OS family (e.g. Linux)
os-version string OS version string
byte-order string LE or BE
word-size int typically 64 or 32

Timing / counters

Key Type Description
when-run epoch s Wall-clock timestamp when the run started (Unix seconds, integer)
end-run epoch s Wall-clock timestamp when the run finished
disk-read-time seconds Real time spent in disk reads, summed across the run (float seconds)
disk-write-time seconds Real time spent in disk writes, summed across the run (float seconds)
tests-ok int Number of individual tests that completed successfully
tests-fail int Number of individual tests that failed
tests-intr int Number of individual tests that were interrupted

The "duration" of a run can be computed in two ways:

test_data keys

Key Type Description
test-name string Human-readable name of the individual benchmark step
real-time seconds Wall-clock time for this test step
user-cpu-time seconds User CPU time for this test step
system-cpu-time seconds System CPU time for this test step
output-size bytes Size of any captured stdout/stderr from the test step
status string Result status (ok, fail, intr, crash, ...)

Useful queries

Per-run wall-clock duration (ms-resolution)

SELECT t.run_id,
       (SELECT value FROM run_data
          WHERE run_id = t.run_id AND key = 'target') AS target,
       SUM(CAST(t.value AS REAL)) AS run_secs
FROM test_data t
WHERE t.key = 'real-time'
GROUP BY t.run_id;

Per-target average over all repeats

WITH per_run AS (
  SELECT t.run_id,
         (SELECT value FROM run_data
            WHERE run_id = t.run_id AND key = 'target') AS target,
         SUM(CAST(t.value AS REAL)) AS run_secs
  FROM test_data t WHERE t.key = 'real-time'
  GROUP BY t.run_id
)
SELECT target, COUNT(*) AS runs, AVG(run_secs) AS avg_s
FROM per_run GROUP BY target ORDER BY target;

Per-test view (test-name + run target denormalised)

After creating this view, comparisons across targets and tests become ordinary SQL.

CREATE TEMP VIEW test_results AS
  SELECT t.run_id, t.test_number,
         r.value AS target,
         n.value AS test_name,
         CAST(t.value AS REAL) AS real_time
  FROM test_data t
  JOIN test_data n
    ON n.run_id = t.run_id
   AND n.test_number = t.test_number
   AND n.key = 'test-name'
  JOIN run_data r
    ON r.run_id = t.run_id
   AND r.key = 'target'
  WHERE t.key = 'real-time';

Conventions and caveats