Skip to content

Commit 11efd8b

Browse files
mafredrimafredri
committed
feat(coderd/database): rewrite GetUserLatencyInsights to use template_usage_stats 
1 parent 02d0f24 commit 11efd8b

File tree

11 files changed

+79
-47
lines changed

11 files changed

+79
-47
lines changed

‎coderd/apidoc/docs.go

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

‎coderd/apidoc/swagger.json

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

‎coderd/database/dbmem/dbmem.go

+2-1
Original file line numberDiff line numberDiff line change
@@ -4350,7 +4350,8 @@ func (q *FakeQuerier) GetUserLatencyInsights(_ context.Context, arg database.Get
43504350
AvatarURL: user.AvatarURL,
43514351
TemplateIDs: templateIDs,
43524352
WorkspaceConnectionLatency50: tryPercentile(latencies, 50),
4353-
WorkspaceConnectionLatency95: tryPercentile(latencies, 95),
4353+
WorkspaceConnectionLatency90: tryPercentile(latencies, 90),
4354+
WorkspaceConnectionLatency99: tryPercentile(latencies, 99),
43544355
}
43554356
rows = append(rows, row)
43564357
}

‎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
@@ -211,7 +211,8 @@ func TestUserLatencyInsights(t *testing.T) {
211211
logger := slogtest.Make(t, nil)
212212
client := coderdtest.New(t, &coderdtest.Options{
213213
IncludeProvisionerDaemon: true,
214-
AgentStatsRefreshInterval: time.Millisecond * 100,
214+
AgentStatsRefreshInterval: time.Millisecond * 50,
215+
DBRollupInterval: time.Millisecond * 100,
215216
})
216217

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

297299
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 {

‎docs/api/insights.md

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

‎docs/api/schemas.md

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

‎site/src/api/typesGenerated.ts

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

0 commit comments

Comments
 (0)