Perf Comparison

ClickHouse Performance Comparison

Use this skill whenever the goal is to answer:

Is this ClickHouse performance result real, flaky/noisy, already known from history, or worth deeper investigation?

The evidence may come from existing performance CI/dashboard data, a local perf.py run, or both. Do not treat this as two separate modes; use whichever evidence is available and add missing evidence only when it changes the decision.

Core principles

1. Keep the current accepted evidence separate from stale exploratory data. If a newer rerun/confidence/history check supersedes an older table, say so and show the newer numbers.
2. Do not call a regression real from one changed row. Check repeated PR runs, confidence, master/history trend, and test stability.
3. Do not dismiss a regression as flaky without evidence. Show the history/trend or repeated-run data that justifies the verdict.
4. Do not emit opaque dashboard internals as evidence. Raw strings like M1:downgrade, M2:neutral are not report-ready; translate confidence into tier/reason and named evidence checks.
5. Metric, arch, query index, and run identity must match. Do not mix ARM with AMD or client_time with CPU/real/memory metrics.
6. Coverage/PR-diff overlap is supporting evidence, not proof. It raises or lowers plausibility but does not replace measurements.
7. Local runs are validation evidence, not a substitute for CI history. Local setup differences must be reported.
8. Never file issues, comment on PRs, or edit source unless explicitly asked.

Useful helper scripts in this skill

# List performance.ci runs for a PR.
python3 scripts/perf_api.py runs --pr 104350

# Inventory a PR first. This prints a small summary layer, then separates changed slowdowns,
# changed speedups, and high-noise rows.
python3 scripts/perf_api.py pr-inventory --pr 104350 --limit 20 --with-confidence

# 30-day master status classification via play.clickhouse.com default.checks.
python3 scripts/perf_api.py master-checks --pr 104350 --limit 50

# Inspect raw CI metric TSVs only when dashboard/API cannot provide required artifact-level data.
python3 scripts/perf_api.py tsv-inventory --tsv pr_104350_amd.tsv pr_104350_arm.tsv --limit 20

# Summarize a run's changed metrics.
python3 scripts/perf_api.py changes --run-id "$RUN_ID" --with-confidence

# Inspect one query with trend/history/coverage/flamegraph diff.
python3 scripts/perf_api.py query \
  --run-id "$RUN_ID" \
  --test aggregation_in_order_2 \
  --query-index 4 \
  --metric client_time \
  --arch arm \
  --with-confidence --with-trend --with-history --with-coverage --with-flamegraph-diff

# Compare one test/query/metric across all dashboard runs for a PR.
python3 scripts/perf_api.py pr-query-history \
  --pr 104350 \
  --test aggregation_in_order_2 \
  --query-index 4 \
  --metric client_time \
  --arch arm

# Parse local tests/performance/scripts/perf.py output.
mkdir -p tmp
python3 scripts/parse_perf_py.py tmp/perf-output.tsv

References:

• references/ci-api.md — performance.ci API and dashboard workflow.
• references/local-perf.md — local perf.py, optional server startup, datasets, and profiling.
• references/verdict-rules.md — classification rules and reporting checklist.

Data source priority

Use the richest reliable source for each question:

1. performance.ci API/dashboard first for current PR runs, full changed-row inventory, confidence, query details, trend/history charts, coverage overlap, and flamegraph-diff links.
2. ClickHouse Play HTTP (default.checks) for 30-day master status counts: how often the same test/query appeared slower, faster, or unstable on master. Use master-checks for this.
3. CI artifacts/logs when dashboard/API cannot provide root-cause data: server logs, job logs, raw trace logs, raw profile-event TSVs, exact binary revision/build ID, or complete artifact-manifest details.
4. Local perf.py only when explicit old/new binaries are provided and local validation is useful; report it as non-CI-equivalent.

Standard performance.ci API

BASE="https://performance.ci.clickhouse.com/api/v1"

Find PR runs:

PR=104350
curl -fsS "$BASE/runs?q=$PR" | jq .

Get run overview:

curl -fsS "$BASE/runs/$RUN_ID?metrics=client_time,real_time,cpu_time,memory" | jq .

Get query detail:

curl -fsS "$BASE/runs/$RUN_ID/tests/$TEST/queries/$QUERY_INDEX?metrics=client_time,real_time,cpu_time,memory" | jq .

Get trend/history:

curl -fsS "$BASE/runs/$RUN_ID/tests/$TEST/trend?metric=$METRIC&queryIndex=$QUERY_INDEX&arch=$ARCH" | jq .
curl -fsS "$BASE/history/$TEST/$QUERY_INDEX?metric=$METRIC&arch=$ARCH" | jq .

Get PR coverage overlap for a test:

curl -fsS "$BASE/runs/$RUN_ID/tests/$TEST/coverage?fileSet=intersecting" | jq .

Open the UI flamegraph page first:

https://performance.ci.clickhouse.com/runs/$RUN_ID/tests/$TEST/queries/$QUERY_INDEX

Use the raw flamegraph-diff API only for machine-readable sample deltas:

curl -fsS "$BASE/runs/$RUN_ID/tests/$TEST/queries/$QUERY_INDEX/flamegraph-diff?metric=$METRIC&arch=$ARCH&traceType=CPU" | jq .

API caveats observed in live testing:

• unstableQueries[] can contain high-noise/below-threshold rows even when the summary card says Unstable queries: 0; do not treat them as actionable changed rows unless they are over threshold.
• trend.selectedRunPoint.value may be a zero marker; use query-detail old/new values for the measurement.
• coverage?fileSet=intersecting may omit totals.intersectingFiles when there are zero intersecting files; use len(files) as the fallback.
• Flamegraph endpoints can return 404 or no frames; missing flamegraph data is absence of evidence, not proof of noise.

Evidence ladder

For each suspicious change, collect as much of this ladder as needed:

1. Complete changed-row inventory, including improvements: before picking representative examples, list changed slowdowns, changed improvements/speedups, and high-noise/unstable-threshold rows separately for the relevant PR/run/arch. Start with python3 scripts/perf_api.py pr-inventory --pr <PR> --with-confidence. The inventory must begin with a small generated summary layer: Top likely signal, Top likely noise, and Top improvements. Changed-row tables must separate signal evidence from noise/uncertain evidence. Do not mix high-noise rows into changed slowdown/speedup tables. Use CI artifacts only when dashboard/API cannot provide required raw data.
2. Changed row details: test, query, metric, arch, old/new, diff, threshold.
3. Repeated PR runs: did the same query/metric/arch change repeatedly over several dashboard runs?
4. Confidence data: report confidence tier/reason and named checks (Raw Sample Evidence, History Adaptive Threshold, Recent Master Variation, etc.). Never leave raw M1/M2/... codes unexplained.
5. Master/history trend: report period, all-history p05/p25/p50/p75/p95, recent-window p05/p50/p95, and selected old/new values versus p50/p95. Do not use only min/max/median/last.
6. Master status counts when needed: run python3 scripts/perf_api.py master-checks --pr <PR> to query play.clickhouse.com default.checks over master-only runs and classify rows as new/rare/flaky/unstable/fixes-known-regression. The output should be grouped by signal vs noise/uncertain evidence and link to the test page with the query selected.
7. Related metrics: do CPU/real/client/memory move consistently or contradict each other?
8. Coverage/PR relation: did the PR touch code covered by this test? does the query text match the changed subsystem?
9. Flamegraph/profile: if available, does a stack/function explain the changed metric?
10. Artifacts/logs for root cause: if dashboard/API cannot answer why, download CI artifacts for server logs, trace logs, raw profile events, SVG flamegraphs, and exact binary/build metadata.
11. Local reproduction: if needed, run perf.py against old/new binaries with isolated servers.

Stop when the verdict is clear; do not gather expensive evidence just to decorate a report. Exceptions: always inventory improvements as well as regressions; for large PRs or many changed rows, do not stop after a few representative examples; produce a portfolio/ranking summary first. Avoid repetitive all-run top tables unless the caller asks for verbose output or rerun history is central to the conclusion.