# Perf Report

> Analyze CI performance comparison reports for a ClickHouse PR. Lists all regressions and improvements, cross-references with master history to distinguish real changes from flaky tests.

- Skill: `clickhouse-clickhouse/perf-report` (Agent Skill)
- Install (CLI): `npx skillmds@latest add clickhouse-clickhouse/perf-report`
- Raw SKILL.md: https://api.skillmd.com/api/skills/clickhouse-clickhouse/perf-report/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Docs & Writing
- Author: ClickHouse (https://skillmd.com/u/clickhouse-clickhouse)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/clickhouse-clickhouse/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:

```bash
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`:

```bash
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:

| Magnitude | Test | Master slower/total (30d) | Verdict |
|---|---|---|---|

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`:

```bash
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:**

```bash
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:

```bash
# 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:

```bash
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 perf job writes `right-trace-log.tsv` and `left-trace-log.tsv`, exports of `system.trace_log` from each server, containing CPU and real-time stack samples for every query.

Two limits to know before reaching for these files. They are NOT shipped in `logs.tar.zst` (the archive excludes `*-trace-log.tsv`), and the dump holds only the four columns the report reads: `query_id`, `trace`, `trace_type`, `size`. There is no `symbols` column; addresses are symbolized separately through `*-addresses.tsv`.

**Use the report's collapsed stacks instead.** `report/stacks.$version.tsv` already carries symbolized, collapsed stacks per query, and it is what the report's own flamegraphs are built from. It has no header; its first three columns are `test`, `query_index` and `trace_type`, so select a query by those three fields (the `math.queryN` spelling is a query id and never appears in this file). Keep the trace types apart: `CPU` and `Real` count samples while `MemorySample` and `JemallocSample` sum bytes, so folding them together mixes units.

```bash
tar -I zstd -xf tmp/perf_logs.tar.zst -C tmp/ ./report/stacks.right.tsv
awk -F'\t' '$1 == "math" && $2 == 2 && $3 == "CPU"' tmp/report/stacks.right.tsv \
  | cut -f 5- | sed 's/\t/ /g' > tmp/math2_right.collapsed
```

The resulting `.collapsed` file can be processed with `flamegraph.pl`. Compare left (master) vs right (PR) to see what changed. Not with `analyze-assembly.py --perf-map`: that flag reads JSONL records keyed by instruction address, so every folded `symbol;...;symbol count` line is rejected as a JSON parse error and the weighting silently comes out empty.

**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:

```bash
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.).

```bash
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:

```bash
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.