Queuing queries review 2021-09-09
Description
Shared runners query
All denormalizations enabled:
WITH "project_builds" AS MATERIALIZED
(SELECT "ci_running_builds"."project_id", COUNT(*) AS running_builds
FROM "ci_running_builds"
WHERE "ci_running_builds"."runner_type" = 1
GROUP BY "ci_running_builds"."project_id")
SELECT "ci_pending_builds".*
FROM "ci_pending_builds"
LEFT JOIN project_builds ON ci_pending_builds.project_id = project_builds.project_id
WHERE "ci_pending_builds"."instance_runners_enabled" = TRUE
AND "ci_pending_builds"."minutes_exceeded" = FALSE
AND (tag_ids <@ ARRAY[NULL]::int[])
ORDER BY COALESCE(project_builds.running_builds, 0) ASC, ci_pending_builds.build_id ASC;Conclusions: fully denormalized
Group runners query
All denormalizations enabled, 1 denormalization pending:
SELECT "ci_pending_builds".*
FROM "ci_pending_builds"
WHERE "ci_pending_builds"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
INNER JOIN "project_ci_cd_settings" ON "project_ci_cd_settings"."project_id" = "projects"."id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "projects"."namespace_id" IN
(WITH RECURSIVE "base_and_descendants" AS (
(SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM "namespaces"
INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."namespace_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "ci_runner_namespaces"."runner_id" = 6)
UNION
(SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
FROM "namespaces",
"base_and_descendants"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id"
FROM "base_and_descendants" AS "namespaces")
AND "project_ci_cd_settings"."group_runners_enabled" = TRUE
AND ("project_features"."builds_access_level" > 0
OR "project_features"."builds_access_level" IS NULL)
AND "projects"."pending_delete" = FALSE)
AND (tag_ids <@ ARRAY[NULL]::int[])
ORDER BY build_id ASC;Conclusion: 1 denormalization pending -> find all namespace_ids or project_ids before executing the query.
Project runners query
All denormalizations enabled, 1 denormalization pending:
SELECT "ci_pending_builds".*
FROM "ci_pending_builds"
WHERE "ci_pending_builds"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
INNER JOIN "ci_runner_projects" ON "projects"."id" = "ci_runner_projects"."project_id"
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE "ci_runner_projects"."runner_id" = 8
AND "projects"."pending_delete" = FALSE
AND ("project_features"."builds_access_level" > 0
OR "project_features"."builds_access_level" IS NULL))
AND (tag_ids <@ ARRAY[NULL]::int[])
ORDER BY build_id ASC;Conclusion: 1 denormalization pending -> find all project_ids before executing the query.
Conclusion
Find a way to feed project_id or namespace_id into the queuing queries with the need of cross-joining with namespaces / projects.
Possible solutions:
- For group runners find all namespaces this group runner can pick builds from and match them with
ci_pending_builds.namespace_id(already denormalized) - For project runners find all projects this project runner can pick builds from and match them with
ci_pending_builds.project_id(already denormalized)
Caveat: check production database to understand how many projects / namespaces a runner can actually support, to understand if the number of items we will need to pass to the query is not too large.