Skip to content

fix(coderd): optimize template app insights query for speed and decrease intervals #9302

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 6 commits into from
Aug 24, 2023
Merged

fix(coderd): optimize template app insights query for speed and decrease intervals #9302

Show file tree
Hide file tree
Changes from 5 commits
Commits
Show all changes
6 commits
Select commit Hold shift + click to select a range
4bb2c73
lower app intervals to 1m and increase test coverage
mafredri Aug 24, 2023
bca6e37
fix(coderd): rewrite template app insights query for speed, decrease …
mafredri Aug 24, 2023
6080394
fix comment
mafredri Aug 24, 2023
d96187f
fix lint
mafredri Aug 24, 2023
66ffdbd
restore fakedb time filter
mafredri Aug 24, 2023
d65295e
use microsecond, postgres time granularity
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
4 changes: 2 additions & 2 deletions coderd/database/dbfake/dbfake.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2048,8 +2048,8 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
t = arg.StartTime
}
for t.Before(s.SessionEndedAt) && t.Before(arg.EndTime) {
appUsageIntervalsByUserAgentApp[key][t] = 300 // 5 minutes.
t = t.Add(5 * time.Minute)
appUsageIntervalsByUserAgentApp[key][t] = 60 // 1 minute.
t = t.Add(1 * time.Minute)
}
}

Expand Down
43 changes: 21 additions & 22 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.

37 changes: 18 additions & 19 deletions coderd/database/queries/insights.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -61,19 +61,10 @@ FROM agent_stats_by_interval_and_user;
-- GetTemplateAppInsights returns the aggregate usage of each app in a given
-- timeframe. The result can be filtered on template_ids, meaning only user data
-- from workspaces based on those templates will be included.
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
), app_stats_by_user_and_agent AS (
WITH app_stats_by_user_and_agent AS (
SELECT
ts.from_,
ts.to_,
ts.seconds,
s.start_time,
60 as seconds,
w.template_id,
was.user_id,
was.agent_id,
Expand All @@ -82,12 +73,7 @@ WITH ts AS (
wa.display_name,
wa.icon,
(wa.slug IS NOT NULL)::boolean AS is_app
FROM ts
JOIN workspace_app_stats was ON (
(was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
)
FROM workspace_app_stats was
JOIN workspaces w ON (
w.id = was.workspace_id
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN w.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
Expand All @@ -98,7 +84,20 @@ WITH ts AS (
wa.agent_id = was.agent_id
AND wa.slug = was.slug_or_port
)
GROUP BY ts.from_, ts.to_, ts.seconds, w.template_id, was.user_id, was.agent_id, was.access_method, was.slug_or_port, wa.display_name, wa.icon, wa.slug
-- This table contains both 1 minute entries and >1 minute entries,
-- to calculate this with our uniqueness constraints, we generate series
-- for the longer intervals.
CROSS JOIN LATERAL generate_series(
date_trunc('minute', was.session_started_at),
-- Subtract 1ms to avoid creating an extra series.
date_trunc('minute', was.session_ended_at - '1 ms'::interval),
'1 minute'::interval
) s(start_time)
WHERE
s.start_time >= @start_time::timestamptz
-- Subtract one minute because the series only contains the start time.
AND s.start_time < (@end_time::timestamptz) - '1 minute'::interval
GROUP BY s.start_time, w.template_id, was.user_id, was.agent_id, was.access_method, was.slug_or_port, wa.display_name, wa.icon, wa.slug
)

SELECT
Expand Down
14 changes: 13 additions & 1 deletion coderd/insights_test.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -778,7 +778,19 @@ func TestTemplateInsights_Golden(t *testing.T) {
endedAt: frozenWeekAgo.Add(time.Hour),
requests: 1,
},
{ // used an app on the last day, counts as active user, 12m -> 15m rounded.
{ // 30s of app usage -> 1m rounded.
app: users[0].workspaces[0].apps[0],
startedAt: frozenWeekAgo.Add(2*time.Hour + 10*time.Second),
endedAt: frozenWeekAgo.Add(2*time.Hour + 40*time.Second),
requests: 1,
},
{ // 1m30s of app usage -> 2m rounded (included in São Paulo).
app: users[0].workspaces[0].apps[0],
startedAt: frozenWeekAgo.Add(3*time.Hour + 30*time.Second),
endedAt: frozenWeekAgo.Add(3*time.Hour + 90*time.Second),
requests: 1,
},
{ // used an app on the last day, counts as active user, 12m.
app: users[0].workspaces[0].apps[2],
startedAt: frozenWeekAgo.AddDate(0, 0, 6),
endedAt: frozenWeekAgo.AddDate(0, 0, 6).Add(12 * time.Minute),
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 @@ -66,7 +66,7 @@
"display_name": "app1",
"slug": "app1",
"icon": "/icon1.png",
"seconds": 25200
"seconds": 25380
},
{
"template_ids": [
Expand All @@ -76,7 +76,7 @@
"display_name": "app3",
"slug": "app3",
"icon": "/icon2.png",
"seconds": 900
"seconds": 720
},
{
"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 @@ -66,7 +66,7 @@
"display_name": "app1",
"slug": "app1",
"icon": "/icon1.png",
"seconds": 25200
"seconds": 25380
},
{
"template_ids": [
Expand All @@ -76,7 +76,7 @@
"display_name": "app3",
"slug": "app3",
"icon": "/icon2.png",
"seconds": 900
"seconds": 720
},
{
"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 @@ -55,7 +55,7 @@
"display_name": "app1",
"slug": "app1",
"icon": "/icon1.png",
"seconds": 3600
"seconds": 3780
},
{
"template_ids": [
Expand All @@ -65,7 +65,7 @@
"display_name": "app3",
"slug": "app3",
"icon": "/icon2.png",
"seconds": 900
"seconds": 720
}
],
"parameters_usage": []
Expand Down
5 changes: 3 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 @@ -51,13 +51,14 @@
},
{
"template_ids": [
"00000000-0000-0000-0000-000000000001",
"00000000-0000-0000-0000-000000000002"
],
"type": "app",
"display_name": "app1",
"slug": "app1",
"icon": "/icon1.png",
"seconds": 21600
"seconds": 21720
},
{
"template_ids": [
Expand All @@ -67,7 +68,7 @@
"display_name": "app3",
"slug": "app3",
"icon": "/icon2.png",
"seconds": 4500
"seconds": 4320
},
{
"template_ids": [
Expand Down