Skip to content

Disable_joins for Ci::Runner groups

Thong Kuah requested to merge disable_joins_ci_runner_groups into master

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).

How disable_joins works

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.

Edited by Pedro Pombeiro (OOO from July 16th till Aug 7th)

Merge request reports