Skip to content

Commit 04d5e3f

Browse files
mafredrimafredri
authored
fix(coderd): rewrite template insights query for speed and fix intervals (coder#9300)
1 parent d00817e commit 04d5e3f

8 files changed

Lines changed: 38 additions & 58 deletions

‎coderd/database/dbfake/dbfake.go‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2340,22 +2340,22 @@ func (q *FakeQuerier) GetTemplateInsights(_ context.Context, arg database.GetTem
23402340
if appUsageIntervalsByUser[s.UserID] == nil {
23412341
appUsageIntervalsByUser[s.UserID] = make(map[time.Time]*database.GetTemplateInsightsRow)
23422342
}
2343-
t := s.CreatedAt.Truncate(5 * time.Minute)
2343+
t := s.CreatedAt.Truncate(time.Minute)
23442344
if _, ok := appUsageIntervalsByUser[s.UserID][t]; !ok {
23452345
appUsageIntervalsByUser[s.UserID][t] = &database.GetTemplateInsightsRow{}
23462346
}
23472347

23482348
if s.SessionCountJetBrains > 0 {
2349-
appUsageIntervalsByUser[s.UserID][t].UsageJetbrainsSeconds = 300
2349+
appUsageIntervalsByUser[s.UserID][t].UsageJetbrainsSeconds = 60
23502350
}
23512351
if s.SessionCountVSCode > 0 {
2352-
appUsageIntervalsByUser[s.UserID][t].UsageVscodeSeconds = 300
2352+
appUsageIntervalsByUser[s.UserID][t].UsageVscodeSeconds = 60
23532353
}
23542354
if s.SessionCountReconnectingPTY > 0 {
2355-
appUsageIntervalsByUser[s.UserID][t].UsageReconnectingPtySeconds = 300
2355+
appUsageIntervalsByUser[s.UserID][t].UsageReconnectingPtySeconds = 60
23562356
}
23572357
if s.SessionCountSSH > 0 {
2358-
appUsageIntervalsByUser[s.UserID][t].UsageSshSeconds = 300
2358+
appUsageIntervalsByUser[s.UserID][t].UsageSshSeconds = 60
23592359
}
23602360
}
23612361

‎coderd/database/queries.sql.go‎

Lines changed: 11 additions & 21 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: 11 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -25,32 +25,22 @@ ORDER BY user_id ASC;
2525
-- GetTemplateInsights has a granularity of 5 minutes where if a session/app was
2626
-- in use during a minute, we will add 5 minutes to the total usage for that
2727
-- session/app (per user).
28-
WITH ts AS (
29-
SELECT
30-
d::timestamptz AS from_,
31-
(d::timestamptz + '5 minute'::interval) AS to_,
32-
EXTRACT(epoch FROM '5 minute'::interval) AS seconds
33-
FROM
34-
-- Subtract 1 second from end_time to avoid including the next interval in the results.
35-
generate_series(@start_time::timestamptz, (@end_time::timestamptz) - '1 second'::interval, '5 minute'::interval) d
36-
), agent_stats_by_interval_and_user AS (
28+
WITH agent_stats_by_interval_and_user AS (
3729
SELECT
38-
ts.from_,
39-
ts.to_,
30+
date_trunc('minute', was.created_at),
4031
was.user_id,
4132
array_agg(was.template_id) AS template_ids,
42-
CASE WHEN SUM(was.session_count_vscode) > 0 THEN ts.seconds ELSE 0 END AS usage_vscode_seconds,
43-
CASE WHEN SUM(was.session_count_jetbrains) > 0 THEN ts.seconds ELSE 0 END AS usage_jetbrains_seconds,
44-
CASE WHEN SUM(was.session_count_reconnecting_pty) > 0 THEN ts.seconds ELSE 0 END AS usage_reconnecting_pty_seconds,
45-
CASE WHEN SUM(was.session_count_ssh) > 0 THEN ts.seconds ELSE 0 END AS usage_ssh_seconds
46-
FROM ts
47-
JOIN workspace_agent_stats was ON (
48-
was.created_at >= ts.from_
49-
AND was.created_at < ts.to_
33+
CASE WHEN SUM(was.session_count_vscode) > 0 THEN 60 ELSE 0 END AS usage_vscode_seconds,
34+
CASE WHEN SUM(was.session_count_jetbrains) > 0 THEN 60 ELSE 0 END AS usage_jetbrains_seconds,
35+
CASE WHEN SUM(was.session_count_reconnecting_pty) > 0 THEN 60 ELSE 0 END AS usage_reconnecting_pty_seconds,
36+
CASE WHEN SUM(was.session_count_ssh) > 0 THEN 60 ELSE 0 END AS usage_ssh_seconds
37+
FROM workspace_agent_stats was
38+
WHERE
39+
was.created_at >= @start_time::timestamptz
40+
AND was.created_at < @end_time::timestamptz
5041
AND was.connection_count > 0
5142
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN was.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
52-
)
53-
GROUP BY ts.from_, ts.to_, ts.seconds, was.user_id
43+
GROUP BY date_trunc('minute', was.created_at), was.user_id
5444
), template_ids AS (
5545
SELECT array_agg(DISTINCT template_id) AS ids
5646
FROM agent_stats_by_interval_and_user, unnest(template_ids) template_id

‎coderd/insights_test.go‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -760,13 +760,13 @@ func TestTemplateInsights_Golden(t *testing.T) {
760760
sessionCountVSCode: 1,
761761
sessionCountSSH: 1,
762762
},
763-
{ // 12 minutes of usage -> 15 minutes.
763+
{ // 12 minutes of usage.
764764
startedAt: frozenWeekAgo.AddDate(0, 0, 1),
765765
endedAt: frozenWeekAgo.AddDate(0, 0, 1).Add(12 * time.Minute),
766766
sessionCountSSH: 1,
767767
},
768-
{ // 2 minutes of usage -> 10 minutes because it crosses the 5 minute interval boundary.
769-
startedAt: frozenWeekAgo.AddDate(0, 0, 2).Add(4 * time.Minute),
768+
{ // 1m30s of usage -> 2m rounded.
769+
startedAt: frozenWeekAgo.AddDate(0, 0, 2).Add(4*time.Minute + 30*time.Second),
770770
endedAt: frozenWeekAgo.AddDate(0, 0, 2).Add(6 * time.Minute),
771771
sessionCountJetBrains: 1,
772772
},

‎coderd/testdata/insights/multiple_users_and_workspaces_week_all_templates.json.golden‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -31,7 +31,7 @@
3131
"display_name": "JetBrains",
3232
"slug": "jetbrains",
3333
"icon": "/icon/intellij.svg",
34-
"seconds": 600
34+
"seconds": 120
3535
},
3636
{
3737
"template_ids": [
@@ -55,7 +55,7 @@
5555
"display_name": "SSH",
5656
"slug": "ssh",
5757
"icon": "/icon/terminal.svg",
58-
"seconds": 11700
58+
"seconds": 11520
5959
},
6060
{
6161
"template_ids": [

‎coderd/testdata/insights/multiple_users_and_workspaces_week_deployment_wide.json.golden‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -31,7 +31,7 @@
3131
"display_name": "JetBrains",
3232
"slug": "jetbrains",
3333
"icon": "/icon/intellij.svg",
34-
"seconds": 600
34+
"seconds": 120
3535
},
3636
{
3737
"template_ids": [
@@ -55,7 +55,7 @@
5555
"display_name": "SSH",
5656
"slug": "ssh",
5757
"icon": "/icon/terminal.svg",
58-
"seconds": 11700
58+
"seconds": 11520
5959
},
6060
{
6161
"template_ids": [

‎coderd/testdata/insights/multiple_users_and_workspaces_week_first_template.json.golden‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,7 @@
2525
"display_name": "JetBrains",
2626
"slug": "jetbrains",
2727
"icon": "/icon/intellij.svg",
28-
"seconds": 600
28+
"seconds": 120
2929
},
3030
{
3131
"template_ids": [
@@ -45,7 +45,7 @@
4545
"display_name": "SSH",
4646
"slug": "ssh",
4747
"icon": "/icon/terminal.svg",
48-
"seconds": 8100
48+
"seconds": 7920
4949
},
5050
{
5151
"template_ids": [

‎coderd/testdata/insights/multiple_users_and_workspaces_week_other_timezone_(São_Paulo).json.golden‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,7 @@
2727
"display_name": "JetBrains",
2828
"slug": "jetbrains",
2929
"icon": "/icon/intellij.svg",
30-
"seconds": 600
30+
"seconds": 120
3131
},
3232
{
3333
"template_ids": [
@@ -47,7 +47,7 @@
4747
"display_name": "SSH",
4848
"slug": "ssh",
4949
"icon": "/icon/terminal.svg",
50-
"seconds": 4500
50+
"seconds": 4320
5151
},
5252
{
5353
"template_ids": [

0 commit comments

Comments
 (0)