Measure overhead of fsync_part_directory for SELECTs from MergeTree/ReplicatedMergeTree in ClickHouse (due to rename under lock)

lockfree-rename-benchmark.sh

#!/usr/bin/env bash

# Measure overhead of fsync_part_directory for SELECTs from MergeTree/ReplicatedMergeTree (due to rename under lock)
# Refs: https://github.com/ClickHouse/ClickHouse/pull/64955

set -e

bin=$1 && shift
id=$BASHPID

$bin client -nm -q "
create table rmt_without_fsync (key Int) engine=ReplicatedMergeTree('/tables/{database}/rmt_without_fsync', 'r1') order by tuple() settings fsync_part_directory=0;
create table rmt_with_fsync (key Int) engine=ReplicatedMergeTree('/tables/{database}/rmt_with_fsync', 'r1') order by tuple() settings fsync_part_directory=1;
create table mt_without_fsync (key Int) engine=MergeTree() order by tuple() settings fsync_part_directory=0;
create table mt_with_fsync (key Int) engine=MergeTree() order by tuple() settings fsync_part_directory=1;
"

for table in rmt_without_fsync rmt_with_fsync mt_without_fsync mt_with_fsync; do
    echo "*** Testing $table"

    sync
    $bin client -q "system stop merges $table"
    $bin client -q "insert into $table select randConstant()%100 from numbers(1e6)" --min_insert_block_size_rows=1 --insert_deduplicate=0 --insert_keeper_max_retries=0 >/dev/null &
    insert_pid=$!

    $bin benchmark --timelimit 10 --delay 0 -q "select count() from $table" --log_comment "lockfree-rename.$id.$table" "$@"

    kill -INT $insert_pid
    wait

    $bin client -q "drop table $table"
done

$bin client -q "system flush logs"

$bin client --format PrettyCompactMonoBlock -q "
SELECT
    log_comment,
    count() / dateDiff('second', min(event_time), max(event_time)) AS QPS,
    count() AS queries,
    sum(ProfileEvents['PartsLockWaitMicroseconds']) / queries AS lock_wait_avg,
    quantileExact(0.95)(query_duration_ms) duration_q95
FROM system.query_log
WHERE (log_comment LIKE 'lockfree-rename.$id.%') AND (type != 'QueryStart')
GROUP BY 1
"

Patched

┌─log_comment─────────────────────────────┬───QPS─┬─queries─┬───────lock_wait_avg─┬─duration_q95─┐
│ lockfree-rename.90977.mt_without_fsync  │ 384.3 │    3843 │ 0.41217798594847777 │            2 │
│ lockfree-rename.90977.mt_with_fsync     │ 422.2 │    4222 │ 0.06395073424917101 │            2 │
│ lockfree-rename.90977.rmt_without_fsync │ 368.6 │    3686 │  1.9834508952794356 │            2 │
│ lockfree-rename.90977.rmt_with_fsync    │   417 │    4170 │ 0.22853717026378897 │            2 │
└─────────────────────────────────────────┴───────┴─────────┴─────────────────────┴──────────────┘

Upstream

┌─log_comment─────────────────────────────┬───QPS─┬─queries─┬──────lock_wait_avg─┬─duration_q95─┐
│ lockfree-rename.91765.mt_with_fsync     │ 196.2 │    1962 │  2436.145259938838 │           19 │
│ lockfree-rename.91765.rmt_with_fsync    │ 203.9 │    2039 │ 2214.7631191760665 │           18 │
│ lockfree-rename.91765.rmt_without_fsync │ 394.6 │    3946 │  2.725798276735935 │            2 │
│ lockfree-rename.91765.mt_without_fsync  │ 411.2 │    4112 │  15.97932879377432 │            2 │
└─────────────────────────────────────────┴───────┴─────────┴────────────────────┴──────────────┘