Keeper Stress Analysis

Keeper Stress-Test Analysis Skill

Analyse ClickHouse Keeper stress-test results from keeper_stress_tests.keeper_metrics_ts on play.clickhouse.com (the same data warehouse the Grafana keeper-stress-run-details dashboard reads from). The skill captures a tested end-to-end workflow plus hard-earned methodology lessons — use it instead of re-deriving the analysis each time.

When to use

This skill triggers on these kinds of requests:

• "Validate these Keeper PRs against the stress tests"
• "What changed in Keeper between 2026-04-01 and 2026-05-01?"
• "Did PR #X cause any regression in Keeper stress?"
• "Why did p99 spike on date Y?"
• "Give me a summary report of Keeper stress runs"
• "How are the Keeper PRs performing on the dashboard?"

The skill does NOT touch any other CI data — it's specific to the Keeper stress framework.

Skill home + working dir convention

• Skill home: the directory containing this SKILL.md. May be either
  • user-level: ~/.claude/skills/keeper-stress-analysis/, or
  • project-level: <repo>/.claude/skills/keeper-stress-analysis/. Both work; scripts/rebuild.sh resolves the home from its own location.
• Working dir (default): tmp/keeper_stress_skill/ under the user's current directory
• The orchestrator scripts/rebuild.sh accepts a working-dir argument as $1, a lower-bound TS-filter as $2, and an optional upper-bound TS-filter as $3 (default 9999-12-31 = unbounded)

Workflow — five phases

Phase 1 — Capture intent

Parse the user's request into one of these shapes:

If the user hasn't given a window, ask (date range OR PR set OR specific question). Default window: from 2026-03-25 (when the current framework began) to today.

Phase 2 — Pull staging data

Always run all 6 SQL queries first into <work_dir>/staging/:

# Locate the skill home (works for either ~/.claude/skills/... or <repo>/.claude/skills/...)
SKILL_HOME="$(find ~/.claude/skills .claude/skills -maxdepth 2 -type d -name keeper-stress-analysis 2>/dev/null | head -1)"
"$SKILL_HOME/scripts/rebuild.sh" tmp/keeper_stress_skill 2026-03-25

rebuild.sh takes three optional args: $1 work dir, $2 lower-bound timestamp filter (default 2026-03-25, when the current keeper-stress framework went live), and $3 upper-bound timestamp filter (default 9999-12-31, i.e. unbounded — same behaviour as a single-arg invocation). The bounds are injected into the SQL via {{TS_FILTER}} / {{TS_FILTER_END}} placeholders and exported to the Python pipeline as KEEPER_SKILL_THRESHOLD / KEEPER_SKILL_THRESHOLD_END (read by build_pr_nightly_map.py and compute_deltas.py for the in-window vs out-of-window split). The upper bound is exclusive (ts < $3), so for "between A and B" inclusive on B, pass B+1 day. To analyse a different window, pass new values — no source edits needed.

For a closed date-range analysis like "what changed between 2026-04-01 and 2026-05-01?", invoke:

"$SKILL_HOME/scripts/rebuild.sh" tmp/keeper_stress_skill 2026-04-01 2026-05-01

The rebuild.sh script:

1. Copies queries/*.sql and scripts/*.py into the work dir.
2. Runs each query against https://play.clickhouse.com/?user=play via curl --data-urlencode.
3. Substitutes {{TS_FILTER}} placeholder if present in the SQL.
4. Builds merged_metrics.tsv (one row per scenario × backend × commit; ~95+ columns covering bench, prom, mntr, container metrics).
5. If <work_dir>/../pr_meta.tsv exists, builds the per-PR pipeline too.
6. Builds cumulative_gains.tsv and cumulative_gains_summary.tsv.

The 6 staging files dropped under staging/:

• bench_summary.tsv — bench-side summary (rps, p99, errors, ops, mem)
• prom_rates.tsv — Keeper prom counters as rates per node
• prom_gauges.tsv — Keeper prom gauges + cumulative-failure counters
• mntr.tsv — ZK 4LW mntr outputs
• container.tsv — cgroup CPU + memory
• pr_branches.tsv — PR-branch smoke stress runs (3 scenarios per branch)

Phase 3 — Build derived tables

Pick the right script(s) based on Phase 1's intent:

All Python steps are dispatched through keeper_stress.py:

The individual step files (build_metrics_table.py, build_pr_nightly_map.py, etc.) are still importable modules; keeper_stress.py dispatches to their main() entrypoints.

The per-PR markdown matrix is not generated by a script — Claude composes it directly from per_pr_metrics_long.tsv (long-form per-PR Δ rows) plus per_pr_summary.tsv (cumulative window numbers), using examples/sample_outputs/PR_PERF_TABLE.md as the structural template. Verdicts are Claude's judgment based on the data + Phase 5 caveats — there is no canonical lookup table.

For PR-set work, the user needs to provide a pr_meta.tsv mapping PR number → title, mergedAt, mergeCommit, base, headRefName. If absent, generate it via gh:

{
  printf 'pr\ttitle\tmergedAt\tmergeCommit\tbase\theadRefName\n'
  for pr in <numbers>
  do
    out=$(gh pr view "$pr" --repo ClickHouse/ClickHouse \
          --json title,mergedAt,mergeCommit,baseRefName,headRefName \
          -q '[.title,.mergedAt,.mergeCommit.oid,.baseRefName,.headRefName] | @tsv' 2>/dev/null)
    printf '%s\t%s\n' "$pr" "$out"
  done
} > tmp/keeper_stress_skill/../pr_meta.tsv

(The pr_meta.tsv lives one level above the work dir so all scripts can find it. headRefName is required for build_pr_branch_isolated.py — without it, that step silently produces no rows.)

Phase 4 — Generate output

Pick the deliverable that matches the request:

Cross-reference the canonical example when filling templates. Never invent prose without a backing data source.

Free-form recipes

Common questions that don't need a new pipeline — just awk over an existing TSV:

# Did <PR> regress fault scenarios specifically?
awk -F'\t' '$1=="<PR>" && $3 ~ /-fault\[/' tmp/keeper_stress_skill/per_pr_scenario_deltas.tsv

# Single-scenario validation (one row, all metric Δs):
awk -F'\t' '$1=="<PR>" && $3=="prod-mix-no-fault[default]"' tmp/keeper_stress_skill/per_pr_scenario_deltas.tsv

# Co-merge diagnosis when a PR's delta looks suspicious — check the `co_merged`
# column in per_pr_summary; if non-empty, the delta is jointly attributable.
awk -F'\t' 'NR==1 || $1=="<PR>"' tmp/keeper_stress_skill/per_pr_summary.tsv

# Per-commit time-series for one (scenario, backend, metric) — emits
# `run_ended\tsha8\tvalue` rows sorted ascending by ts.
awk -F'\t' 'NR==1 {for(i=1;i<=NF;i++) if($i=="<METRIC>") c=i; next}
            $1=="<scenario>" && $2=="<backend>" && $c!="" {print $5, $4, $c}' \
   tmp/keeper_stress_skill/merged_metrics.tsv | sort

These three patterns cover the bulk of "drill into one slice of the data" requests; reach for the pipeline only when the slice doesn't already exist as a column or row in merged_metrics.tsv / per_pr_*.tsv.

Phase 5 — Apply learned-the-hard-way checks

Before quoting any number, run these checks:

Memory check (THE most common trap)

If a memory delta > 5 % is reported, separately query:

• container_memory_bytes (the cgroup peak — sensitive to bench page cache)
• KeeperApproximateDataSize (Keeper's own state report)

If the cgroup moved but KeeperApproximateDataSize did NOT, the delta is bench-side, not Keeper-side. See references/known_confounds.md for PR #100670 example.

# Quick check pattern:
awk -F'\t' '
NR==1 {next}
$1==SCENARIO && $2==BACKEND {
  date=$5; gsub(/ .*/, "", date)
  printf "%s  sha=%s  KeeperApproxDataSize=%5.2fGB  container_peak=%5.2fGB\n",
    date, $4, $7/1e9, $72+0
}' merged_metrics.tsv | sort

Step-change check

If a metric changes as a single-day step across multiple unrelated scenarios, it's almost certainly bench-side. Cross-reference references/known_confounds.md:

• #100670 "keeper-bench: go faster" landed 2026-04-04 — affects read-heavy memory + multi-write error_pct
• #101801 "keeper-bench: more features" landed 2026-04-11 — affects rocks-side write-multi memory

Noise-floor check

The single-nightly Δ noise floor is ±3-5 % on rps/p99. The typos PR #102739 (which cannot affect Keeper performance) shows ±5 % rps Δ via PR-branch isolation — that's the floor. Never claim sub-3 % per-PR effects without an isolation method.

The PR-branch isolation pool widens to ±1 ISO week if the same-week pool has fewer than 2 entries (using datetime arithmetic so year/W01/W52-53 boundaries are handled). Same-branch runs are excluded from the pool so prior WIP commits don't bias the median toward the PR's own change. Full method in references/methodology.md Method C and the docstring at the top of scripts/build_pr_branch_isolated.py.

CPU spike check

container_cpu_usage_usec rates can spike to spurious 18-38 cores from counter discontinuities. Always use p95_cpu_cores, never max_cpu_cores. See references/metric_glossary.md.