This directory contains a collection of benchmarks each driven by a simple ‘.benchmark’ text file and sql queries that exercise the DataFusion execution engine against a variety of benchmark suites. The sql benchmark framework is intentionally simple so that benchmarks and queries can be added or modified without touching the core engine or requiring recompilation.
The sql benchmarks are organized in sub‑directories that correspond to the benchmark suites that are commonly used in the community:
| Benchmark Suite | Description |
|---|---|
clickbench | ClickBench benchmark |
clickbench extended | 12 additional, more complex queries against the Clickbench dataset |
clickbench_sorted | ClickBench benchmark using a pre-sorted hits file. |
h2o | The h2o benchmark |
hj | Hash join benchmark |
imdb | IMDb benchmark |
nlj | Nested‑loop join benchmark |
smj | Sort‑merge join benchmark |
sort tpch | Sorting benchmarks against the TPC-H lineitem table |
taxi | NYC taxi dataset benchmark |
tpcds | TPC‑DS queries |
tpch | TPC‑H queries |
wide_schema | Small-projection queries on a wide (1024-col, 256-file) synthetic dataset; runs wide + narrow subgroups for comparison |
The easiest way to run a benchmark is to use the bench.sh shell script (up one level from this document) as it takes care of configuring any required environment variables and can populate any required data files. However, it is possible to directly run a sql benchmark using the cargo bench command. For example:
BENCH_NAME=tpch cargo bench --bench sql
Sql benchmarks are configured via environment variables. Cargo's bench command and criterion (the underlying benchmark framework) have an unfortunate limitation in that custom command arguments cannot be passed into a benchmark. The alternative is to use environment variables to pass in arguments which is what is used here.
The SQL benchmarking tool uses the following environment variables:
| Environment Variable | Description |
|---|---|
| BENCH_NAME | The name of the benchmark suite to run. For example ‘imdb’. This should correspond to a directory name in the sql_benchmarks directory. |
| BENCH_SUBGROUP | The subgroup with the benchmark suite to run. For example ‘window’ to run the window subgroup of the h2o benchmark. |
| BENCH_QUERY | A query number to run. |
| BENCH_PERSIST_RESULTS | true/false to persist benchmark results. Results will be persisted in csv format so be cognizant of the size of the results. |
| BENCH_VALIDATE | true/false to validate benchmark results against persisted results or result_query's. If both BENCH_PERSIST_RESULTS and BENCH_VALIDATE are true, persist mode runs and validation is skipped. |
| DATA_DIR | Root directory for benchmark data loaded by SQL benchmark files. When unset, uses data (relative to the benchmarks/ directory). |
| SIMULATE_LATENCY | Simulate object store latency to mimic remote storage (e.g. S3). Adds random latency in the range 20-200ms to each object store operation. |
| MEM_POOL_TYPE | The memory pool type to use, should be one of “fair” or “greedy”. |
| MEMORY_LIMIT | Memory limit (e.g. ‘100M’, ‘1.5G’). If not specified, run all pre-defined memory limits for given query if there's any, otherwise run with no memory limit. |
Example – Run the H2O window benchmarks on the ‘small’ sized CSV data files:
BENCH_NAME=h2o BENCH_SUBGROUP=window H2O_BENCH_SIZE=small H20_FILE_TYPE=csv cargo bench --bench sql
Some benchmarks use custom environment variables as outlined below:
| Name | Description | Default value |
|---|---|---|
| BENCH_SIZE | Used in the tpch, sort-tpch and tpcds benchmarks. The size corresponds to the scale factor. | 1 |
| TPCH_FILE_TYPE | Used in the tpch benchmark to specify which file type to query against. The valid options are csv, parquet and mem | parquet |
| H2O_FILE_TYPE | Used in the h2o benchmark to specify which file type to query against. The valid options are csv and parquet | csv |
| CLICKBENCH_TYPE | The type of partitioning for the clickbench benchmark. Valid options are single and partitioned | single |
| H2O_BENCH_SIZE | Used in the h2o benchmark. The valid options are small, medium and big | small |
| PREFER_HASH_JOIN | Control datafusion's config option datafusion.optimizer.prefer_hash_join | true |
| HASH_JOIN_BUFFERING_CAPACITY | Control datafusion's config option datafusion.execution.hash_join_buffering_capacity | 0 |
| BENCH_SORTED | Used in the sort_tpch benchmark to indicate whether the lineitem table should be sorted. | false |
| SORTED_BY | Used in the clickbench_sorted benchmark to indicate the column to sort by. | EventTime |
| SORTED_ORDER | Used in the clickbench_sorted benchmark to indicate the sort order of the column. | ASC |
SQL benchmarks are run via cargo's bench command using criterion for running and gathering statistics of each sql being benchmarked.
Each individual benchmark is represented by a <name>.benchmark file that contains a number of directives instructing the tool on how to load data, run initializations, run assertions, run the benchmark, optionally persist and validate results, and finally run any cleanup if required.
Variables are supported in two forms:
true (case-insensitive) selects the true branch; any other set value selects the false branch. If ENV_VAR is unset, the valud of default is used toComments in files are supported with lines starting with # or --.
Many if not most of the benchmarks are set up using templates to reduce duplication across the .benchmark files. For example here is one of the benchmark files for the h2o benchmark suite:
subgroup groupby template sql_benchmarks/h2o/h2o.benchmark.template QUERY_NUMBER=1 QUERY_NUMBER_PADDED=01
The template directive above defines the subgroup the benchmark is part of, sets two variables (QUERY_NUMBER and QUERY_NUMBER_PADDED) and points to a file containing more directives that are shared across the benchmark suite.
load sql_benchmarks/h2o/init/load_${BENCH_SUBGROUP:-groupby}_${BENCH_SIZE:-small}_${BENCH_FILE_TYPE:-csv}.sql
name Q${QUERY_NUMBER_PADDED}
group h2o
run sql_benchmarks/h2o/queries/${BENCH_SUBGROUP:-groupby}/q${QUERY_NUMBER_PADDED}.sql
result sql_benchmarks/h2o/results/${BENCH_SUBGROUP:-groupby}/${BENCH_SIZE:-small}/q${QUERY_NUMBER_PADDED}.csv
The above showcases the use of defaults for variables: ${NAME:-default}
The name of the benchmark. This will be used as part of the display name used by criterion.
Example:
The name directive also makes the value available to benchmark-file replacements as BENCH_NAME. This is separate from the BENCH_NAME environment variable used to select which benchmark group to run.
The group name of the benchmark used for grouping benchmarks together.
Example:
The sub group name of the benchmark used for filtering to a specific sub group.
Example:
The load directive called during initialization of the benchmark. If a path to a file is provided on the same line as the load directive that path will be parsed and any sql statements in that file will be executed during initialization. If no path is specified the next line is required to be the sql statement to execute.
The load directive (including any following sql statement) must be followed by a blank line.
Example:
The init directive is called after the load directive prior to benchmark execution. If a path to a file is provided on the same line as the init directive that path will be parsed and any sql statements in that file will be executed during the benchmark initialization. If no path is specified the next line is required to be the sql statement to execute.
The init directive (including any following sql statement) must be followed by a blank line.
Example:
The run directive called during execution of the benchmark. If a path to a file is provided on the same line as the run directive that path will be parsed and any sql statements in that file will be executed during the benchmark run. If no path is specified the next line is required to be the sql statement to execute.
Multiple statements are allowed within a single run directive, however a benchmark file may contain only one run directive. When running with BENCH_PERSIST_RESULTS or BENCH_VALIDATE, only the last SELECT or WITH statement from that run directive will be used for comparison.
The run directive (including any following sql statement) must be followed by a blank line.
Example:
The cleanup directive is called after all other directives and can be used to cleanup after the benchmark - e.g. to drop tables. If a path to a file is provided on the same line as the cleanup directive that path will be parsed and any sql statements in that file will be executed during cleanup. If no path is specified the next line is required to be the sql statement to execute.
The cleanup directive (including any following sql statement) must be followed by a blank line.
Example:
The expect_plan directive will check the physical plan for the string provided on the same line. This can be used to validate that a particular join was used.
Example:
The assert directive is run between the init and run directives and can be used to validate system state correctness prior to running the benchmark sql. The format is
The number of I's corresponds to the number of columns in the result. The expected results can be either tab delimited or pipe delimited.
The result_query directive is run during the verify phase and can be used to verify a different set of results than any that might come from queries executed from the run directive. The format is the same as the assert directive above.
Example:
Note that the results of the run query are not automatically stored into a table in datafusion. If you want to verify a result from queries executed from the run directive those queries will have to be saved to a table directly using CREATE TABLE AS (..) or similar.
The result directive declares the expected result file used during verification. A path to a file is required on the same line as the result directive. The file is parsed only during verification, and must be a pipe-delimited CSV file with a header row. During verification, these expected rows are compared with the rows produced by the last saved SELECT or WITH statement from the run directive.
Example:
The template directive allows for inclusion of another file in a benchmark file. A path to a file is required on the same line as the template directive which will be parsed as a benchmark file. Parameters can be passed to the template file using the format KEY=value, one per line after the template directive followed by a blank line.
Example:
The echo directive allows for echoing a string to stdout during the execution of the benchmark and may be useful for debugging.
Example:
If you want to add a new query:
<name>.benchmark for each individual benchmark.