Skip to content

fix(coderd): rewrite template insights query for speed and fix intervals #9300

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 2 commits into from
Aug 24, 2023
Merged

fix(coderd): rewrite template insights query for speed and fix intervals #9300

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
82a899b
fix(coderd): rewrite template insights query for speed and fix intervals
mafredri Aug 24, 2023
718c695
fix fakedb
mafredri Aug 24, 2023
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
10 changes: 5 additions & 5 deletions coderd/database/dbfake/dbfake.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2340,22 +2340,22 @@ func (q *FakeQuerier) GetTemplateInsights(_ context.Context, arg database.GetTem
if appUsageIntervalsByUser[s.UserID] == nil {
appUsageIntervalsByUser[s.UserID] = make(map[time.Time]*database.GetTemplateInsightsRow)
}
t := s.CreatedAt.Truncate(5 * time.Minute)
t := s.CreatedAt.Truncate(time.Minute)
if _, ok := appUsageIntervalsByUser[s.UserID][t]; !ok {
appUsageIntervalsByUser[s.UserID][t] = &database.GetTemplateInsightsRow{}
}

if s.SessionCountJetBrains > 0 {
appUsageIntervalsByUser[s.UserID][t].UsageJetbrainsSeconds = 300
appUsageIntervalsByUser[s.UserID][t].UsageJetbrainsSeconds = 60
}
if s.SessionCountVSCode > 0 {
appUsageIntervalsByUser[s.UserID][t].UsageVscodeSeconds = 300
appUsageIntervalsByUser[s.UserID][t].UsageVscodeSeconds = 60
}
if s.SessionCountReconnectingPTY > 0 {
appUsageIntervalsByUser[s.UserID][t].UsageReconnectingPtySeconds = 300
appUsageIntervalsByUser[s.UserID][t].UsageReconnectingPtySeconds = 60
}
if s.SessionCountSSH > 0 {
appUsageIntervalsByUser[s.UserID][t].UsageSshSeconds = 300
appUsageIntervalsByUser[s.UserID][t].UsageSshSeconds = 60
}
}

Expand Down
32 changes: 11 additions & 21 deletions coderd/database/queries.sql.go
View file
Open in desktop

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

32 changes: 11 additions & 21 deletions coderd/database/queries/insights.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -25,32 +25,22 @@ ORDER BY user_id ASC;
-- GetTemplateInsights has a granularity of 5 minutes where if a session/app was
-- in use during a minute, we will add 5 minutes to the total usage for that
-- session/app (per user).
WITH ts AS (
SELECT
d::timestamptz AS from_,
(d::timestamptz + '5 minute'::interval) AS to_,
EXTRACT(epoch FROM '5 minute'::interval) AS seconds
FROM
-- Subtract 1 second from end_time to avoid including the next interval in the results.
generate_series(@start_time::timestamptz, (@end_time::timestamptz) - '1 second'::interval, '5 minute'::interval) d
), agent_stats_by_interval_and_user AS (
WITH agent_stats_by_interval_and_user AS (
SELECT
ts.from_,
ts.to_,
date_trunc('minute', was.created_at),
was.user_id,
array_agg(was.template_id) AS template_ids,
CASE WHEN SUM(was.session_count_vscode) > 0 THEN ts.seconds ELSE 0 END AS usage_vscode_seconds,
CASE WHEN SUM(was.session_count_jetbrains) > 0 THEN ts.seconds ELSE 0 END AS usage_jetbrains_seconds,
CASE WHEN SUM(was.session_count_reconnecting_pty) > 0 THEN ts.seconds ELSE 0 END AS usage_reconnecting_pty_seconds,
CASE WHEN SUM(was.session_count_ssh) > 0 THEN ts.seconds ELSE 0 END AS usage_ssh_seconds
FROM ts
JOIN workspace_agent_stats was ON (
was.created_at >= ts.from_
AND was.created_at < ts.to_
CASE WHEN SUM(was.session_count_vscode) > 0 THEN 60 ELSE 0 END AS usage_vscode_seconds,
CASE WHEN SUM(was.session_count_jetbrains) > 0 THEN 60 ELSE 0 END AS usage_jetbrains_seconds,
CASE WHEN SUM(was.session_count_reconnecting_pty) > 0 THEN 60 ELSE 0 END AS usage_reconnecting_pty_seconds,
CASE WHEN SUM(was.session_count_ssh) > 0 THEN 60 ELSE 0 END AS usage_ssh_seconds
FROM workspace_agent_stats was
WHERE
was.created_at >= @start_time::timestamptz
AND was.created_at < @end_time::timestamptz
AND was.connection_count > 0
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN was.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
)
GROUP BY ts.from_, ts.to_, ts.seconds, was.user_id
GROUP BY date_trunc('minute', was.created_at), was.user_id
), template_ids AS (
SELECT array_agg(DISTINCT template_id) AS ids
FROM agent_stats_by_interval_and_user, unnest(template_ids) template_id
Expand Down
6 changes: 3 additions & 3 deletions coderd/insights_test.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -760,13 +760,13 @@ func TestTemplateInsights_Golden(t *testing.T) {
sessionCountVSCode: 1,
sessionCountSSH: 1,
},
{ // 12 minutes of usage -> 15 minutes.
{ // 12 minutes of usage.
startedAt: frozenWeekAgo.AddDate(0, 0, 1),
endedAt: frozenWeekAgo.AddDate(0, 0, 1).Add(12 * time.Minute),
sessionCountSSH: 1,
},
{ // 2 minutes of usage -> 10 minutes because it crosses the 5 minute interval boundary.
startedAt: frozenWeekAgo.AddDate(0, 0, 2).Add(4 * time.Minute),
{ // 1m30s of usage -> 2m rounded.
startedAt: frozenWeekAgo.AddDate(0, 0, 2).Add(4*time.Minute + 30*time.Second),
endedAt: frozenWeekAgo.AddDate(0, 0, 2).Add(6 * time.Minute),
sessionCountJetBrains: 1,
},
Expand Down
4 changes: 2 additions & 2 deletions coderd/testdata/insights/multiple_users_and_workspaces_week_all_templates.json.golden
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -31,7 +31,7 @@
"display_name": "JetBrains",
"slug": "jetbrains",
"icon": "/icon/intellij.svg",
"seconds": 600
"seconds": 120
},
{
"template_ids": [
Expand All @@ -55,7 +55,7 @@
"display_name": "SSH",
"slug": "ssh",
"icon": "/icon/terminal.svg",
"seconds": 11700
"seconds": 11520
},
{
"template_ids": [
Expand Down
4 changes: 2 additions & 2 deletions coderd/testdata/insights/multiple_users_and_workspaces_week_deployment_wide.json.golden
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -31,7 +31,7 @@
"display_name": "JetBrains",
"slug": "jetbrains",
"icon": "/icon/intellij.svg",
"seconds": 600
"seconds": 120
},
{
"template_ids": [
Expand All @@ -55,7 +55,7 @@
"display_name": "SSH",
"slug": "ssh",
"icon": "/icon/terminal.svg",
"seconds": 11700
"seconds": 11520
},
{
"template_ids": [
Expand Down
4 changes: 2 additions & 2 deletions coderd/testdata/insights/multiple_users_and_workspaces_week_first_template.json.golden
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -25,7 +25,7 @@
"display_name": "JetBrains",
"slug": "jetbrains",
"icon": "/icon/intellij.svg",
"seconds": 600
"seconds": 120
},
{
"template_ids": [
Expand All @@ -45,7 +45,7 @@
"display_name": "SSH",
"slug": "ssh",
"icon": "/icon/terminal.svg",
"seconds": 8100
"seconds": 7920
},
{
"template_ids": [
Expand Down
4 changes: 2 additions & 2 deletions ...stdata/insights/multiple_users_and_workspaces_week_other_timezone_(São_Paulo).json.golden
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -27,7 +27,7 @@
"display_name": "JetBrains",
"slug": "jetbrains",
"icon": "/icon/intellij.svg",
"seconds": 600
"seconds": 120
},
{
"template_ids": [
Expand All @@ -47,7 +47,7 @@
"display_name": "SSH",
"slug": "ssh",
"icon": "/icon/terminal.svg",
"seconds": 4500
"seconds": 4320
},
{
"template_ids": [
Expand Down