跳转到主要内容

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​