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

Commit258a839

Browse files
authored
chore(coderd/database): optimize GetRunningPrebuiltWorkspaces (#18588)
Fixescoder/internal#715After this change, the only use of the `workspace_prebuilds` view is the`ClaimPrebuiltWorkspace` query. A subsequent PR will update the view.Before: ~44mshttps://explain.dalibo.com/plan/76cbe21d1a4c9329#planAfter: 7.3mshttps://explain.dalibo.com/plan/5abbdf926315677e#plan
1 parent0f56f00 commit258a839

File tree

3 files changed

+188
-25
lines changed

3 files changed

+188
-25
lines changed

‎coderd/database/querier_test.go‎

Lines changed: 92 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3758,9 +3758,9 @@ func createPrebuiltWorkspace(
37583758
job:=dbgen.ProvisionerJob(t,db,nil, database.ProvisionerJob{
37593759
Type:database.ProvisionerJobTypeWorkspaceBuild,
37603760
OrganizationID:orgID,
3761-
3762-
CreatedAt:now.Add(-1*time.Minute),
3763-
Error:jobError,
3761+
CreatedAt:now.Add(-1*time.Minute),
3762+
CompletedAt: sql.NullTime{Time:now,Valid:true},
3763+
Error:jobError,
37643764
})
37653765

37663766
// create ready agents
@@ -3930,6 +3930,95 @@ func TestWorkspacePrebuildsView(t *testing.T) {
39303930
}
39313931
}
39323932

3933+
funcTestGetRunningPrebuiltWorkspaces(t*testing.T) {
3934+
t.Parallel()
3935+
if!dbtestutil.WillUsePostgres() {
3936+
t.SkipNow()
3937+
}
3938+
3939+
now:=dbtime.Now()
3940+
orgID:=uuid.New()
3941+
userID:=uuid.New()
3942+
3943+
testCases:= []struct {
3944+
namestring
3945+
readyAgentsint
3946+
notReadyAgentsint
3947+
expectRowsint
3948+
expectReadybool
3949+
}{
3950+
{
3951+
name:"one ready agent",
3952+
readyAgents:1,
3953+
notReadyAgents:0,
3954+
expectRows:1,
3955+
expectReady:true,
3956+
},
3957+
{
3958+
name:"one not ready agent",
3959+
readyAgents:0,
3960+
notReadyAgents:1,
3961+
expectRows:1,
3962+
expectReady:false,
3963+
},
3964+
{
3965+
name:"one ready, one not ready",
3966+
readyAgents:1,
3967+
notReadyAgents:1,
3968+
expectRows:1,
3969+
expectReady:false,
3970+
},
3971+
{
3972+
name:"both ready",
3973+
readyAgents:2,
3974+
notReadyAgents:0,
3975+
expectRows:1,
3976+
expectReady:true,
3977+
},
3978+
{
3979+
name:"five ready, one not ready",
3980+
readyAgents:5,
3981+
notReadyAgents:1,
3982+
expectRows:1,
3983+
expectReady:false,
3984+
},
3985+
}
3986+
3987+
for_,tc:=rangetestCases {
3988+
t.Run(tc.name,func(t*testing.T) {
3989+
t.Parallel()
3990+
3991+
sqlDB:=testSQLDB(t)
3992+
err:=migrations.Up(sqlDB)
3993+
require.NoError(t,err)
3994+
db:=database.New(sqlDB)
3995+
3996+
ctx:=testutil.Context(t,testutil.WaitShort)
3997+
3998+
dbgen.Organization(t,db, database.Organization{
3999+
ID:orgID,
4000+
})
4001+
dbgen.User(t,db, database.User{
4002+
ID:userID,
4003+
})
4004+
4005+
tmpl:=createTemplate(t,db,orgID,userID)
4006+
tmplV1:=createTmplVersionAndPreset(t,db,tmpl,tmpl.ActiveVersionID,now,nil)
4007+
createPrebuiltWorkspace(ctx,t,db,tmpl,tmplV1,orgID,now,&createPrebuiltWorkspaceOpts{
4008+
readyAgents:tc.readyAgents,
4009+
notReadyAgents:tc.notReadyAgents,
4010+
})
4011+
4012+
workspacePrebuilds,err:=db.GetRunningPrebuiltWorkspaces(ctx)
4013+
require.NoError(t,err)
4014+
require.Len(t,workspacePrebuilds,tc.expectRows)
4015+
iftc.expectRows>0 {
4016+
require.Equal(t,tc.expectReady,workspacePrebuilds[0].Ready)
4017+
}
4018+
})
4019+
}
4020+
}
4021+
39334022
funcTestGetPresetsBackoff(t*testing.T) {
39344023
t.Parallel()
39354024
if!dbtestutil.WillUsePostgres() {

‎coderd/database/queries.sql.go‎

Lines changed: 48 additions & 11 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/queries/prebuilds.sql‎

Lines changed: 48 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -49,18 +49,55 @@ WHERE tvp.desired_instances IS NOT NULL -- Consider only presets that have a pre
4949
AND (t.id=sqlc.narg('template_id')::uuidORsqlc.narg('template_id') ISNULL);
5050

5151
-- name: GetRunningPrebuiltWorkspaces :many
52+
WITH latest_prebuildsAS (
53+
SELECT
54+
latest_build.workspace_id,
55+
workspaces.name,
56+
workspaces.template_id,
57+
latest_build.template_version_id,
58+
latest_build.template_version_preset_id,
59+
latest_build.job_id,
60+
workspaces.created_at
61+
FROM workspaces
62+
JOIN LATERAL (
63+
SELECT
64+
workspace_builds.workspace_id,
65+
workspace_builds.template_version_id,
66+
workspace_builds.job_id,
67+
workspace_builds.template_version_preset_id
68+
FROM workspace_builds
69+
JOIN provisioner_jobsONprovisioner_jobs.id=workspace_builds.job_id
70+
WHEREworkspace_builds.workspace_id=workspaces.id
71+
ANDworkspace_builds.transition='start'::workspace_transition
72+
ANDprovisioner_jobs.job_status='succeeded'::provisioner_job_status
73+
ORDER BYworkspace_builds.build_numberDESC
74+
LIMIT1
75+
)AS latest_buildON true
76+
WHEREworkspaces.deleted= false
77+
ANDworkspaces.owner_id='c42fdf75-3097-471c-8c33-fb52454d81c0'::UUID
78+
),
79+
ready_agentsAS (
80+
SELECT
81+
latest_prebuilds.job_id,
82+
BOOL_AND(workspace_agents.lifecycle_state='ready'::workspace_agent_lifecycle_state)::booleanAS ready
83+
FROM latest_prebuilds
84+
JOIN workspace_resourcesONworkspace_resources.job_id=latest_prebuilds.job_id
85+
JOIN workspace_agentsONworkspace_agents.resource_id=workspace_resources.id
86+
WHEREworkspace_agents.deleted= false
87+
ANDworkspace_agents.parent_id ISNULL
88+
GROUP BYlatest_prebuilds.job_id
89+
)
5290
SELECT
53-
p.id,
54-
p.name,
55-
p.template_id,
56-
b.template_version_id,
57-
p.current_preset_idAS current_preset_id,
58-
p.ready,
59-
p.created_at
60-
FROM workspace_prebuilds p
61-
INNER JOIN workspace_latest_builds bONb.workspace_id=p.id
62-
WHERE (b.transition='start'::workspace_transition
63-
ANDb.job_status='succeeded'::provisioner_job_status);
91+
latest_prebuilds.workspace_idAS id,
92+
latest_prebuilds.name,
93+
latest_prebuilds.template_id,
94+
latest_prebuilds.template_version_id,
95+
latest_prebuilds.template_version_preset_idAS current_preset_id,
96+
COALESCE(ready_agents.ready, false)::booleanAS ready,
97+
latest_prebuilds.created_at
98+
FROM latest_prebuilds
99+
LEFT JOIN ready_agentsONready_agents.job_id=latest_prebuilds.job_id
100+
ORDER BYlatest_prebuilds.workspace_idASC;
64101

65102
-- name: CountInProgressPrebuilds :many
66103
-- CountInProgressPrebuilds returns the number of in-progress prebuilds, grouped by preset ID and transition.

0 commit comments

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