clickhouse-benchmark

Connects to a ClickHouse server and repeatedly sends specified queries. Syntax

$ clickhouse-benchmark --query ["single query"] [keys]

$ echo "single query" | clickhouse-benchmark [keys]

$ clickhouse-benchmark [keys] <<< "single query"

If you want to send a set of queries, create a text file and place each query on the individual string in this file. For example:

SELECT * FROM system.numbers LIMIT 10000000;
SELECT 1;

Then pass this file to a standard input of clickhouse-benchmark:

clickhouse-benchmark [keys] < queries_file;

Command-line options

--query=QUERY — Query to execute. If this parameter is not passed, clickhouse-benchmark will read queries from standard input.
--query_id=ID — Query Id.
--query_id_prefix=ID_PREFIX — Query Id Prefix.
--queries-format=FORMAT — Format of queries read from standard input. Possible values: tsv (default, one tab-escaped query per line) and script (parse the input as a script of multiple queries separated by semicolons). Limitation of script: INSERT ... FORMAT queries must be on a single line.
-c N, --concurrency=N — Number of queries that clickhouse-benchmark sends simultaneously. Default value: 1.
-C N, --max_concurrency=N — Gradually increases number of parallel queries up to specified value, making one report for every concurrency level.
--precise — Enables precise per-interval reporting with weighted metrics.
-d N, --delay=N — Interval in seconds between intermediate reports (to disable reports set 0). Default value: 1.
-h HOST, --host=HOST — Server host. Default value: localhost. For the comparison mode you can use multiple -h keys.
-i N, --iterations=N — Total number of queries. Default value: 0 (repeat forever).
-r, --randomize — Random order of queries execution if there is more than one input query.
--arrival-rate=R — Enables the open-loop mode: queries arrive at random times at the average rate of R queries per second. A comma-separated list of rates, such as --arrival-rate=5,10,20, runs the benchmark for every rate one after another and makes one report per rate; it requires --iterations or --timelimit.
--max-in-flight=N — The maximum number of queries executing at the same time in the open-loop mode. Default value: 256.
--seed=N — Seed of the random generator for the arrival times, the order of queries with --randomize, and the choice of a server in the open-loop mode. The same seed and options reproduce the same run. The arrival times depend only on the seed and the rate, not on the queries or the servers, so different query mixes or servers can be compared under the same arrival schedule. Default: a random seed, printed at the start in the open-loop mode.
--per-query — Prints statistics for every query at the end: the number of queries and errors, QPS, latency percentiles, the maximum latency and the mean CPU time of a query on the server.
-s, --secure — Using TLS connection.
-t N, --timelimit=N — Time limit in seconds. clickhouse-benchmark stops sending queries when the specified time limit is reached. Default value: 0 (time limit disabled).
--port=N — Server port. Default value: 9000. For the comparison mode you can use multiple --port keys.
--confidence=N — Level of confidence for T-test. Possible values: 0 (80%), 1 (90%), 2 (95%), 3 (98%), 4 (99%), 5 (99.5%). Default value: 5. In the comparison mode clickhouse-benchmark performs the Independent two-sample Student’s t-test to determine whether the two distributions aren’t different with the selected level of confidence.
--cumulative — Printing cumulative data instead of data per interval.
--database=DATABASE_NAME — ClickHouse database name. Default value: default.
--user=USERNAME — ClickHouse user name. Default value: default.
--password=PSWD — ClickHouse user password. Default value: empty string.
--stacktrace — Stack traces output. When the key is set, clickhouse-benchmark outputs stack traces of exceptions.
--stage=WORD — Query processing stage at server. ClickHouse stops query processing and returns an answer to clickhouse-benchmark at the specified stage. Possible values: complete, fetch_columns, with_mergeable_state. Default value: complete.
--roundrobin — Instead of comparing queries for different --host/--port just pick one random --host/--port for every query and send query to it.
--reconnect=N — Control reconnection behaviour. Possible values 0 (never reconnect), 1 (reconnect for every query), or N (reconnect after every N queries). Default value: 0.
--max-consecutive-errors=N — Number of allowed consecutive errors. Default value: 0.
--ignore-error,--continue_on_errors — Continue testing even if queries failed.
--client-side-time — Display the time including network communication instead of server-side time; Note that for server versions before 22.8 we always display client-side time.
--proto-caps — Enable/disable chunking in data transfer. choices (can be comma-separated): chunked_optional, notchunked, notchunked_optional, send_chunked, send_chunked_optional, send_notchunked, send_notchunked_optional, recv_chunked, recv_chunked_optional, recv_notchunked, recv_notchunked_optional. Default value: notchunked.
--help — Shows the help message.
--verbose — Increase help message verbosity.

If you want to apply some settings for queries, pass them as a key --<session setting name>= SETTING_VALUE. For example, --max_memory_usage=1048576.

Environment variable options

The user name, password and host can be set via environment variables CLICKHOUSE_USER, CLICKHOUSE_PASSWORD and CLICKHOUSE_HOST.
Command line arguments --user, --password or --host take precedence over environment variables.

Output

By default, clickhouse-benchmark reports for each --delay interval. Example of the report:

Queries executed: 10.

localhost:9000, queries 10, QPS: 6.772, RPS: 67904487.440, MiB/s: 518.070, result RPS: 67721584.984, result MiB/s: 516.675.

000%      0.145 sec.
000%     0.146 sec.
000%     0.146 sec.
000%     0.146 sec.
000%     0.147 sec.
000%     0.148 sec.
000%     0.148 sec.
000%     0.148 sec.
000%     0.149 sec.
000%     0.150 sec.
000%     0.150 sec.
000%     0.150 sec.
900%     0.150 sec.
990%     0.150 sec.

In the report you can find:

Number of queries in the Queries executed: field.
Status string containing (in order):
- Endpoint of ClickHouse server.
- Number of processed queries.
- QPS: How many queries the server performed per second during a period specified in the --delay argument.
- RPS: How many rows the server reads per second during a period specified in the --delay argument.
- MiB/s: How many mebibytes the server reads per second during a period specified in the --delay argument.
- result RPS: How many rows placed by the server to the result of a query per second during a period specified in the --delay argument.
- result MiB/s. How many mebibytes placed by the server to the result of a query per second during a period specified in the --delay argument.
Percentiles of queries execution time.

Open-loop mode

By default clickhouse-benchmark works in a closed loop: each of the --concurrency clients sends the next query when the previous one returns. When the server slows down, it receives fewer queries, so the load adapts to the server. With --arrival-rate the load does not depend on the server. Queries arrive at random times at a fixed average rate, like requests from many independent users: the time between two arrivals is a random value with the exponential distribution and the mean 1 / R (a Poisson process). Each query is sent at its arrival time even if the previous queries are still running. At most --max-in-flight queries execute at the same time. An arrival that finds all of them busy waits in a client-side queue until one of them finishes. The latency of a query is measured in the same way as in the closed loop (see --client-side-time), so it does not include the time spent in the queue. The number of queued arrivals is reported separately. The --iterations option sets the number of arrivals; clickhouse-benchmark waits for all of them to finish. The --timelimit option stops the arrivals after the time limit; the queries in flight finish, and the arrivals in the queue are reported as not sent. The report contains two more lines, for example:

Arrivals: 1061, target rate: 100.000/s, achieved rate: 106.100/s, queued: 0 (max queue size 0).
In flight: mean 0.223, max 4 of 256.

Arrivals: The number of arrivals.
target rate: The rate from --arrival-rate.
achieved rate: The number of arrivals divided by the duration of the arrival process. It differs from the target rate by a random error, which is smaller for longer runs.
queued: The number of arrivals that found all --max-in-flight slots busy and waited in the client-side queue, and the maximum length of this queue.
not sent: The number of arrivals that were still in the queue when the run stopped. It is shown only if it is not zero.
In flight: The time-weighted mean and the maximum number of queries executing at the same time.

With open-loop arrivals QPS follows the arrival rate, so it says little about the cost of the load. In the open-loop mode the status string of each server also contains:

CPU: The CPU time of the queries on the server per second, in cores.
CPU per query: The mean CPU time of a query on the server, in seconds.

Both include the queries that failed during execution (with --ignore-error), because they also load the server. The CPU time is the sum of the UserTimeMicroseconds and SystemTimeMicroseconds profile events that the server sends for every query, so it covers the threads executing the query and is an approximation of its total CPU cost. For a distributed query it is summed over all servers the query runs on, because the initiator forwards the profile events of the remote servers. If the server sends no profile events (for example with --send_profile_events=0), the status string shows CPU: not reported by the server. To run a mixed workload, run several clickhouse-benchmark processes at the same time, one per class of queries. Each process reports the statistics of its own class. Give every process its own --seed, or none: processes with the same seed draw the same random sequence, so their arrivals coincide (or, with different rates, are scaled copies of each other). With independent seeds the combined arrivals of all processes are a Poisson process too. Examples:

# 100 queries per second for 10 seconds; the lines above are from this run.
$ clickhouse-benchmark --arrival-rate=100 --seed=42 --timelimit=10 --query "SELECT sum(number) FROM numbers(1000000)"

# A sweep over four rates, 60 seconds each, with per-query statistics.
$ clickhouse-benchmark --arrival-rate=5,10,20,40 --timelimit=60 --randomize --per-query < queries.tsv

Comparison Mode

clickhouse-benchmark can compare performances for two running ClickHouse servers. To use the comparison mode, specify endpoints of both servers by two pairs of --host, --port keys. Keys matched together by position in arguments list, the first --host is matched with the first --port and so on. clickhouse-benchmark establishes connections to both servers, then sends queries. Each query addressed to a randomly selected server. The results are shown in a table.

Example

$ echo "SELECT * FROM system.numbers LIMIT 10000000 OFFSET 10000000" | clickhouse-benchmark --host=localhost --port=9001 --host=localhost --port=9000 -i 10

Loaded 1 queries.

Queries executed: 5.

localhost:9001, queries 2, QPS: 3.764, RPS: 75446929.370, MiB/s: 575.614, result RPS: 37639659.982, result MiB/s: 287.168.
localhost:9000, queries 3, QPS: 3.815, RPS: 76466659.385, MiB/s: 583.394, result RPS: 38148392.297, result MiB/s: 291.049.

000%          0.258 sec.      0.250 sec.
000%         0.258 sec.      0.250 sec.
000%         0.258 sec.      0.250 sec.
000%         0.258 sec.      0.267 sec.
000%         0.258 sec.      0.267 sec.
000%         0.273 sec.      0.267 sec.
000%         0.273 sec.      0.267 sec.
000%         0.273 sec.      0.267 sec.
000%         0.273 sec.      0.269 sec.
000%         0.273 sec.      0.269 sec.
000%         0.273 sec.      0.269 sec.
000%         0.273 sec.      0.269 sec.
900%         0.273 sec.      0.269 sec.
990%         0.273 sec.      0.269 sec.

No difference proven at 99.5% confidence

Fundamentals

ClickHouse concepts

Best practices

Command-line options

Environment variable options

Output

Open-loop mode

Comparison Mode

Example

​Command-line options

​Environment variable options

​Output

​Open-loop mode

​Comparison Mode

​Example

Command-line options

Environment variable options

Output

Open-loop mode

Comparison Mode

Example