Lumosql

Documentation
Login

Documentation

Displaying/processing benchmark results

The LumoSQL project runs benchmarks on different versions of SQLite optionally combined with third party storage backends. The results of these benchmarks are kept in a SQLite database, by default benchmarks.sqlite.benchmark-filter is a TCL script with reporting and update features, including the ability to extend the schema. An example would be a field to identify who ran the benchmarks. Benchmarks can be exported and imported.

Assuming benchmarks.sqlite exists, call the tool with:

tclsh tool/benchmark-filter.tcl OPTIONS

Here is a simple example, with the default limit being the first 20 lines:

tclsh tool/benchmark-filter.tcl -list -fields ID:8,TARGET,DURATION
ID        TARGET                 DURATION
B70E9DE8  3.53.2                   12.292
416B6EAB  3.43.0                   11.440
6CA8B349  3.43.0+lmdb-0.9.35        5.829
D414B582  3.45.0                   12.429
89819885  3.45.0+lmdb-0.9.35        5.613
280983F9  3.50.0                   10.691
C1801DA5  3.50.0+lmdb-0.9.35        4.882
632A43FD  3.53.2+lmdb-0.9.35        5.996
FCDD0A85  3.53.2+lmdbv1-1.0         5.780

Run IDs can be abbreviated to any unambiguous prefix from the hash. To display selected tests from one run:

tclsh tool/benchmark-filter.tcl -summary -tests 2,total B70E9DE8
    Benchmark: sqlite 3.53.2
       Target: 3.53.2
              (3.53.2 2026-06-03 19:12:13 d6e03d8c777cfa2d35e3b60d8ec3e0187f3e9f99d8e2ee9cac695fd6fcdfalt1 (64-bit))
       Ran at: 2026-06-21 07:30:38
     Duration: 12.292
    Disk time: read: 0.577; write: 3.235

       TIME TEST NAME
      9.257    2 1000 INSERTs
------------
     12.292 (total benchmark run time)

Multiple IDs produce side-by-side results:

tclsh tool/benchmark-filter.tcl -quick -tests 2,total B70E9DE8 632A43FD FCDD0A85
     3.53.2      3.53.2      3.53.2 (sqlite version)
                   lmdb      lmdbv1 (backend name)
                 0.9.35         1.0 (backend version)
--------------- TIME -------------- TEST NAME
      9.257       2.578       2.475    2 1000 INSERTs
------------------------------------
     12.292       5.996       5.780 (total benchmark run time)

The same runs can be selected by their properties:

tclsh tool/benchmark-filter.tcl -quick -tests 2,total -version 3.53.2

To compare all LMDB runs:

tclsh tool/benchmark-filter.tcl -quick -tests 2,total -backend lmdb
     3.43.0      3.45.0      3.50.0      3.53.2 (sqlite version)
       lmdb        lmdb        lmdb        lmdb (backend name)
     0.9.35      0.9.35      0.9.35      0.9.35 (backend version)
--------------------- TIME -------------------- TEST NAME
      2.440       2.746       1.600       2.578    2 1000 INSERTs
------------------------------------------------
      5.829       5.613       4.882       5.996 (total benchmark run time)

For many runs, put tests in columns and select only those of interest:

tclsh tool/benchmark-filter.tcl -quick -column benchmark \
    -tests 2,8,total -backend lmdb
Column 2: 1000 INSERTs
Column 8: 5000 SELECTs with an index
Column T: Total run duration

    2     8     T Target
2.440 0.074 5.829 3.43.0+lmdb-0.9.35
2.746 0.094 5.613 3.45.0+lmdb-0.9.35
1.600 0.099 4.882 3.50.0+lmdb-0.9.35
2.578 0.106 5.996 3.53.2+lmdb-0.9.35

Full set of options

The tool accepts loads of options:

environment

selecting runs

If more than one selection option is provided, the tool will select runs which match all the criteria; however if the same option is repeated, it selects any which match: so for example -version N -version X -backend B selects all runs with backend B which also used saqlite version N or X.

output format

More than one output format option can appear in the command line, and they all apply, unless specified otherwise

If no output format options are specified, the default is -list if there are no specific run selection criteria, -summary if there are any criteria. The aggregating modes (-stats, -average, -compare) and the explicit output modes (-list, -summary, -quick, -details, -count, -export, -copy) override this default.

When -stats, -average or -compare is in effect and the user has not explicitly given -limit, the row limit defaults to 0 (no limit), since truncating an aggregate over the first 20 runs would normally produce a useless result. -list and -quick default to 20.

list columns

The following entries are valid values for the -fields option, selecting which columns are displayed:

Worked example: comparing two groups

Given a database containing both vanilla SQLite and SQLite+LMDB runs, produce a per-test comparison with a speed-up ratio:

tclsh tool/benchmark-filter.tcl -db benchmarks.sqlite \
    -compare -A -no-backend -B -backend lmdb 2>/dev/null | head -n 5
TEST_NUM  TEST                                                             A_AVG_S  B_AVG_S  SPEEDUP
--------  ---------------------------------------------------------------  -------  -------  -------
       1  Creating database and tables                                       0.128    0.099    1.29x
       2  1000 INSERTs                                                       8.448    2.341    3.61x
       3  100 UPDATEs without an index, upgrading a read-only transaction    0.144    0.114    1.27x

The same query as TSV, suitable for eg piping into column -ts$'\t':

tclsh tool/benchmark-filter.tcl -db benchmarks.sqlite -format tsv \
    -compare -A -no-backend -B -backend lmdb

The grouping key for -stats and -average can also be changed; for example, to see the per-LMDB-version trend collapsed across SQLite versions:

tclsh tool/benchmark-filter.tcl -db benchmarks.sqlite \
    -stats -group-by backend-version

extra actions

Merging/combining databases

benchmark-filter.tcl does not merge two .sqlite files, but the same effect can be achieved with export/import:

tclsh tool/benchmark-filter.tcl -db db1.sqlite -export dump1.txt
tclsh tool/benchmark-filter.tcl -db db2.sqlite -export dump2.txt
tclsh tool/benchmark-filter.tcl -import dump1.txt dump2.txt -export combined.txt

checking test results

When running tests (as opposed to benchmarks) the build tool will store the results in a different database and the useful data could be different; one can get a summary of test results with:

tool/benchmark-filter.tcl -database tests.sqlite -list -fields TARGET,DONE,OK,INTR,FAIL

or have complete information about targets with failed tests:

tool/benchmark-filter.tcl -database tests.sqlite -details -failed

Failed steps: diagnosing and cleaning

A failed benchmark step shows up in benchmark-filter output as ERR1 (or similar) in the status column. The runner persists the sqlite3 shell's stderr text and exit code for such steps under the error-text and error-exit-code keys on the relevant test_data row. These are populated only on failure.

Run the following two queries against any benchmark database before treating its totals as performance evidence.

List every failed step with the SQL error and the target it failed under:

SELECT t.run_id,
       (SELECT value FROM run_data
         WHERE run_id = t.run_id AND key = 'target')         AS target,
       t.test_number,
       (SELECT value FROM test_data t2
         WHERE t2.run_id = t.run_id
           AND t2.test_number = t.test_number
           AND t2.key = 'test-name')                         AS test_name,
       (SELECT value FROM test_data t2
         WHERE t2.run_id = t.run_id
           AND t2.test_number = t.test_number
           AND t2.key = 'status')                            AS status,
       t.value                                               AS error_text
  FROM test_data t
 WHERE t.key = 'error-text';

List any step whose status is not OK. This is broader than the query above: it also catches runs killed by signal (SIGHUP, etc.) which have no error-text because the shell never got to print one:

SELECT t.run_id,
       (SELECT value FROM run_data
         WHERE run_id = t.run_id AND key = 'target')         AS target,
       t.test_number,
       (SELECT value FROM test_data t2
         WHERE t2.run_id = t.run_id
           AND t2.test_number = t.test_number
           AND t2.key = 'test-name')                         AS test_name,
       t.value                                               AS status
  FROM test_data t
 WHERE t.key = 'status' AND t.value != 'OK';

A run with a missing step in the middle of speedtest1 has a total that no longer means what it appears to mean. Either fix the underlying cause and re-run, or drop the affected runs entirely:

BEGIN;
CREATE TEMP TABLE bad_runs AS
  SELECT DISTINCT run_id FROM test_data
  WHERE key='status' AND value != 'OK';
DELETE FROM test_data WHERE run_id IN (SELECT run_id FROM bad_runs);
DELETE FROM run_data WHERE run_id IN (SELECT run_id FROM bad_runs);
DROP TABLE bad_runs;
COMMIT;
VACUUM;

tool/benchmark-filter.tcl -delete is the filter-driven equivalent when the selection criteria are expressible as filter options.