bug: GetWorkspaceByAgentID is called over a million times a day on dogfood

[spikecurtis]

https://console.cloud.google.com/sql/instances/dogfood-v2/insights;database=coder;start=2025-06-12T05:55:18.232Z;end=2025-06-19T05:55:18.232Z;trace=ac9bc1c4b501d64bb6ad790ae5a8633a;span=c6909b51da0c6db3;query_hash=3814061901782322789;sort_by=TOTAL_EXEC_TIME?inv=1&invt=Ab0giQ&project=coder-dogfood-v2

[ethanndickson]

I ran a 1.2k workspace scaletest yesterday and the frequency at which this query was called (350+ q/s), as part of the agent stats loop, was responsible for driving the majority of load on the database (according to CloudSQL query insights). Let me know if there's any data from that scaletest that would help your investigation here.

[mafredri]

@ethanndickson That's very helpful insight, thank you!

That sounds a bit high for just metadata as it's supposed to be batched up. Can you confirm what the template is configured with in terms of how many stats are defined/agent and what their update interval is?

Potentially we could introduce some caching into the coderd/agentapi to avoid constant lookups. Most agentapi endpoints call AgentFn and I guess that it indiscriminately does a DB lookup.

[ethanndickson]

@mafredri
CODER_AGENT_STATS_REFRESH_INTERVAL is the default at 30 seconds, so that should only be contributing ~40q/s.
The template is just a single agent and I don't see anything that would be configuring it to refresh any more often than that.
https://github.com/coder/scaletest/blob/main/terraform/coder_templates.tf

Taking a look at one specific trace (that lasts the entire lifetime of the agent, apparently), it looks like it's called in a loop with FetchMemoryResourceMonitorsByAgentID, even though the template doesn't define any resource monitors.

EDIT: Though FWICT looking at the code, resource monitoring doesn't call this query 🤔

[cstyan]

Had some time to look into this one today. In the last week we've called GetWorkspaceByAgentID > 27M times.

Looking at the code + our own profiling data, I think the overall call volume is mostly coming from the resource usage collection/monitoring in agentapi. I ran some queries against the DB via the read-only template, our peak agent count is between 190 and 220 per day. If we just assume ~200 running agents, and 2 calls for resources (memory and volumes) every 10s (default here), that's ~40 calls per second which is just about 24.2M per week (90% of total). Even if our average agent count is only 100 that's still 12M or 44% of the calls.

The resource monitoring code calls some dbauthz functions( https://github.com/coder/coder/blob/main/coderd/database/dbauthz/dbauthz.go#L1874-L1886 and https://github.com/coder/coder/blob/main/coderd/database/dbauthz/dbauthz.go#L1906-L1918) which call the GetWorkspaceByAgentID. This seems to be only for authorization purposes, is that necessary? We already store the workspace ID one the resource monitoring api struct.

EDIT: I think we have a few options here so we can keep the authorize call.

• we already have the agent ID and workspace ID on the API struct, AFAICT we need the owner ID and organization ID as well
• we can either add these to the struct and have them be passed in at instantiation time here
• we can denormalize the workspace_agents table to also store the workspace ID, owner ID, and organization ID
• something else ACL related?

[spikecurtis]

Yeah, agents have a persistent connection to the agentapi. The queries for the resource monitors are called each time the agent pushes an update, but the resource monitors are set at build time and can't be changed --- so there is no reason to be querying them each time. We should be able to drastically reduce not just the calls to GetWorkspaceByAgentID but the calls to fetch the monitors by just querying them once when the agent initially connects and then re-using them for the lifetime of the agentapi connection.

No need to get into authz changes for this one.

[cstyan]

The call volume for this query isn't completely resolved, prior to this fix we had ~5.1M calls per day and now afterwards we have ~3.8M calls per day. Another major source of call volume is via the stats API, which I will open another PR for.