Disable_joins for Ci::Runner groups
What does this MR do and why?
Disable_joins for Ci::Runner groups
Related issue: #338659 (closed)
For bin/rspec spec/requests/api/ci/runners_spec.rb:298
, we have:
OLD sql:
Pretty SQL
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 "namespaces"."id" = "ci_runner_namespaces"."namespace_id"
WHERE
"namespaces"."type" = 'Group'
AND "ci_runner_namespaces"."runner_id" = 2168
Group Load (0.8ms) 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 "namespaces"."id" = "ci_runner_namespaces"."namespace_id" WHERE "namespaces"."type" = 'Group' AND "ci_runner_namespaces"."runner_id" = 2168 /application:test,correlation_id:6595e6e0-0d6f-480e-a580-b208710691bc,endpoint_id:GET /api/:version/runners/:id,db_config_name:main/
NEW SQL:
Pretty SQL
SELECT
"ci_runner_namespaces"."namespace_id"
FROM
"ci_runner_namespaces"
WHERE
"ci_runner_namespaces"."runner_id" = 2158
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"
WHERE
"namespaces"."id" = 2026
AND "namespaces"."type" = 'Group'
(0.7ms) SELECT "ci_runner_namespaces"."namespace_id" FROM "ci_runner_namespaces" WHERE "ci_runner_namespaces"."runner_id" = 2158 /application:test,correlation_id:ecee278a-7dd6-42d7-b65a-2c569e02e68f,endpoint_id:GET /api/:version/runners/:id,db_config_name:main/
↳ lib/gem_extensions/active_record/disable_joins/associations/association_scope.rb:32:in block in last_scope_chain' Group Load (0.5ms) 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" WHERE "namespaces"."id" = 2026 AND "namespaces"."type" = 'Group' /*application:test,correlation_id:ecee278a-7dd6-42d7-b65a-2c569e02e68f,endpoint_id:GET /api/:version/runners/:id,db_config_name:main*/ ↳ lib/gem_extensions/active_record/associations/has_many_through_association.rb:11:in
find_target'
In the old query, you can see that ci_runner_namespaces
is clearly joined namespaces
which is a problem (https://docs.gitlab.com/ee/development/database/multiple_databases.html#removing-joins-between-ci_-and-non-ci_-tables).
disable_joins
works
How disable_joins
works by calling pluck
on the intermediate association, in this case it plucks the namespace_id
from ci_runner_namespaces
.
pluck
might be concern if there are a lot of plucked rows but as you see above we only have at most one row.
We then use the plucked value to query for the Namespace / Group
Screenshots or screen recordings
These are strongly recommended to assist reviewers and reduce the time to merge your change.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.