Welcome to LumoSQL Benchmarking

Welcome to LumoSQL's graphical benchmark data presentation, which summarises thousands of SQLite and LumoSQL benchmarking runs.

We are experimenting with R, so feel free to review our R server and R ui code as we develop our data model.

At the bottom of this page is information about how you can get the data for yourself, or generate your own data sets.

Each data point represents the duration of one benchmark run and the line connecting the points indicates the benchmarks were run on the same computer. The legend consists of : backend version, cpu-info, disk-info, os-version and byte order. Hover above the data points to see the measured time and run information.

Contrast demo above...

Replicating benchmarking results from our data

There are 4 minimum requirements for you to display the data we have collected from our benchmark runs:

Schema of benchmarking data

There are two tables: run_data for information about the setup of a particular run (hardware, versions of software being tested, etc) and test_data, for the timing or other results from the run.

Every benchmarking run in run_data has an SHA3 runid allocated, stored as (run_id, key, value) tuples.

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);
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);

Trying it out

Here are some commands to get a feel for benchmark-filter.tcl:

tclsh benchmark-filter.tcl -help
# The following command tells us there are 8140 tests in this data file
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -count
# This one shows the test result from the most recent 20 runs, with 20
# rows of data, one test timing per column
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -summary -column test
# We can select for hardware types, for example two types of autodetected disk:
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -column test -disk %ramdisk%
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -column test -disk %wdc%
# Or for example an autodetected processor:
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -column test -cpu %ryzen%
# The same, but showing one column per run, one row per test, with more
# details about runs, but also getting unreadable if showing more runs)
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -cpu %ryzen%
# This compares SQLite against other versions of itself on a particular
# hardware combination (-no-backend means only to show an unmodified SQLite,
# as opposed to one with an alternative storage backend provided)
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -column test -no-backend -datasize 1 -cpu %ryzen% -disk '%ssd%'
# This compares one version of SQLite with all versions of LMDB on the same
# hardware combination (-no-backend shows unmodified SQLite, and
# -backend lmdb adds to that runs with LMDB as alternative storage backend):
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -column test -version 3.37.2 -datasize 1 -no-backend -backend lmdb \
      -cpu %ryzen% -disk '%ssd%'
# This compares all versions of SQLite with one version of LMDB again on
# the same hardware combination:
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -column test -datasize 1 -backend lmdb-0.9.29 -no-backend \
      -cpu %ryzen% -disk %ssd% -limit 0
# This compares the last 200 benchmarks we ran, _unreadably_:
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -ignore-numbers -summary -limit 200
# same, but swapping rows and columns for an easier to read output:
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -ignore-numbers -summary -limit 200 -column test
# And finally, for humour value, this compares native SQLite with the copy
# of SQLite 3.18.2 included as "SQL option" in BDB 18.1.32 (we filter by
# -version 3.18.2 which selects both the native and the modified SQLite):
tclsh benchmark-filter.tcl -db all-lumosql-benchmark-data-combined.sqlite \
      -version 3.18.2 -quick

DATASIZE=r,w option and implications

These are some of the more interesting results.

Most tests include a number of SQL statements executed in sequence; DATASIZE=r,w allows to multiply the number of statements which read data by r and the number of statement which write or update data by w (if r = w, we use DATASIZE=r as an abbreviation, and this is how it is displayed by benchmark-filter.tcl)

Note: when results include different data sizes, the test names will differ so it refuses to show them all in one combined output; however the -ignore-numbers option replaces all numbers in the test names by a single #, so they can be shown side by side.

(show some examples of comparing for example 1,2 2,1 and 2 and comment on this, using -ignore-numbers )

Replicating results ab initio

We expect quite a few of you might want to see these results for yourself, on your systems. Our install instructions are fairly well tested, after which you should be able to continue to the next section, Quickstart Build and Benchmarking. This is about generating benchmark run data for your system. You will then be able to run all the benchmark-filter.tcl commands listed earlier in this document on your benchmarks.sqlite file.

Refining your benchmarking

Limitations

What’s next