Benchmark MCP query/tile tasks on the DuckDB + httpfs backend (methodology + standing results)

[cboettig]

Tracking issue for benchmarking the MCP query/tile tasks on the DuckDB + httpfs backend — establishing where performance stands, what's rate-limiting, and pushing further. Consolidates the bench scripts on branch bench/s3-ceiling (s3-throughput / s3-ceiling / s3-diag / s3-decomp + the engine/mean-carbon probes).

TL;DR (so far)

• Real DuckDB queries are decode/compute-bound, not network- or httpfs-bound. S3 ≈ local ≈ internal-MinIO for heavy aggregates; throughput scales ~linearly with threads.
• DuckDB local/httpfs parquet reads are GB/s-class (uncompressed) and competitive with Polars / faster than PyArrow. Earlier alarming "~180–250 MB/s" numbers were measurement artifacts (see invalid probes).
• Column pruning is the dominant lever (~10×): 1-col sum ~16 GB/s vs 7-col ~1.6 GB/s uncompressed (local NVMe).

Methodology — valid metrics

• Report uncompressed GB/s and Mrow/s, never compressed-byte MB/s. Carbon hex compresses ~4–6×, so compressed-byte rates understate decode by that factor.
• Use queries that actually read+process data: sum()/avg() over real columns, or full materialize.
• Determine rate-limiting two ways: thread scaling (scales → compute-bound; flat → I/O-bound) and local-vs-remote (equal → compute-bound).
• Set DuckDB memory_limit explicitly: in a container DuckDB sizes it from host RAM and gets cgroup-OOMKilled at the pod limit.

Invalid probes — DO NOT use these to measure read throughput

• count(*) — answered from parquet row-count metadata; reads ≈ no data.
• count(COLUMNS(*)) — answered from per-column null-count metadata; reads ≈ no data. (This invalidated an earlier "DuckDB ~180 MB/s ceiling" claim — it was measuring footers, not transfer.)
• read_blob as a proxy for query throughput — measures sequential whole-file transfer that does not parallelize across files (~95 MB/s over httpfs). read_parquet parallelizes ranged reads and is 13–23× faster over the same httpfs. Use read_blob only to characterize raw transport, never as the query-path number.
• Environmental confounds to control: client NIC (cirrus has a 1 Gb/s card; the MinIO external-IP hairpin capped reads at ~1 Gb/s — fixed by using the internal minio-svc.minio.svc.cluster.local:9000), client CPU on small pods, and warm vs cold caches.

Standing results

Engine comparison, local NVMe (ZFS, warmed), carbon hex, uncompressed GB/s:

query	DuckDB	Polars	PyArrow
sum 1 col	16.6	9.5	5.4
read all 7 cols	1.6	1.6	0.38

Representative MCP aggregates — cirrus pod (128 core), httpfs → internal MinIO, full carbon hex (4.84B rows / 13.7 GB):

query	T=1	T=8	T=16	bound by
mean carbon / h0 (122 grp)	100.3s	10.6s	6.2s	CPU (9.4× then 1.7× scaling)
mean carbon / h1 (h3 rollup)	—	25.1s	14.1s	CPU (extra col + h3 on 4.84B rows)

Next steps

• Thread sweep to 48/128 on real queries (headroom: only tested to 16 of 128 cores). Re-evaluate production TILE_THREADS/THREADS=48.
• More query shapes: spatial joins, IUCN×carbon, group-by-h at multiple resolutions, filtered/DPP queries, tile-build SQL.
• Column-pruning audit of build/query SQL (the 10× lever).
• Denser datasets (GBIF hex). Engine cross-check vs cuDF/Polars (relates to Benchmark: compare mcp-data-server (DuckDB/CPU) against mcp-gpu-data-server #42, Fold GPU query path into this repo as an optional deploy-time backend (supersedes #64; archive the fork) #227).

Refs: #249 (throughput bench), #103 (httpfs no conn-pool / DNS ceiling), #237 (httpfs large-chunk bug, fixed in 1.5.4), #42 / #227 (GPU path).

[cboettig]

Compute-scaling status + resume plan (after cirrus pod restart)

Key correction: this session was CPU-throttled at 12 cores

The cirrus JupyterHub pod's cgroup cpu.max = 1200000 100000 = 12 cores, even though os.cpu_count() and sched_getaffinity both report 128 (the node). So the mean-carbon thread-sweep numbers below are at a 12-core ceiling, not the query's potential — THREADS=16 runs were already over quota (CFS-throttled), which is why 8→16 only gave ~1.7×.

Always check the real ceiling: cat /sys/fs/cgroup/cpu.max.

Standing mean-carbon numbers (cirrus, httpfs → internal MinIO, 4.84B rows / 13.7 GB, 12-core cap)

query	T=1	T=8	T=16
mean carbon / h0	100.3s	10.6s	6.2s
mean carbon / h1 (h3 rollup)	—	25.1s	14.1s

Compute/decode-bound (9.4× scaling 1→8). At a full allocation these should drop ~proportionally (h0 likely ~1.5–2s at 48 cores).

Resume steps after restart (with a higher pod CPU limit)

1. Confirm the new quota: cat /sys/fs/cgroup/cpu.max.
2. Run the committed sweep (branch bench/s3-ceiling, benchmarks/s3-compute-bench.py):

   MRE_THREADS=1,4,8,16,32,48 \
   uv run --with duckdb python3 benchmarks/s3-compute-bench.py
   

   (defaults to the fixed cirrus internal MinIO minio-svc.minio.svc.cluster.local:9000, no SSL, path style; sets memory_limit; reports uncompressed GB/s + Mrow/s + scaling.)
3. Look for where compute-scaling saturates → decide whether to raise production TILE_THREADS/THREADS from 48.

Then push further (from the issue's Next steps)

• Broaden query shapes (spatial joins carbon×IUCN/WDPA, filtered/DPP, tile-build) to find any genuinely I/O-bound shape.
• Column-pruning audit (the ~10× lever).
• Engine cross-check vs Polars/cuDF (Benchmark: compare mcp-data-server (DuckDB/CPU) against mcp-gpu-data-server #42, Fold GPU query path into this repo as an optional deploy-time backend (supersedes #64; archive the fork) #227).

Housekeeping

• Bench scripts committed on branch bench/s3-ceiling (s3-throughput/ceiling/diag/decomp/compute + the engine probe). Worth consolidating into one documented benchmark and PRing.
• ~14 GB scratch parquet left at /home/jovyan/bench_parq (persists across restart on the ZFS PVC) — delete when done.

[cboettig]

Thread-sweep results (full CPU, 32 GB pod)

cirrus, DuckDB httpfs → internal MinIO (minio-svc.minio.svc.cluster.local:9000), full carbon hex = 4.84B rows / 12.1 GB compressed / 56.5 GB uncompressed (4.7×). memory_limit=8GB, temp_directory=/tmp.

query	T=4	T=8	T=16	T=32	T=48	T=64	bound / saturation
mean carbon/h0 (1 col + partition)	20.7s	11.1s	6.7s	5.2s	5.1s	5.8s	saturates ~32, degrades >48
sum(carbon) [1 col]	—	10.9s	6.9s	5.5s	5.6s	—	saturates ~32
mean carbon/h1 (h3_cell_to_parent(h9,1))	—	25.6s	13.3s	8.3s	7.3s	—	still climbing past 48 (3.5×)
sum(all 7 cols) [heavy decode]	—	46.9s	23.6s	13.1s	10.3s	—	still climbing past 48 (4.6×)

Mrow/s at T=48: h0 945 · sum(carbon) 861 · h1 667 · sum(all 7) 471.

Conclusions

• All queries are compute/decode-bound — every shape scales with threads; none flatten early (the I/O-bound signature). Confirms decode/compute, not httpfs/network, is the limiter.
• 1-column queries saturate at ~32 threads (~870–945 Mrow/s); multi-column / h3 queries keep scaling past 48 (more decode work per row).
• THREADS=48 is well-chosen — at the knee for light queries, still helping heavy ones. Higher mainly helps multi-column/h3, but raises the connection fan-out risk from Investigate DNS exhaustion ceiling for high-concurrency S3 queries #103 under concurrency. Primary lever remains column pruning (1-col 873 vs 7-col 471 Mrow/s).
• Memory model: peak RSS ≈ memory_limit + ~2–3 GB uncounted httpfs buffers, growing with threads×columns. memory_limit=8GB peaked at 11.4 GB (sum-all, T=48). The prior 16 GB OOM was memory_limit=12GB × 96 threads. Keep memory_limit ≤ pod−8GB and threads ≤ ~48.
• These are fast: full 4.84B-row continental carbon aggregate in ~5–10s. The original "low speeds" were a measurement artifact (compressed-byte metric + invalid count probes), not a real ceiling.

Reproduce: MRE_THREADS=4,8,16,32,48 MRE_MEM=8GB uv run --with duckdb python3 benchmarks/s3-compute-bench.py (branch bench/s3-ceiling).

[cboettig]

CORRECTION: the production NRP-Ceph path is I/O-latency-bound, not compute-bound

Earlier in this issue I concluded queries are "compute-bound, not network/httpfs-bound." That was measured on cirrus's on-box MinIO (loopback, ~µs latency) and does NOT hold for the production NRP-Ceph path. On real NRP Ceph (rook-ceph-rgw), per-request latency dominates and the read is I/O-latency-bound, ~12–18× slower than cirrus for the same query/data.

Node comparison — mean carbon / h0, T=48 (4.84B rows)

node / storage	wall	notes
cirrus, on-box MinIO (loopback)	5.1s	compute-bound (µs latency)
NRP SDSC, co-located Ceph	77.8s	I/O-bound, still scaling at 48
NRP Berkeley, Ceph over Internet2	94.9s	I/O-bound (5.2× from T=16→48)

Even co-located NRP is I/O-bound → it's the Ceph RGW per-request path, not node distance.

I/O ceiling report — 100G DTN (k8s-100g-dtn-6), full 13.7 GB compressed wire read

layer	throughput	limited by
Fabric/NIC (expected)	~100 Gb/s	—
boto3 parallel (best client) conc 64–256	~7 Gb/s (plateau)	Ceph RGW read path
DuckDB core httpfs T=192	~3.0 Gb/s (climbing, noisy)	httpfs small-range pattern + RGW
DuckDB core httpfs T=48 (≈prod)	~1.2 Gb/s	httpfs + RGW latency
cache_httpfs T=96/192	FAILED — "Could not connect to server" (RGW)	—

Two stacked ceilings: (1) the RGW caps a single pod at ~7 Gb/s even with boto3 (~7% of fabric); (2) DuckDB adds ~2.4× on top from its request pattern. Concurrency helps both (DuckDB 5.7× from T=16→192) but can't beat the RGW.

Caveats / reproducibility

• RGW ceiling is variable: the same DTN hit 31 Gb/s (boto3) in an earlier run vs 7 Gb/s here — shared/contended gateway. Needs multi-run, multi-node/time sampling to characterize the distribution before quoting a firm number.
• /sys pod-veth speed reads 10 Gb/s (CNI artifact; node did 31 Gb/s → real NIC faster).
• T=96 dip is run-to-run noise.
• cache_httpfs connection failures need debugging before it's a viable lever.

Revised takeaways (supersede the "compute-bound" framing for NRP)

1. Production NRP reads are I/O-latency-bound; more threads keep helping (not saturated at 48 — PR k8s: scale to 6 replicas + thread-tuning benchmarks #102 saw benefit to ~400, capped by the Investigate DNS exhaustion ceiling for high-concurrency S3 queries #103 DNS/connection ceiling).
2. The dominant infra limit is the Ceph RGW read path (~7 Gb/s/pod, variable) — the right thing to take to NRP: not bandwidth in the abstract, but per-pod RGW read throughput/latency vs a ~100 Gb/s fabric.
3. Co-location is a ~15× lever (cirrus on-box vs NRP) — a local/co-located cache for hot datasets would massively speed read-heavy MCP queries.
4. The earlier "compute-bound, GB/s-class" result is valid ONLY for near-zero-latency storage (cirrus loopback), not production.

Reproduce: benchmarks/s3-io-ceiling-bench.py + k8s/s3-io-ceiling-job.yaml (branch bench/s3-ceiling).

[cboettig]

RGW ceiling: shared, not per-client (+ cache_httpfs dead-end)

Per-client vs shared RGW (raw boto3, engine-independent). 4 pods on 4 distinct us-west nodes reading the 12 GB carbon hex from rook-ceph-rgw-nautiluss3.rook simultaneously (verified overlapping time windows):

	Gb/s
single pod (conc 64–256, flat)	~7 (variable; once ~31)
4 pods concurrent — each	~2.5
4 pods concurrent — aggregate	~10

4 clients → ~10 Gb/s aggregate, not 4×7≈28. So the West RGW read path is a shared ceiling (~7–10 Gb/s aggregate, high variance), ~10% of the ~100 Gb/s fabric — an infra property, not per-client. 8 pods (~1024 concurrent connections) → RGW connection resets (IncompleteRead), a second (connection-count) ceiling echoing #103.

cache_httpfs — not a lever here. Works fine on low-latency storage (cirrus), but on NRP Ceph: fanout-capped it's slower than core httpfs (0.7 vs 0.9 Gb/s @t=32); uncapped fanout × high threads → "Could not connect to server" (same RGW connection exhaustion). Its metadata/glob/file-handle caching doesn't address the per-range-request latency that dominates.

Net for the report: the bottleneck is the shared West-RGW read path (~10 Gb/s aggregate, connection-limited), well below the fabric. Levers: (a) NRP-side — more RGW throughput/replicas/balancing (drafted inquiry: benchmarks/nrp-ceph-inquiry.md); (b) our-side — co-location / on-box cache (~15× on cirrus), column pruning (~10×), and more DuckDB threads (still helps, capped by connection resets).

[cboettig]

source.coop (AWS us-west-2) vs NRP Ceph — full eval + Prometheus confirmation

Infra-side confirmation (NRP Prometheus, ceph_rgw_* on the West exporter 67.58.50.67)

• RGW read peak = 19.5 Gb/s over both 1h AND 24h — over a full day of all-cluster load the West RGW tier never exceeds ~20 Gb/s. Other RGW exporters ~0 during our reads → single West gateway tier. Confirms the client-side ceiling independently.
• RGW GET latency median = 118 ms (24h baseline) — inherent per-request latency; this is the root cause of the I/O-latency-bound behavior (DuckDB's many small range-requests each pay ~120 ms).

source.coop vs NRP (same 12 GB carbon hex, from NRP us-west nodes)

metric	NRP Ceph (West RGW)	source.coop (AWS us-west-2, over Internet2)
boto3 single pod (best)	~7 Gb/s	~5.6 Gb/s
boto3 4-pod concurrent aggregate	~10 Gb/s (shared)	~19 Gb/s (near per-client)
RGW/tier hard ceiling	~20 Gb/s (Prometheus, single tier)	scales past — AWS S3 not a single gateway
DuckDB core httpfs sum-all, T=192	~3.0 Gb/s	~1.7 Gb/s (higher WAN RTT → slower single query)
GET latency	~120 ms (RGW)	tens of ms (WAN RTT) but throttles anon at high conc
cache_httpfs	fails (RGW conn exhaustion)	works, but T=192=5.8 Gb/s is warm-/tmp-cache reuse, not cold S3

Takeaway

• NRP Ceph: lower latency (in-cluster) → better for interactive single queries, but a hard ~20 Gb/s shared-RGW ceiling and ~120 ms GET latency.
• source.coop (AWS S3): higher WAN latency (worse single-query DuckDB) but scales past the single-gateway ceiling — ~19 Gb/s aggregate at just 4 clients, would keep climbing. Better for high-concurrency / many-user aggregate load.
• Neither approaches 100 Gb/s for a single query — both are latency-bound; concurrency is the only lever.
• Practical: for many concurrent users, reading from source.coop can beat NRP's shared RGW aggregate; for single-query latency, NRP wins. Co-location / on-box cache still beats both (~15×, cirrus).

[cboettig]

Follow-up spun out to #258 — exploring LINSTOR (near-native NVMe, US-West linstor-sdsu/linstor-igrok) to bypass the RGW read ceiling by reading local Parquet instead of s3://. Covers the RWO constraint vs our multi-replica serving, and a plan to benchmark LINSTOR-local reads vs the ~7/~10 Gb/s S3 numbers here.