cluster-spec-sheet: Sweep prepared QPS across concurrency and CPU allocations

[aljoscha]

Motivation

Part of the spec-sheet QPS benchmark stack. Two high-concurrency points on one query cluster cannot distinguish adapter scaling from compute or load-driver limits.

Description

Replace the existing SELECT QPS strong-scaling scenario, preserving its name and environmentd CPU allocations (1 through 32 powers of two). COPY throughput and other scenarios remain unchanged.

Measure prepared one-row indexed point lookups at 1, 2, 4, 8, 16, 32, 64, 128, 256, and 512 persistent clients, replacing the old simple-protocol ten-row scans. Distribute clients across configurable single-replica 50cc query clusters, defaulting to 32. Simple protocol is available explicitly.

Keep configuration and result validation in cluster_spec_sheet_qps.py, with orchestration in the existing composition using upstream pgbench. Remove this composition's unused dbbench helper and service; leave the standalone dbbench image unchanged.

Each point has 5 seconds warmup and 20 seconds measurement: 25 minutes of query phases plus connection/setup and log-flush overhead for the default six-CPU-allocation sweep. Save mean/p50/p95/p99/maximum latency, replica utilization, and load-driver qualification in CSV/JSON artifacts. Annotate possible driver or compute saturation.

Continue uploading QPS to the existing analytics table, encoding protocol/concurrency/cluster count in test_name. Scenario version 3.0.0 separates the new workload from historical results. Latency remains in artifacts, with no database migration or dashboard changes.

Reuse the existing published Postgres image, with no new image publication or CI local-build overrides required. Region colocation is supplied independently by #39599; no experimental runtime flags or statement-logging overrides are included.

Verification

Add Python coverage for configuration, libpq environment routing and credential preservation, result validation, all CPU/concurrency/protocol combinations, cluster-count limits, and pgbench execution/result-artifact wiring. Existing spec-sheet runs exercise integration without an additional smoke-test CI step.

[antiguru]

Posted by Claude Code on behalf of @antiguru.

Sweeping concurrency across independently routed clusters fixes the main weakness of the old two-point measurement. The CI wiring, the failure path, and the replica telemetry need another look.

CI: CI_ALLOW_LOCAL_BUILD on three steps

The flag is needed only because qpsbench is publish: false. It also changes behavior for every image in those steps.

• mzbuild.py drops pull retries from 5 to 1 when the flag is set, which removes the hardening against flaky Docker pulls.
• If a published image is missing, the step falls back to building it on the agent instead of failing fast with "Expected builds to be available".
• The production cluster and source-ingestion steps don't run the QPS scenario, so they don't need the flag at all.

Publishing qpsbench like dbbench (whose mzbuild.yml uses the default publish: true) would remove the need for all three env overrides. The linux-aarch64-small to medium change applies to all 7 staging shards, not just the one running envd_qps_scalability.

Failure path loses all diagnostics

composition.run defaults to check=True. When qpsbench exits 1 on a failed measurement, run raises before json.loads(output.stdout), so the partial result JSON is discarded. As written in #39671, qpsbench prints only "QPS measurement failed (credentials and query errors omitted)" to stderr. A failed point therefore reports neither the query error nor the partial numbers. The errors != 0 branch of validate_qps_result is also unreachable through this path.

Replica CPU telemetry vs. the 20s window

mz_cluster_replica_utilization_history is fed by a 60s sampling loop (METRICS_INTERVAL in src/controller/src/clusters.rs). With a 20s measurement window, each replica gets at most one sample and often none. I expect the "no query-replica CPU samples" warning to fire on most points, and replica_cpu_peak_percent to reflect a single instant rather than a peak. The 80% check therefore cannot reliably show compute headroom. Either lengthen the window past the sampling interval or read the replica's CPU from a source with finer resolution.

Setup cost per CPU point

On Cloud, EnvdCpuSweep.apply recreates the region for every CPU allocation. run then creates 31 clusters and hydrates them serially before measuring, which happens 6 times per sweep. The README's "25 minutes plus setup" doesn't say how large that setup is. It would help to measure it once, or to create the clusters concurrently.

Smaller points

• qps_connection_flags still returns the dbbench flag list (-driver, -params, ...), which QpsSweep.config then parses back into a dict. Returning structured connection parameters would remove the round trip and the dead -driver/cluster=c entries. The abstract method's docstring still mentions driver.
• The finally block issues several queries. If one of them raises (for example after a connection loss), it masks the original exception.
• Hydration waits use SET cluster = ... followed by a separate SELECT. If run_query reconnects in between, the SELECT runs on the default cluster and doesn't wait for that cluster's index.
• The Docker warning is appended once per point, which repeats it about 60 times in the annotation.
• test_sweep_defaults asserts arithmetic on the default constants, so it can only fail when someone changes a default on purpose.

[antiguru]

Looks good.

[antiguru]

Consider using shlex.quote instead.

[antiguru]

Correction to my earlier review: spec-sheet build 63 disproves my prediction that the "no query-replica CPU samples" warning would fire on most points. Every one of the 60 points reports 8 to 14 replica_cpu_samples, and warnings is empty throughout. The query aggregates across all 32 replicas, and each replica reports once per 60s METRICS_INTERVAL, so roughly a third of them land in each measurement window.

A narrower version of the concern still holds. Each replica contributes at most one sample per point, so the reported peak covers only the replicas that happened to report inside the window. In build 63 replica CPU stays at 2 to 4.3%, so this does not affect the current results, but it would understate the peak if query replicas ever became the bottleneck.

Posted by Claude Code.

[def-]

QA LLM Review

1. MEDIUM -- Unit test posts a real "QPS benchmark qualification warnings" annotation on every CI build

misc/python/materialize/test_cluster_spec_sheet_qps.py:203

test_pgbench_measurement_records_max_latency calls the real QpsEnvdStrongScalingScenario.measure() without isolating it from Buildkite, so when CI runs it, it executes buildkite-agent annotate. Every test pipeline build, including unrelated PRs and main, then gets a yellow "QPS benchmark qualification warnings" annotation. This already happened on this PR's own build.

Details

The stubbed run_query returns [(None, 0)], so measure() always appends "no query-replica CPU samples in measurement window". measure() then calls buildkite.add_annotation(..., context="qps-headroom") (test/cluster-spec-sheet/mzcompose.py:2061), guarded only by buildkite.is_in_buildkite(). It also calls buildkite.upload_artifact(...) (mzcompose.py:2018). That upload is a no-op only because the JSON was written under tmp_path while the upload resolves the path against MZ_ROOT.

The test does run inside Buildkite. lint-fast (inputs: ["*"], so every build) runs bin/lint, and check-python-docs.sh runs pytest --doctest-modules misc/python from there. bin/ci-builder forwards every BUILDKITE* variable and mounts the host buildkite-agent. On build 138362 for this head commit, job 01a12107-b26d-4771-aaff-eec209e0ef4b ("Lint and rustfmt") shows the test passing. The build's annotations include context 01a12107-b26d-4771-aaff-eec209e0ef4b-qps-headroom (style warning) with the body qps_prepared_1_conns_1_clusters, envd=2: no query-replica CPU samples in measurement window.

Fix: make the test hermetic, e.g. monkeypatch.setattr(module.buildkite, "is_in_buildkite", lambda: False) (or monkeypatch.delenv("BUILDKITE", raising=False)) before calling measure().