PR preview envs and local dev run the full page-cache loop against prod ClickHouse — now 19.7 cores, ~39% of the cluster

[nikw9944]

Summary

lake_dev_reader — the credential lake's own README tells developers to use for local development against production ClickHouse — consumed 12.5 cores, 27.8% of the entire prod cluster, for at least a day. One developer's local k3d stack, running the full page-cache refresh loop against prod data.

It stopped at 20:53Z on 2026-08-20 when the developer shut it down. Nothing about the setup prevents the next person from reproducing it.

What happened

A local api pod (api-fcfb7459c-fhjj9, k3d, pod CIDR 10.42.0.0/16) connected as lake_dev_reader and ran lake's page-cache refresh loop — all ~39 entries — against prod ClickHouse.

Measured over a 15-minute window while live:

pod	runs	cores	read
api-fcfb7459c-fhjj9	3,673	12.48	4.61 TiB
indexer-6694979854-j4d59	57	0.02	2.56 GiB

4.61 TiB per 15 minutes is ~18 TiB/hour. Over a 5.9h window it was 84,773 queries at 11.8 cores sustained. It had already been running the previous day at ~6.6 cores (16.8% of cluster), so it roughly doubled.

The queries were byte-for-byte prod's own most expensive page-cache entries, at the same cost per run:

shape	cores	interval	cpu/run	read/run	prod cpu/run
metro-pair join	1.30	90s	117.1	49.4 GiB	~113.6
jitter_improvement_pct	1.23	82s	100.6	44.2 GiB	~97.8
validators listing	0.74	10s	7.5	3.9 GiB	~7.5
bandwidth series	0.68	41s	27.9	7.6 GiB	~27.9
edge scoreboard	0.61	75s	45.9	15.7 GiB	~402 (24h variant)

Top 10 shapes were only 5.85 of the 12.48 cores — the other 53% is the long tail of remaining page-cache entries. No single query is at fault; the refresh loop is.

Local instances also refresh faster than prod: metro-pair at 90s here versus prod's 107s, because a local cycle has less competing work and completes sooner.

Why this is a documented path, not a mistake

lake_dev_reader is not an ad-hoc credential. It is named in three places in this repo:

• README.md:166 — REMOTE_CH_USER=lake_dev_reader
• .env.example:176 — same
• scripts/setup-remote-tables.sh:60 — printed as setup guidance

Nothing in any of them warns that running the stack against prod imposes production-scale load. A developer following the README exactly reproduces this.

Proposed fixes

1. Disable the page-cache worker for local --use-remote runs. This is the actual fix — the refresh loop is where all 12 cores went. --no-worker already exists (api/main.go:244, help text "for prod where it runs standalone"). A local instance pointed at prod almost never needs a background loop pre-warming 39 entries. Either default it on when --use-remote is set, or document it prominently.

2. Quota the user as a backstop, so no future instance can do this regardless of flags:

ALTER USER lake_dev_reader SETTINGS
    max_execution_time = 60,
    max_bytes_to_read  = 20000000000;   -- 20 GB

The two heaviest shapes read 49.4 and 44.2 GiB per run, so 20 GB stops them outright rather than slowing them.

⚠️ Check this against our own traffic first. Five of our pods share this credential — lake-api-558cf9695f-5ngwc, lake-api-79796bf5c8-vx9sg, lake-indexer-7f748ddb87-57n9j, lake-indexer-5999968459-7hhnf, lake-indexer-7559c77454-xxzl7 — for the remoteSecure() proxy tables described in CLAUDE.md:50. They totalled 2.29 cores over 7,824 queries, far lighter than the local instance, so a 20 GB ceiling should clear them. Verify before applying. The cleaner alternative is a separate per-developer user, leaving lake_dev_reader for our own services.

3. Warn in the README at the point where the credential is introduced.

Note on attribution

Identifying the source took several hours and three wrong conclusions. The trap worth recording: system.query_log.initial_address is self-reported by the client; address is the real TCP peer. The local pod reported 10.42.0.19, which looked like an in-VPC address and sent the investigation through AWS route tables, VPC peerings and PrivateLink endpoints — none of which were involved. address gave the true external IP immediately, and client_hostname named the pod. Query those two first.

Related: malbeclabs/infra#1123 — ClickHouse users are created by hand with no version control or audit trail, so there was no record of who provisioned this credential or when.

[nikw9944]

Update 2026-08-21: recurred from a different source — this is not a local-dev problem, it's a PR preview environment problem

lake_dev_reader is back, larger than before, and it is now the single biggest CPU consumer on the prod cluster — ahead of lake_api.

Measured over a clean 30-minute window (19:05–19:35Z) with three preview environments concurrently up:

user	cores
lake_dev_reader	19.72
lake_api	18.88
refreshable MVs (all tenants)	5.74
lake_indexer	3.83

That is ~39% of a ~50-core cluster, and none of it is production traffic.

It is the PR preview environments

address was a single IP — 44.218.193.116 — which resolves entirely inside our own infrastructure:

44.218.193.116
  -> eipalloc-0c1c5e38b45ddf00f
  -> eni-075f6ae79e9a077a4
  -> nat-0d800605c2600cd45          (NAT gateway)
  -> vpc-0050b44dc81a1d586          (doublezero-shared)
  -> EKS cluster doublezero-shared, account 879381273509, us-east-1a

Because every pod in the cluster egresses through that one NAT, address cannot separate them. initial_address can — it carries the pod IP. Mapping it against live pod IPs:

pod IP	workload	cores	read / 30 min
10.0.36.213	lake-pr-798 lake-api	6.99	4.18 TiB
10.0.36.212	lake-pr-797 lake-api	6.46	4.88 TiB
10.0.46.155	lake-pr-799 lake-api	3.97	3.06 TiB
10.0.17.179	lake-staging lake-api	2.24	1.49 TiB
10.0.16.20/.22, 10.0.29.227, 10.0.22.34	the four lake-indexer pods	0.01–0.02 each	~5 GiB each

Each open PR costs 4–7 cores of production ClickHouse. The indexers are negligible; it is lake-api's page-cache loop, exactly as in the original report.

Why every preview env does this

lake-api in a preview namespace connects to clickhouse:9000 in its own namespace — it looks local. But lake-indexer's init container wires that local ClickHouse straight to prod:

initContainer: setup-remote-tables  --setup-remote-tables=external
  REMOTE_CH_USER = lake_dev_reader
  REMOTE_CH_HOST = btjr1b5uy8.us-east-1.aws.clickhouse.cloud

The tables are remote engines, so every page-cache refresh in the preview environment executes on the production cluster as lake_dev_reader. Nothing in the preview env is misconfigured — it is reading precisely the tables it was told to read. Confirmed identical in lake-pr-407, lake-pr-797, lake-pr-798, lake-pr-799.

This rescopes fix #2 (the quota)

The quota as originally written cannot be applied as the primary fix. lake_dev_reader is the shared identity for every preview environment and lake-staging, whose 2.24 cores are legitimate. A ceiling tight enough to bound a preview env's 7 cores also applies to staging, and staging is already failing to complete page-cache cycles inside its budget (225 budget-exhausted events in 24h as of 15:24Z today). Tightening it there makes that worse and generates more non-actionable log noise.

Fix #1 is the whole fix, and it belongs in the PR-env template, not in local dev docs. A PR preview almost certainly does not need 39 pre-warmed cache entries. Pass --no-worker to lake-api in the preview-environment manifests. That removes 4–7 cores per open PR at the source and needs no ClickHouse-side change at all.

Keep the quota as a backstop only, and only after splitting the credential — a per-preview-env or per-developer user, leaving lake_dev_reader to our own services. Filed separately if wanted.

Two related findings

The cluster scaled up during this. Per-replica vCPU went 29 -> 58 (all three replicas replaced 17:39–17:42Z), roughly doubling compute cost. The preview environments were created at 15:22Z, 16:33Z and 16:49Z, and the load ramp is visible from 17:45Z. I cannot prove causation: system.query_log is node-local, so every replica replacement wipes it, and all history before 17:39Z is gone. That is worth its own issue — any CPU baseline we care about has to be exported at capture time, or a scale event destroys the evidence. It has now cost us the 16-hour baseline behind doublezero-data#4 and the placement measurement behind malbeclabs/infra#2219.

One query read 1.27 TiB. At 18:19:51Z, 156s wall, 6,096 cpu-seconds — a toDate(fromUnixTimestamp64Nano(recv_ts_ns)) group-by with uniqExact(instrument_id) over feeds data, no partition pruning. A single preview-env query burning ~2% of an hour of cluster CPU.

Correction to the attribution note

The original note said to query address first. That was right for a laptop and wrong here: behind a shared NAT gateway, address collapses every pod to one IP and tells you nothing. The rule is to read both — address for the real network origin, initial_address for the client's own identity — and to treat neither as authoritative alone. initial_address is self-reported and can mislead; address is real but may be a shared egress point.

Also: lake-pr-407 is a leaked namespace

134 days old, lake-indexer in Init:CrashLoopBackOff, still holding clickhouse-0, neo4j-0, postgres-0, temporal, temporal-ui and a lake-api. Near-zero ClickHouse load right now, but it is consuming EKS capacity long after its PR closed. Worth deleting, and worth finding out why teardown did not fire.