Skip to content

Commit c0b0216

Browse files
mafredrimafredri
committed
feat(coderd/database): rewrite GetUserLatencyInsights to use template_usage_stats 
1 parent 59c0a23 commit c0b0216

File tree

6 files changed

+55
-38
lines changed

6 files changed

+55
-38
lines changed

‎coderd/database/dbmem/dbmem.go

+2-1
Original file line numberDiff line numberDiff line change
@@ -4056,7 +4056,8 @@ func (q *FakeQuerier) GetUserLatencyInsights(_ context.Context, arg database.Get
40564056
AvatarURL: user.AvatarURL,
40574057
TemplateIDs: templateIDs,
40584058
WorkspaceConnectionLatency50: tryPercentile(latencies, 50),
4059-
WorkspaceConnectionLatency95: tryPercentile(latencies, 95),
4059+
WorkspaceConnectionLatency90: tryPercentile(latencies, 90),
4060+
WorkspaceConnectionLatency99: tryPercentile(latencies, 99),
40604061
}
40614062
rows = append(rows, row)
40624063
}

‎coderd/database/queries.sql.go

+25-18
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

+20-15
Original file line numberDiff line numberDiff line change
@@ -4,22 +4,27 @@
44
-- template_ids, meaning only user data from workspaces based on those templates
55
-- will be included.
66
SELECT
7-
workspace_agent_stats.user_id,
8-
users.username,
9-
users.avatar_url,
10-
array_agg(DISTINCT template_id)::uuid[] AS template_ids,
11-
coalesce((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY connection_median_latency_ms)), -1)::FLOAT AS workspace_connection_latency_50,
12-
coalesce((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY connection_median_latency_ms)), -1)::FLOAT AS workspace_connection_latency_95
13-
FROM workspace_agent_stats
14-
JOIN users ON (users.id = workspace_agent_stats.user_id)
7+
tus.user_id,
8+
u.username,
9+
u.avatar_url,
10+
array_agg(DISTINCT tus.template_id)::uuid[] AS template_ids,
11+
COALESCE((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tus.median_latency_ms)), -1)::float AS workspace_connection_latency_50,
12+
COALESCE((PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY tus.median_latency_ms)), -1)::float AS workspace_connection_latency_90,
13+
COALESCE((PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY tus.median_latency_ms)), -1)::float AS workspace_connection_latency_99
14+
FROM
15+
template_usage_stats tus
16+
JOIN
17+
users u
18+
ON
19+
u.id = tus.user_id
1520
WHERE
16-
workspace_agent_stats.created_at >= @start_time
17-
AND workspace_agent_stats.created_at < @end_time
18-
AND workspace_agent_stats.connection_median_latency_ms > 0
19-
AND workspace_agent_stats.connection_count > 0
20-
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
21-
GROUP BY workspace_agent_stats.user_id, users.username, users.avatar_url
22-
ORDER BY user_id ASC;
21+
tus.start_time >= @start_time::timestamptz
22+
AND tus.end_time <= @end_time::timestamptz
23+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN tus.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
24+
GROUP BY
25+
tus.user_id, u.username, u.avatar_url
26+
ORDER BY
27+
tus.user_id ASC;
2328

2429
-- name: GetUserActivityInsights :many
2530
-- GetUserActivityInsights returns the ranking with top active users.

‎coderd/insights.go

+2-1
Original file line numberDiff line numberDiff line change
@@ -215,7 +215,8 @@ func (api *API) insightsUserLatency(rw http.ResponseWriter, r *http.Request) {
215215
AvatarURL: row.AvatarURL,
216216
LatencyMS: codersdk.ConnectionLatency{
217217
P50: row.WorkspaceConnectionLatency50,
218-
P95: row.WorkspaceConnectionLatency95,
218+
P90: row.WorkspaceConnectionLatency90,
219+
P99: row.WorkspaceConnectionLatency99,
219220
},
220221
})
221222
}

‎coderd/insights_test.go

+4-2
Original file line numberDiff line numberDiff line change
@@ -210,7 +210,8 @@ func TestUserLatencyInsights(t *testing.T) {
210210
logger := slogtest.Make(t, nil)
211211
client := coderdtest.New(t, &coderdtest.Options{
212212
IncludeProvisionerDaemon: true,
213-
AgentStatsRefreshInterval: time.Millisecond * 100,
213+
AgentStatsRefreshInterval: time.Millisecond * 50,
214+
DBRollupInterval: time.Millisecond * 100,
214215
})
215216

216217
// Create two users, one that will appear in the report and another that
@@ -290,7 +291,8 @@ func TestUserLatencyInsights(t *testing.T) {
290291
require.Len(t, userLatencies.Report.Users, 1, "want only 1 user")
291292
require.Equal(t, userLatencies.Report.Users[0].UserID, user.UserID, "want user id to match")
292293
assert.Greater(t, userLatencies.Report.Users[0].LatencyMS.P50, float64(0), "want p50 to be greater than 0")
293-
assert.Greater(t, userLatencies.Report.Users[0].LatencyMS.P95, float64(0), "want p95 to be greater than 0")
294+
assert.Greater(t, userLatencies.Report.Users[0].LatencyMS.P90, float64(0), "want p90 to be greater than 0")
295+
assert.Greater(t, userLatencies.Report.Users[0].LatencyMS.P99, float64(0), "want p99 to be greater than 0")
294296
}
295297

296298
func TestUserLatencyInsights_BadRequest(t *testing.T) {

‎codersdk/insights.go

+2-1
Original file line numberDiff line numberDiff line change
@@ -98,7 +98,8 @@ type UserActivity struct {
9898
// ConnectionLatency shows the latency for a connection.
9999
type ConnectionLatency struct {
100100
P50 float64 `json:"p50" example:"31.312"`
101-
P95 float64 `json:"p95" example:"119.832"`
101+
P90 float64 `json:"p90" example:"119.832"`
102+
P99 float64 `json:"p99" example:"432.34"`
102103
}
103104

104105
type UserLatencyInsightsRequest struct {

0 commit comments

Comments
 (0)