Perf Report

Performance Report Analysis Skill

Arguments

• $0 (required): PR number (e.g. 99474) or a CI report URL

Steps

1. Fetch performance data

Use the fetch_perf_report.py tool to get TSV data for both architectures:

python3 .claude/tools/fetch_perf_report.py "https://github.com/ClickHouse/ClickHouse/pull/$PR" --arch amd --tsv
python3 .claude/tools/fetch_perf_report.py "https://github.com/ClickHouse/ClickHouse/pull/$PR" --arch arm --tsv

If a direct CI report URL is given instead of a PR number, use it directly.

Extract all changed queries (column 10 == 1 means the change exceeds the threshold):

• Column 1: test name
• Column 2: query index
• Column 4: batch
• Column 5: old time
• Column 6: new time
• Column 8: ratio (e.g. 1.5 = 1.5x)
• Column 10: 1 if changed, 0 if within threshold
• Column 12: "slower" or "faster"
• Column 13: query text

2. List ALL changes

Present the complete, unfiltered list of all changes above 1.10x, sorted by magnitude, for both architectures separately. Use tables with columns: Magnitude, Direction, Test, Query#, Batch.

Do NOT summarize, collapse, or hide entries. Do NOT dismiss anything as "noise" or "not actionable" without evidence. Every entry must be visible.

3. Cross-reference with master history

This step is MANDATORY. For every test that shows as slower or faster above 1.10x, check the public CI database to determine if this is a known flaky test or a genuine change introduced by the PR. Do NOT skip this step or use alternative approaches (like manually fetching perf reports from other PRs).

Query the default.checks table on play.clickhouse.com with user=explorer:

clickhouse client --format PrettyCompactNoEscapes --host play.clickhouse.com --user explorer --secure --query "
SELECT
    replaceRegexpOne(test_name, '::(new|old)$', '') AS test,
    countIf(test_status = 'slower') AS slower_count,
    countIf(test_status = 'faster') AS faster_count,
    countIf(test_status = 'unstable') AS unstable_count,
    count() AS total_runs
FROM default.checks
WHERE pull_request_number = 0
  AND check_name LIKE '%Performance%amd%'
  AND check_start_time >= now() - INTERVAL 30 DAY
  AND test_name IN (
    'norm_distance #2::new',
    'array_sort #0::new'
  )
GROUP BY test
ORDER BY slower_count DESC, test
"

Run one query per architecture — use '%Performance%amd%' for x86 and '%Performance%arm%' for ARM.

Critical details:

• Host: play.clickhouse.com, user: explorer (NOT play)
• Table: default.checks (NOT perftest or other tables)
• pull_request_number = 0 filters to master-only commits (no PR noise)
• Test names in the DB have ::new and ::old suffixes — always query with ::new
• Include ALL changed tests in a single IN clause to minimize round-trips

4. Classify each change

For each test, classify based on the master history:

• Flaky on master: appears as slower in >1% of master runs, or has many unstable entries. Note the count (e.g. "8/685 runs").
• New in this PR: 0 slower (or faster) appearances on master in the last 30 days with a meaningful number of total runs (>50). This change was first observed in this PR.
• Rarely on master: 1-2 appearances out of hundreds. Treat as borderline — note it but don't dismiss.
• For "faster" results: check if the test was previously slower on master (meaning this PR fixes it).

5. Present the verdict

Present the final classification in a table per architecture:

Classify as:

• Flaky — frequently appears on master, dismiss
• Unstable — high unstable count on master, dismiss
• New in this PR — investigate — regression not seen on master before
• New in this PR — improvement — speedup not seen on master before, or fixes a previously-slower test
• Rarely on master — borderline, note it

6. Summary

After the tables, provide a brief summary:

• Count of genuine regressions (never on master) per architecture
• Count of genuine improvements per architecture
• Count of flaky/dismissed entries
• Call out any extreme outliers (>2x) explicitly regardless of flaky status

7. Deep-dive: accessing CI logs (when asked)

When the user asks to investigate a specific regression further, download and analyze the CI artifacts.

Get artifact links using the fetch_ci_report.js tool with --links:

node .claude/tools/fetch_ci_report.js "<CI-report-URL-for-specific-batch>" --links

This will show logs.tar.zst, job.log.zst, all-query-metrics.tsv, report.html, etc.

Download and extract server logs:

curl -sS "<logs.tar.zst-URL>" -o tmp/perf_logs.tar.zst
tar -I zstd -tf tmp/perf_logs.tar.zst  # list contents
tar -I zstd -xf tmp/perf_logs.tar.zst -C tmp/ ./right/server.log  # PR binary
tar -I zstd -xf tmp/perf_logs.tar.zst -C tmp/ ./left/server.log   # master binary

• right/server.log = PR binary (the "new" version)
• left/server.log = master binary (the "old" version)

Analyze the query execution by finding the query ID in the server log:

# Find the query and its timing
grep "math.query2" tmp/right/server.log | grep -E "Aggregated|Read.*rows.*sec"

The perf framework uses query IDs like {test_name.query{N}.run{M}} (e.g. math.query2.run0). Look at:

• Aggregated ... in X sec — actual compute time
• executeQuery: Read N rows ... in X sec — total query time
• TCPHandler: Processed in X sec — wall clock including network

Compare both servers during the same time window to see if the machine was under load or if only the PR binary was slow. Check for:

• Background activity (merges, flushes, system log writes)
• Errors or warnings
• Whether the slowdown is in UserTime (CPU-bound) or wall clock (I/O/contention)

Check the git hash of the binary that actually ran:

grep "Starting ClickHouse" tmp/right/server.log | head -1

This shows the exact revision, build ID, and PID. Compare with what you expect — the CI perf test may use a different binary than the latest commit if the build was cached.