Run in a container, connect to the Benchto database running in another container and write to report.html in the current directory:

docker run -it --rm \
  -e DB_URL=postgresql+psycopg2://postgres@$(docker inspect -f '{{.NetworkSettings.IPAddress}}' benchto-postgres):5432/benchto \
  -e PGPASSWORD \
  -v $(pwd)/out:/out \
  benchmark-reports:latest

Since the output directory can get rather big, it's best to compress it using the Zstandard method. Using GNU TAR:

ZST_CLEVEL=10 tar --zstd -cvf out.tar.zst out

Using BSD TAR (on MacOS):

tar --zstd --options zstd:compression-level=10 -cvf out.tar.zst out

To run it without a container, set up the Python virtual environment and install dependencies. This is only required once:

python3 -mvenv venv
source venv/bin/activate
python -m pip install -r requirements.txt

Then, when you want to generate a report:

source venv/bin/activate
./report.py \
    --sql sql \
    --db-url postgresql+psycopg2://postgres@localhost:5432/benchto \
    --output report.html
open report.html

Note: don't specify the password in the DB URL option. Instead, configure it in the ~/.pgpass file.

To generate the report only for selected Benchto environments, specify their names in the --environments option. To select all environments for a particular Trino version, use SQL wildcards, for example: -e '%393%,%394%'.

Reports are generated by executing queries from plain-text files. They're self-contained and do not require any additional configuration. This requires the following conventions:

1. First line should be a comment and will be treated as the report title.
2. If there's no comment, the query is still executed, but its results will not be included in the report. There can be setup queries that create views and functions used by other queries.
3. If there are more comment lines, they're treated as the report description.
4. Column names must be in snake_case.

There are three kinds of columns recognized:

1. Metrics - their column name must end with the following suffix: num2f, num, pct, unit, err;
2. Labels - must end with _label. Note that their values are not formatted, so any required formatting should be applied in the query itself;
3. Dimensions - columns without any of the suffixes mentioned above.

Metric columns are always aligned right and formatted according the their suffix:

• num doesn't apply any formatting, can be used for integer numbers,
• num2f formats the value as a decimal, rounded 2 decimal places,
• pct formats the value as a percent (rounded to 2 decimal places).

If the suffix is unit, there should be a dimension columns named unit and the metric is formatted according to it's value. There's a separate chart created for every unit present in the results. Recognized units are:

• MILLISECONDS
• BYTES
• PERCENT
• QUERY_PER_SECOND

If the suffix is err, there should be a matching column with the same prefix, which will have an error value marked on its bar. For example, there could be two columns named mean_unit and mean_err.

Column labels are generated by changing snake_case into capitalized words and dropping the suffix.

Label columns contained additional information and are not dispalyed in bar charts, only in tables.

Dimension columns uniquely identify a single metric value, so on bar charts they're joined with a comma. If a dimension column name ends with _pivot, it'll be used to additionally group metrics on bar charts. If it ends with _group, there will be a separate chart for every combination of values of such group columns.

To refresh the list of dependencies:

pip-compile requirements.in

To run tests:

./report.py -t

If you see a failure in pipelines and the diff from the assert is not informative enough, download the artifact from the pipeline and compare the actual.html with testdata/expected.html. If all changes are expected, you can then overwrite testdata/expected.html with actual.html.

To make a new backup of the Benchto database to use in tests:

pg_dump -f testdata/backup.dump -Fc 'postgres://postgres@localhost/benchto?sslmode=disable'

Size of the backup is mostly determined by the query_info table:

pg:postgres@localhost/benchto=> \dt+
                              List of relations
┌────────┬────────────────────────────┬───────┬───────┬────────────┬─────────┐
│ Schema │            Name            │ Type  │ Rows  │    Size    │ Comment │
├────────┼────────────────────────────┼───────┼───────┼────────────┼─────────┤
│ public │ benchmark_run_measurements │ table │     0 │ 0 bytes    │         │
│ public │ benchmark_runs             │ table │   255 │ 144 kB     │         │
│ public │ benchmark_runs_attributes  │ table │  2074 │ 168 kB     │         │
│ public │ benchmark_runs_variables   │ table │  3516 │ 240 kB     │         │
│ public │ environment_attributes     │ table │     0 │ 528 kB     │         │
│ public │ environments               │ table │     0 │ 8192 bytes │         │
│ public │ execution_attributes       │ table │  1270 │ 152 kB     │         │
│ public │ execution_measurements     │ table │ 18438 │ 832 kB     │         │
│ public │ executions                 │ table │  1320 │ 160 kB     │         │
│ public │ measurements               │ table │ 18442 │ 1608 kB    │         │
│ public │ query_info                 │ table │  1270 │ 110 MB     │         │
│ public │ schema_version             │ table │     0 │ 16 kB      │         │
│ public │ tags                       │ table │     0 │ 16 kB      │         │
└────────┴────────────────────────────┴───────┴───────┴────────────┴─────────┘
(13 rows)

To restore it:

pg_restore -d 'postgres://postgres@localhost?sslmode=disable' --create --exit-on-error testdata/backup.dump