Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita8ed689

Browse files
authored
feat(coderd/database): usetemplate_usage_stats inGetUserLatencyInsights query (#12671)
This PR updates the `GetUserLatencyInsights` query to use rolled up `template_usage_stats` instead of raw agent and app stats.
1 parent5738a03 commita8ed689

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
deferq.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:=rangeq.workspaceAgentStats {
4306-
iflen(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-
ifs.ConnectionCount==0 {
4328+
seenTemplatesByUserID:=make(map[uuid.UUID][]uuid.UUID)
4329+
for_,stat:=rangeq.templateUsageStats {
4330+
ifstat.StartTime.Before(arg.StartTime)||stat.EndTime.After(arg.EndTime) {
43134331
continue
43144332
}
4315-
ifs.ConnectionMedianLatencyMS<=0 {
4333+
iflen(arg.TemplateIDs)>0&&!slices.Contains(arg.TemplateIDs,stat.TemplateID) {
43164334
continue
43174335
}
43184336

4319-
latenciesByUserID[s.UserID]=append(latenciesByUserID[s.UserID],s.ConnectionMedianLatencyMS)
4320-
ifseenTemplatesByUserID[s.UserID]==nil {
4321-
seenTemplatesByUserID[s.UserID]=make(map[uuid.UUID]struct{})
4337+
ifstat.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,pfloat64)float64 {
@@ -4333,15 +4350,6 @@ func (q *FakeQuerier) GetUserLatencyInsights(_ context.Context, arg database.Get
43334350

43344351
varrows []database.GetUserLatencyInsightsRow
43354352
foruserID,latencies:=rangelatenciesByUserID {
4336-
sort.Float64s(latencies)
4337-
templateIDSet:=seenTemplatesByUserID[userID]
4338-
templateIDs:=make([]uuid.UUID,0,len(templateIDSet))
4339-
fortemplateID:=rangetemplateIDSet {
4340-
templateIDs=append(templateIDs,templateID)
4341-
}
4342-
slices.SortFunc(templateIDs,func(a,b uuid.UUID)int {
4343-
returnslice.Ascending(a.String(),b.String())
4344-
})
43454353
user,err:=q.getUserByIDNoLock(userID)
43464354
iferr!=nil {
43474355
returnnil,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 aboutcustomizing 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)::FLOATAS workspace_connection_latency_50,
12-
coalesce((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY connection_median_latency_ms)),-1)::FLOATAS workspace_connection_latency_95
13-
FROM workspace_agent_stats
14-
JOIN usersON (users.id=workspace_agent_stats.user_id)
7+
tus.user_id,
8+
u.username,
9+
u.avatar_url,
10+
array_agg(DISTINCTtus.template_id)::uuid[]AS template_ids,
11+
COALESCE((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BYtus.median_latency_ms)),-1)::floatAS workspace_connection_latency_50,
12+
COALESCE((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BYtus.median_latency_ms)),-1)::floatAS 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-
ANDworkspace_agent_stats.created_at< @end_time
18-
ANDworkspace_agent_stats.connection_median_latency_ms>0
19-
ANDworkspace_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 BYworkspace_agent_stats.user_id,users.username,users.avatar_url
22-
ORDER BYuser_idASC;
20+
tus.start_time>= @start_time::timestamptz
21+
ANDtus.end_time<= @end_time::timestamptz
22+
ANDCASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THENtus.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_idASC;
2327

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

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp