Skip to content

Commit a8ed689

Browse files
mafredrimafredri
authored
feat(coderd/database): use template_usage_stats in GetUserLatencyInsights query (coder#12671)
This PR updates the `GetUserLatencyInsights` query to use rolled up `template_usage_stats` instead of raw agent and app stats.
1 parent 5738a03 commit a8ed689

File tree

3 files changed

+71
-55
lines changed

3 files changed

+71
-55
lines changed

‎coderd/database/dbmem/dbmem.go

Lines changed: 32 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -4300,27 +4300,44 @@ func (q *FakeQuerier) GetUserLatencyInsights(_ context.Context, arg database.Get
43004300
q.mutex.RLock()
43014301
defer q.mutex.RUnlock()
43024302

4303+
/*
4304+
SELECT
4305+
tus.user_id,
4306+
u.username,
4307+
u.avatar_url,
4308+
array_agg(DISTINCT tus.template_id)::uuid[] AS template_ids,
4309+
COALESCE((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tus.median_latency_ms)), -1)::float AS workspace_connection_latency_50,
4310+
COALESCE((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY tus.median_latency_ms)), -1)::float AS workspace_connection_latency_95
4311+
FROM
4312+
template_usage_stats tus
4313+
JOIN
4314+
users u
4315+
ON
4316+
u.id = tus.user_id
4317+
WHERE
4318+
tus.start_time >= @start_time::timestamptz
4319+
AND tus.end_time <= @end_time::timestamptz
4320+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN tus.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
4321+
GROUP BY
4322+
tus.user_id, u.username, u.avatar_url
4323+
ORDER BY
4324+
tus.user_id ASC;
4325+
*/
4326+
43034327
latenciesByUserID := make(map[uuid.UUID][]float64)
4304-
seenTemplatesByUserID := make(map[uuid.UUID]map[uuid.UUID]struct{})
4305-
for _, s := range q.workspaceAgentStats {
4306-
if len(arg.TemplateIDs) > 0 && !slices.Contains(arg.TemplateIDs, s.TemplateID) {
4307-
continue
4308-
}
4309-
if !arg.StartTime.Equal(s.CreatedAt) && (s.CreatedAt.Before(arg.StartTime) || s.CreatedAt.After(arg.EndTime)) {
4310-
continue
4311-
}
4312-
if s.ConnectionCount == 0 {
4328+
seenTemplatesByUserID := make(map[uuid.UUID][]uuid.UUID)
4329+
for _, stat := range q.templateUsageStats {
4330+
if stat.StartTime.Before(arg.StartTime) || stat.EndTime.After(arg.EndTime) {
43134331
continue
43144332
}
4315-
if s.ConnectionMedianLatencyMS <= 0 {
4333+
if len(arg.TemplateIDs) > 0 && !slices.Contains(arg.TemplateIDs, stat.TemplateID) {
43164334
continue
43174335
}
43184336

4319-
latenciesByUserID[s.UserID] = append(latenciesByUserID[s.UserID], s.ConnectionMedianLatencyMS)
4320-
if seenTemplatesByUserID[s.UserID] == nil {
4321-
seenTemplatesByUserID[s.UserID] = make(map[uuid.UUID]struct{})
4337+
if stat.MedianLatencyMs.Valid {
4338+
latenciesByUserID[stat.UserID] = append(latenciesByUserID[stat.UserID], stat.MedianLatencyMs.Float64)
43224339
}
4323-
seenTemplatesByUserID[s.UserID][s.TemplateID] = struct{}{}
4340+
seenTemplatesByUserID[stat.UserID] = uniqueSortedUUIDs(append(seenTemplatesByUserID[stat.UserID], stat.TemplateID))
43244341
}
43254342

43264343
tryPercentile := func(fs []float64, p float64) float64 {
@@ -4333,15 +4350,6 @@ func (q *FakeQuerier) GetUserLatencyInsights(_ context.Context, arg database.Get
43334350

43344351
var rows []database.GetUserLatencyInsightsRow
43354352
for userID, latencies := range latenciesByUserID {
4336-
sort.Float64s(latencies)
4337-
templateIDSet := seenTemplatesByUserID[userID]
4338-
templateIDs := make([]uuid.UUID, 0, len(templateIDSet))
4339-
for templateID := range templateIDSet {
4340-
templateIDs = append(templateIDs, templateID)
4341-
}
4342-
slices.SortFunc(templateIDs, func(a, b uuid.UUID) int {
4343-
return slice.Ascending(a.String(), b.String())
4344-
})
43454353
user, err := q.getUserByIDNoLock(userID)
43464354
if err != nil {
43474355
return nil, err
@@ -4350,7 +4358,7 @@ func (q *FakeQuerier) GetUserLatencyInsights(_ context.Context, arg database.Get
43504358
UserID: userID,
43514359
Username: user.Username,
43524360
AvatarURL: user.AvatarURL,
4353-
TemplateIDs: templateIDs,
4361+
TemplateIDs: seenTemplatesByUserID[userID],
43544362
WorkspaceConnectionLatency50: tryPercentile(latencies, 50),
43554363
WorkspaceConnectionLatency95: tryPercentile(latencies, 95),
43564364
}

‎coderd/database/queries.sql.go

Lines changed: 20 additions & 16 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

Lines changed: 19 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -4,22 +4,26 @@
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.95) WITHIN GROUP (ORDER BY tus.median_latency_ms)), -1)::float AS workspace_connection_latency_95
13+
FROM
14+
template_usage_stats tus
15+
JOIN
16+
users u
17+
ON
18+
u.id = tus.user_id
1519
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;
20+
tus.start_time >= @start_time::timestamptz
21+
AND tus.end_time <= @end_time::timestamptz
22+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN tus.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
23+
GROUP BY
24+
tus.user_id, u.username, u.avatar_url
25+
ORDER BY
26+
tus.user_id ASC;
2327

2428
-- name: GetUserActivityInsights :many
2529
-- GetUserActivityInsights returns the ranking with top active users.

0 commit comments

Comments
 (0)