跳到主要内容

How to Benchmark With Hyperfine

Hyperfine is a cross-platform command-line benchmarking tool, that supports warm-up and parameterized benchmarks.

Databend recommends using hyperfine to perform benchmarking via the ClickHouse/MySQL client. In this article, we will use the MySQL client to introduce it.

Before you begin

Design SQL for benchmark

Design benchmarks based on your dataset and key SQLs, write them to a file.

Some SQLs for stateless computing benchmarks are listed below. Save them to a file called bench.sql:

SELECT avg(number) FROM numbers_mt(100000000000)
SELECT sum(number) FROM numbers_mt(100000000000)
SELECT min(number) FROM numbers_mt(100000000000)
SELECT max(number) FROM numbers_mt(100000000000)
SELECT count(number) FROM numbers_mt(100000000000)
SELECT sum(number+number+number) FROM numbers_mt(100000000000)
SELECT sum(number) / count(number) FROM numbers_mt(100000000000)
SELECT sum(number) / count(number), max(number), min(number) FROM numbers_mt(100000000000)
SELECT number FROM numbers_mt(10000000000) ORDER BY number DESC LIMIT 10
SELECT max(number), sum(number) FROM numbers_mt(1000000000) GROUP BY number % 3, number % 4, number % 5 LIMIT 10

Write an easy-to-use script

Open a file called benchmark.sh and write the following:

#!/bin/bash

WARMUP=3
RUN=10

export script="hyperfine -w $WARMUP -r $RUN"

script=""
function run() {
port=$1
sql=$2
result=$3
script="hyperfine -w $WARMUP -r $RUN"
while read SQL; do
n="-n \"$SQL\" "
s="echo \"$SQL\" | mysql -h127.0.0.1 -P$port -uroot -s"
script="$script '$n' '$s'"
done <<< $(cat $sql)

script="$script --export-markdown $result"
echo $script | bash -x
}


run "$1" "$2" "$3"

In this script:

  • Use the -w/--warmup & WARMUP to perform 3 program executions before the actual benchmarking.
  • And use -r/--runs & RUN to execute 10 benchmarking runs.
  • Allows to specify MySQL compatible service ports for Databend.
  • Need to Specify the input SQL file and the output Markdown file.

The usage is shown below. For executable, run chmod a+x ./benchmark.sh first.

./benchmark.sh <port> <sql> <result>

Execute and review benchmark results

In this example, the MySQL compatible port is 3307, benchmark SQLs file is bench.sql, and expected output is databend-hyperfine.md.

Run ./benchmark.sh 3307 bench.sql databend-hyperfine.md. Of course, if you deploy in your own configuration, you can adjust it to suit.

:::Note The following results were benchmarked with AMD Ryzen 9 5900HS and 16GB of RAM, for example only. :::

The output in the terminal is shown in the following example.

Benchmark 1:  "SELECT avg(number) FROM numbers_mt(100000000000)"
Time (mean ± σ): 3.486 s ± 0.016 s [User: 0.003 s, System: 0.002 s]
Range (min … max): 3.459 s … 3.506 s 10 runs

The final result in databend-hyperfine.md is as follows.

CommandMean [s]Min [s]Max [s]Relative
"SELECT avg(number) FROM numbers_mt(100000000000)"3.524 ± 0.0253.4973.5672.94 ± 0.06
"SELECT sum(number) FROM numbers_mt(100000000000)"3.531 ± 0.0243.4943.5742.94 ± 0.06
"SELECT min(number) FROM numbers_mt(100000000000)"5.970 ± 0.0435.9256.0834.98 ± 0.09
"SELECT max(number) FROM numbers_mt(100000000000)"6.201 ± 0.1376.0256.5355.17 ± 0.15
"SELECT count(number) FROM numbers_mt(100000000000)"2.368 ± 0.0502.3342.4991.97 ± 0.05
"SELECT sum(number+number+number) FROM numbers_mt(100000000000)"17.406 ± 0.83016.37518.47414.51 ± 0.74
"SELECT sum(number) / count(number) FROM numbers_mt(100000000000)"3.580 ± 0.0183.5563.6212.98 ± 0.05
"SELECT sum(number) / count(number), max(number), min(number) FROM numbers_mt(100000000000)"10.391 ± 0.11310.16710.5278.66 ± 0.18
"SELECT number FROM numbers_mt(10000000000) ORDER BY number DESC LIMIT 10"2.175 ± 0.0222.1552.2161.81 ± 0.04
"SELECT max(number), sum(number) FROM numbers_mt(1000000000) GROUP BY number % 3, number % 4, number % 5 LIMIT 10"1.199 ± 0.0211.1641.2471.00

Follow up