Perf Report

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

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.

Query the public CI database:

clickhouse client --host play.clickhouse.com --user explorer --secure -q "
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%'  -- or arm
  AND check_start_time >= now() - INTERVAL 30 DAY
  AND test_name IN (
    'test_name #N::new',
    ...
  )
GROUP BY test
ORDER BY slower_count DESC, test
"

Important:

• pull_request_number = 0 means master commits
• Test names in the DB have ::new and ::old suffixes — query with ::new
• Use %amd% for AMD results and %arm% for ARM results
• Query both architectures separately

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.

8. Deep-dive: trace log profiling (flamegraphs)

The logs.tar.zst archive contains right-trace-log.tsv and left-trace-log.tsv — these are exports of system.trace_log from each server, containing CPU and real-time stack samples for every query.

Extract trace logs:

tar -I zstd -xf tmp/perf_logs.tar.zst -C tmp/ ./right-trace-log.tsv ./left-trace-log.tsv

Key columns (TSV format, header in row 1, types in row 2, data from row 3):

• Column 7: trace_type — CPU (sampled CPU time), Real (wall clock), Memory, etc.
• Column 11: query_id — matches the {test.queryN.runM} pattern from server logs
• Column 19: symbols — comma-separated list of function names (leaf first), wrapped in ['...']

Find the hotspot for a specific query — extract CPU traces and count leaf functions:

grep "math.query2" tmp/right-trace-log.tsv | awk -F'\t' '$7 == "CPU" {print $19}' | \
  sed "s/\[//g; s/\]//g; s/'//g" | \
  awk -F',' '{print $1}' | \
  sort | uniq -c | sort -rn | head -20

This gives a flat profile of where CPU time is spent, similar to perf report. Compare left (master) vs right (PR) to see what changed.

Build collapsed stacks for flamegraph visualization:

grep "math.query2" tmp/right-trace-log.tsv | awk -F'\t' '$7 == "CPU" {print $19}' | \
  sed "s/\[//g; s/\]//g; s/','/;/g; s/'//g" | \
  sort | uniq -c | awk '{print $2, $1}' > tmp/math2_right.collapsed

The resulting .collapsed file can be processed with flamegraph.pl or the analyze-assembly.py --perf-map tool.

This is the fastest way to identify the root cause of a regression. Example: for a 31x exp10 regression, the trace log immediately showed modf consuming 577/1200 CPU samples on the PR binary vs 28/60 on master — pinpointing the exact function responsible without needing to reproduce locally.

The archive also contains pre-built SVG flamegraphs for queries the framework selected for detailed analysis:

tar -I zstd -tf tmp/perf_logs.tar.zst | grep "\.svg"

These come in .left.svg (master), .right.svg (PR), and .diff.svg (differential) variants, for both CPU and Real time. Not all queries get flamegraphs — only those the framework considers interesting.

9. Deep-dive: profile events from raw TSV

The archive contains per-query raw metric data in analyze/tmp/{test}_{queryN}.tsv. Each row is one run, with an array of all ProfileEvents (counters like UserTimeMicroseconds, OSCPUVirtualTimeMicroseconds, RealTimeMicroseconds, etc.).

tar -I zstd -xf tmp/perf_logs.tar.zst -C tmp/ ./analyze/tmp/math_2.tsv

The all-query-metrics.tsv file (linked from --links output) contains the processed comparison data with old/new values, ratios, and thresholds for every metric of every query. The fetch_perf_report.py tool already parses this, but the raw file has all metrics, not just client_time.

Important: use unique download paths

When analyzing multiple batches or PRs, use unique directory names to avoid overwriting:

mkdir -p tmp/batch1_amd tmp/batch5_amd
curl -sS "<batch1-logs-url>" -o tmp/batch1_amd/logs.tar.zst
curl -sS "<batch5-logs-url>" -o tmp/batch5_amd/logs.tar.zst
tar -I zstd -xf tmp/batch1_amd/logs.tar.zst -C tmp/batch1_amd/

Rules

• Never dismiss a regression without checking master history first. Do not call anything "noise" or "not actionable" based on intuition alone.
• Show all data. The user wants the full picture, not a filtered summary.
• Both architectures matter. AMD runs on Intel Sapphire Rapids (m7i.4xlarge), ARM runs on Graviton 4 (m8g.4xlarge). Regressions on one but not the other are still real.
• A test appearing in multiple CI runs of the same PR is a strong signal even if it also occasionally appears on master.
• Do not look at the PR code or run local benchmarks unless explicitly asked. This skill is purely about CI report analysis.