- Notifications
You must be signed in to change notification settings - Fork907
chore: optimize workspace_latest_builds view query#17789
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.
By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.
Already on GitHub?Sign in to your account
Uh oh!
There was an error while loading.Please reload this page.
Conversation
0d2d188 to9811abeCompareLet's please wait for@evgeniy-scherbina to review this before merging. Thank you for the effort put into this ❤️ |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
LGTM, nice work!
coderd/database/migrations/000322_workspace_latest_builds_optimization.up.sql OutdatedShow resolvedHide resolved
Uh oh!
There was an error while loading.Please reload this page.
…mization.up.sqlCo-authored-by: Mathias Fredriksson <mafredri@gmail.com>
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
LGTM
Good idea with LATERAL JOIN and filtering out deleted workspaces.
Optionally we can preserve formatting & comments when we recreateworkspace_prebuilds view.
Signed-off-by: Danny Kopping <dannykopping@gmail.com>
I'm going to merge#17792 first so we can observe the impact on database load from that, and then merge this in to see it further drop. Thanks all 🎉 |
`Collect()` is called whenever the `/metrics` endpoint is hit toretrieve metrics.The queries used in prebuilds metrics collection are quite heavy, and wewant to avoid having them running concurrently / too often to keep dbload down.Here I'm moving towards a background retrieval of the state required toset the metrics, which gets invalidated every interval.Also introduces `coderd_prebuilt_workspaces_metrics_last_updated` whichoperators can use to determine when these metrics go stale.See#17789 as well.---------Signed-off-by: Danny Kopping <dannykopping@gmail.com>
ef745c0 intomainUh oh!
There was an error while loading.Please reload this page.
Uh oh!
There was an error while loading.Please reload this page.
Avoids two sequential scans of massive tables (
workspace_builds,provisioner_jobs) and uses index scans instead. This new view largely replicates our already optimized queryGetWorkspacesto fetch the latest build.The original query and the new query were compared against the dogfood database to ensure they return the exact same data in the exact same order (minus the new
workspaces.deleted = falsefilter to improve performance even more). The performance is massively improved even without theworkspaces.deleted = falsefilter, but it was added to improve it even more.Note: these query times are probably inflated due to high database load on our dogfood environment that this intends to partially resolve.
Before: 2,139ms (explain)
After: 33ms (explain)
Co-authored-by: Cian Johnstoncian@coder.com