Repository navigation
perf: use the build parameter index in GetTemplateParameterInsights - #31765
dylanhuff-at-coder merged 3 commits into
Conversation
…rameterInsights The final join matched build parameters with `utp.workspace_build_ids @> ARRAY[wbp.workspace_build_id]`, which leaves only `name` as a usable join key. Postgres ends up scanning every row in workspace_build_parameters and running the array check per row, so the query gets slower as build history grows no matter how small the window is. The insights metrics collector runs it every 5 minutes on every replica. Unnest the build IDs and join on (workspace_build_id, name) instead so the existing unique index serves the lookup. Each build ID shows up at most once per group, so the result is the same. Adds TestGetTemplateParameterInsights to pin grouping across templates, latest-in-window build selection and per-value counts. It passes against both the old and new query.
…s in the Prometheus example Every coderd replica exports the same coderd_insights_* values, so the `sum by (template_name)` example read N times too high on an N replica deployment. Take the max per app first, then sum per template and organization, and add a note with the other metric family notes that these metrics should be aggregated across replicas with max.
|
/coder-agents-review Please limit the review to P0, P1, and P2 issues only. |
|
Chat: Review posted | View chat Review history
deep-review v0.13.0 | Round 1 | Last posted: Round 1, no findings, COMMENT. Review Finding inventoryFinding inventory: PR #31765Findings
Contested and acknowledgedRound logRound 1Round type: round 1, full panel. Netero first pass: 1 P3, 1 Note (no P0, panel proceeded). Law not run (effective additions 128). Panel: knuckle, ging-go, killua, pen-botter, pariston, mafuuu, bisky, komugi, gon, leorio, razor, plus wildcard kite. Size 12 (multi-domain: SQL, Go, docs). Mafu-san not selected (commits authored by a human). Cross-check: no P0-P2 findings. Gon's two P2 test comment findings (CRF-3, CRF-4) downgraded to Nit. Keep-argument written first: redundant comments drift from the code and the joinIDs name hides the sort. Downgrade reason: both comments are accurate today, the helper is six lines, and no behavior or reader decision depends on them. The PR author requested P0-P2 findings only, so every below-P2 item is recorded here and not posted. SQL equivalence verified by Knuckle, Pariston, and Netero via the unique constraints on workspace_builds(workspace_id, build_number) and template_version_parameters(template_version_id, name). Pariston measured no regression at wide windows (PG16 synthetic, full-window: new 20.7 s vs old >170 s). Pre-push hook claim (CRF-7) verified by orchestrator at scripts/githooks/pre-push:23,108. Reviewed against 42640dc..7e80fc4. About deep-reviewCRF = Coder Review Finding (P0-P4, Nit, Note)
|
There was a problem hiding this comment.
This PR replaces the @> array join in GetTemplateParameterInsights with an unnest equality join on (workspace_build_id, name), adds TestGetTemplateParameterInsights, and fixes the replica aggregation in the Prometheus docs example. No P0, P1, or P2 findings; per your request, lower-severity items are not posted. The panel confirmed the result is unchanged: the unique constraints on workspace_builds(workspace_id, build_number) and template_version_parameters(template_version_id, name) keep build IDs distinct within each group, so unnest cannot double count.
Out of scope (needs a ticket or explicit acceptance by a human):
scripts/githooks/pre-push:108:echo "$changed" | grep -qE ...runs underset -o pipefail; with a large change listgrep -qexits early,echoexits 141, and the hook prints "no Go/TS changes, skipping tests" for pushes that change Go files.coderd/prometheusmetrics/insights/metricscollector.go:87: each replica's 5 minute window is relative to its owntime.Now()and ticker start, socoderd_insights_*values differ between replicas for the same period and no cross-replica aggregation gives one exact figure.
🤖 This review was automatically generated with Coder Agents.
BobbyHo
left a comment
There was a problem hiding this comment.
lgtm, nice optimization!
Documentation CheckBlocking
Findings for agents- id: accuracy-1
persona: accuracy
class: wrong
blocking: true
file: docs/admin/integrations/prometheus.md
line: 102
quote: "Every `coderd` replica exports the same `coderd_insights_*` values, so aggregate across replicas with `max`, not `sum`."
evidence:
- cli/server.go:347
- coderd/prometheusmetrics/insights/metricscollector.go:83-91
rule: null
problem: A reader is told replicas export identical values, but each replica computes over its own unaligned [now-5m, now] window, so values can differ across replicas at window boundaries.
fix: "Each `coderd` replica exports the `coderd_insights_*` metrics over its own rolling window, so aggregate across replicas with `max`, not `sum`."
verified: trueAutomated review via Coder Agents. Reply to a finding's thread with the reason if the finding is wrong. |
…lica windows Each replica computes the coderd_insights_* series over its own rolling 5 minute window, so the values aren't guaranteed to match across replicas. The guidance to aggregate with max stays the same.
…31766) Adds an index on `workspace_app_stats(session_ended_at)`. The table had no time index and is never purged, so the insights collector's app usage query scanned all of it every 5 minutes on every replica. On 3M synthetic rows that query went from 124 ms to 19 ms. **NOTE**: the index build holds a lock that blocks app stat writes until the migration batch commits (about 1.4 to 3.4 s per 3M rows in testing), and active session upserts stop being HOT updates. This is separate from #31765 so that one can be backported without a migration. dbrollup's `UpsertTemplateUsageStats` still needs its own query change to use this index on Postgres 17+ (noted on [CORE-776](https://linear.app/codercom/issue/CORE-776)). The migration number may need a bump at merge time. I skipped the pre-push hook for this push since `make pre-push` also fails on clean `main` in my workspace. This branch only failed in tests it doesn't touch. <details><summary>Failing pre-push tests</summary> Clean `main` (42640dc), two runs: 9 Go and 122 JS failures, then 6 Go and 104 JS. This branch, Go: `agent/agentscripts` TestCloseWaitsForCanceledStartReports, TestExecuteCanceledScriptReportsCanceled and TestStartCronDuringCloseDoesNotBlockClose, plus `agent/agentssh` TestNewServer_CloseActiveConnections, TestSSHShutdownReason and Test_sessionStart_orphan. JS: timeouts across 19 files, mostly `AgentsPageLayout`, `AgentCreatePage`, `ChatTopBar` and `WorkspacePage`. </details> Part of [CORE-439](https://linear.app/codercom/issue/CORE-439).
Speeds up
GetTemplateParameterInsights, which the insights collector runs every 5 minutes on every replica. The@>array join could only hash onname, so every run scanned all ofworkspace_build_parameters. Unnesting the build IDs and joining on(workspace_build_id, name)lets the existing unique index serve it, and the results don't change since each build ID shows up at most once per group.On 8.4M synthetic build parameters the collector's 5 minute window went from 5,193 ms to about 31 ms.
TestGetTemplateParameterInsightsis new and passes on both the old and new SQL. This also fixes the Prometheus docs example, which summed per-replica copies of the same metric.I split the
workspace_app_statsindex into #31766 so this one has no migration and can be backported to 2.34 through 2.39 (2.34 to 2.38 without the docs hunk).The pre-push hook skipped tests on this push because of a stale
origin/mainin my checkout. An earlier full run only failed inagent/andsite/.Part of CORE-439.