Skip to content

fix: correct connection_median_latency_ms in query #15086

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 2 commits into from
Oct 17, 2024
Merged

fix: correct connection_median_latency_ms in query #15086

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
e0464a9
fix: correct connection_median_latency_ms in query
f0ssel Oct 15, 2024
47460be
fix insights
f0ssel Oct 16, 2024
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
23 changes: 8 additions & 15 deletions coderd/database/queries.sql.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

2 changes: 1 addition & 1 deletion coderd/database/queries/insights.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -661,7 +661,7 @@ WITH
AND date_trunc('minute', was.created_at) = mb.minute_bucket
AND was.template_id = mb.template_id
AND was.user_id = mb.user_id
AND was.connection_median_latency_ms >= 0
Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This is the bug fix, in other queries that use connection_median_latency_ms we always filter our the 0 values to rule out any usage stats.

AND was.connection_median_latency_ms > 0
GROUP BY
mb.start_time, mb.template_id, mb.user_id
)
Expand Down
21 changes: 7 additions & 14 deletions coderd/database/queries/workspaceagentstats.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -188,8 +188,9 @@ WITH agent_stats AS (
coalesce((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY connection_median_latency_ms)), -1)::FLOAT AS workspace_connection_latency_50,
coalesce((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY connection_median_latency_ms)), -1)::FLOAT AS workspace_connection_latency_95
FROM workspace_agent_stats
-- The greater than 0 is to support legacy agents that don't report connection_median_latency_ms.
WHERE workspace_agent_stats.created_at > $1 AND connection_median_latency_ms > 0 GROUP BY user_id, agent_id, workspace_id, template_id
-- The greater than 0 is to support legacy agents that don't report connection_median_latency_ms.
WHERE workspace_agent_stats.created_at > $1 AND connection_median_latency_ms > 0
GROUP BY user_id, agent_id, workspace_id, template_id
), latest_agent_stats AS (
SELECT
a.agent_id,
Expand Down Expand Up @@ -332,9 +333,11 @@ WITH agent_stats AS (
agent_id,
workspace_id,
coalesce(SUM(rx_bytes), 0)::bigint AS rx_bytes,
coalesce(SUM(tx_bytes), 0)::bigint AS tx_bytes
coalesce(SUM(tx_bytes), 0)::bigint AS tx_bytes,
coalesce(MAX(connection_median_latency_ms), 0)::float AS connection_median_latency_ms
FROM workspace_agent_stats
WHERE workspace_agent_stats.created_at > $1
-- The greater than 0 is to support legacy agents that don't report connection_median_latency_ms.
WHERE workspace_agent_stats.created_at > $1 AND connection_median_latency_ms > 0
Comment on lines +339 to +340
Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This is another fix I found along the way where we weren't filtering out the stats with 0 or selecting only recent stats.

GROUP BY user_id, agent_id, workspace_id
), latest_agent_stats AS (
SELECT
Expand All @@ -349,12 +352,6 @@ WITH agent_stats AS (
-- spread across multiple rows.
WHERE usage = true AND created_at > now() - '1 minute'::interval
GROUP BY user_id, agent_id, workspace_id
), latest_agent_latencies AS (
SELECT
agent_id,
coalesce(MAX(connection_median_latency_ms), 0)::float AS connection_median_latency_ms
FROM workspace_agent_stats
GROUP BY user_id, agent_id, workspace_id
)
SELECT
users.username, workspace_agents.name AS agent_name, workspaces.name AS workspace_name, rx_bytes, tx_bytes,
Expand All @@ -370,10 +367,6 @@ LEFT JOIN
latest_agent_stats
ON
agent_stats.agent_id = latest_agent_stats.agent_id
JOIN
latest_agent_latencies
ON
agent_stats.agent_id = latest_agent_latencies.agent_id
JOIN
users
ON
Expand Down
Loading