GraphQL: Fix N+1 issues with RunnerGroupsResolver
What does this MR do and why?
This MR adds support for preloading relationships for fullPath and webUrl properties for CiRunner.groups, which are in use for the Runners' admin UI.
For this, it was necessary to create a RunnerGroupsResolver, similar to RunnerProjectsResolver.
Part of #384066 (closed) and #385355 (closed)
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
-
Run the following query:
{ runners(type: GROUP_TYPE, last: 20) { count nodes { id runnerType groups { nodes { id path fullPath webUrl } } } } }
The queries generated by this branch should no longer generate a route query per-runner.
The functionality isn't changed in this MR, we only avoid an N+1 issue which is covered by the test.
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.
Database queries
Old queries
Processing by GraphqlController#execute as */*
Parameters: {"query"=>"{\n runners(type: GROUP_TYPE, last: 20) {\n count\n nodes {\n id\n runnerType\n groups {\n nodes {\n id\n fullPath\n webUrl\n }\n }\n }\n }\n}\n", "variables"=>"[FILTERED]", "graphql"=>{"query"=>"{\n runners(type: GROUP_TYPE, last: 20) {\n count\n nodes {\n id\n runnerType\n groups {\n nodes {\n id\n fullPath\n webUrl\n }\n }\n }\n }\n}\n", "variables"=>"[FILTERED]"}}
(14.6ms) SELECT COUNT(*) FROM "ci_runners" WHERE "ci_runners"."runner_type" = 2 /*application:web,correlation_id:01GM3GT53083526YXFRBSK9KYP,endpoint_id:graphql:unknown,db_config_name:ci,line:/app/graphql/types/countable_connection_type.rb:20:in `count'*/
↳ app/graphql/types/countable_connection_type.rb:20:in `count'
Ci::Runner Load (2.9ms) SELECT "ci_runners".* FROM "ci_runners" WHERE "ci_runners"."runner_type" = 2 ORDER BY "ci_runners"."created_at" ASC, "ci_runners"."id" ASC LIMIT 21 /*application:web,correlation_id:01GM3GT53083526YXFRBSK9KYP,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:114:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:114:in `block in limited_nodes'
(0.4ms) SELECT "ci_runner_namespaces"."runner_id", "ci_runner_namespaces"."namespace_id" FROM "ci_runner_namespaces" WHERE "ci_runner_namespaces"."runner_id" IN (3246, 3245, 3244, 3243, 3242, 3241, 3240, 3239, 3238, 3237, 3236, 3235, 3234, 3233, 3232, 3231, 3230, 3229, 3228, 7) /*application:web,correlation_id:01GM3GT53083526YXFRBSK9KYP,endpoint_id:GraphqlController#execute,db_config_name:ci,line:/app/graphql/types/ci/runner_type.rb:164:in `block in batched_owners'*/
↳ app/graphql/types/ci/runner_type.rb:164:in `block in batched_owners'
Group Load (1.3ms) 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"."type" = 'Group' AND "namespaces"."id" IN (27, 22) /*application:web,correlation_id:01GM3GT53083526YXFRBSK9KYP,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/graphql/types/ci/runner_type.rb:172:in `block in batched_owners'*/
↳ app/graphql/types/ci/runner_type.rb:172:in `block in batched_owners'
License Load (0.2ms) SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100 /*application:web,correlation_id:01GM3GT53083526YXFRBSK9KYP,endpoint_id:GraphqlController#execute,db_config_name:main,line:/ee/app/models/license.rb:82:in `load_license'*/
↳ ee/app/models/license.rb:82:in `load_license'
IpRestriction Load (0.2ms) SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 22 /*application:web,correlation_id:01GM3GT53083526YXFRBSK9KYP,endpoint_id:GraphqlController#execute,db_config_name:main,line:/ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'*/
↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
Route Load (0.5ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 22 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01GM3GT53083526YXFRBSK9KYP,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/concerns/routable.rb:121:in `full_path'*/
↳ app/models/concerns/routable.rb:121:in `full_path'
IpRestriction Load (0.2ms) SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 27 /*application:web,correlation_id:01GM3GT53083526YXFRBSK9KYP,endpoint_id:GraphqlController#execute,db_config_name:main,line:/ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'*/
↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
Route Load (0.2ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 27 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:web,correlation_id:01GM3GT53083526YXFRBSK9KYP,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/concerns/routable.rb:121:in `full_path'*/
↳ app/models/concerns/routable.rb:121:in `full_path'
Completed 200 OK in 203ms (Views: 0.4ms | ActiveRecord: 39.9ms | Elasticsearch: 0.0ms | Allocations: 193796)New queries
Processing by GraphqlController#execute as */*
Parameters: {"query"=>"{\n runners(type: GROUP_TYPE, last: 20) {\n count\n nodes {\n id\n runnerType\n groups {\n nodes {\n id\n fullPath\n webUrl\n }\n }\n }\n }\n}\n", "variables"=>"[FILTERED]", "graphql"=>{"query"=>"{\n runners(type: GROUP_TYPE, last: 20) {\n count\n nodes {\n id\n runnerType\n groups {\n nodes {\n id\n fullPath\n webUrl\n }\n }\n }\n }\n}\n", "variables"=>"[FILTERED]"}}
(7.9ms) SELECT COUNT(*) FROM "ci_runners" WHERE "ci_runners"."runner_type" = 2 /*application:web,correlation_id:01GM3GVSADWSKFH16VDGVN1HK3,endpoint_id:graphql:unknown,db_config_name:ci,line:/app/graphql/types/countable_connection_type.rb:20:in `count'*/
↳ app/graphql/types/countable_connection_type.rb:20:in `count'
Ci::Runner Load (0.6ms) SELECT "ci_runners".* FROM "ci_runners" WHERE "ci_runners"."runner_type" = 2 ORDER BY "ci_runners"."created_at" ASC, "ci_runners"."id" ASC LIMIT 21 /*application:web,correlation_id:01GM3GVSADWSKFH16VDGVN1HK3,endpoint_id:graphql:unknown,db_config_name:ci,line:/lib/gitlab/graphql/pagination/keyset/connection.rb:114:in `block in limited_nodes'*/
↳ lib/gitlab/graphql/pagination/keyset/connection.rb:114:in `block in limited_nodes'
(0.3ms) SELECT "ci_runner_namespaces"."runner_id", "ci_runner_namespaces"."namespace_id" FROM "ci_runner_namespaces" WHERE "ci_runner_namespaces"."runner_id" IN (3246, 3245, 3244, 3243, 3242, 3241, 3240, 3239, 3238, 3237, 3236, 3235, 3234, 3233, 3232, 3231, 3230, 3229, 3228, 7) /*application:web,correlation_id:01GM3GVSADWSKFH16VDGVN1HK3,endpoint_id:GraphqlController#execute,db_config_name:ci,line:/app/graphql/resolvers/ci/runner_groups_resolver.rb:24:in `block in resolve_with_lookahead'*/
↳ app/graphql/resolvers/ci/runner_groups_resolver.rb:24:in `block in resolve_with_lookahead'
Group Load (0.3ms) 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"."type" = 'Group' AND "namespaces"."id" IN (27, 22) /*application:web,correlation_id:01GM3GVSADWSKFH16VDGVN1HK3,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/user_max_access_level_in_groups_preloader.rb:48:in `map'*/
↳ app/models/preloaders/user_max_access_level_in_groups_preloader.rb:48:in `map'
Route Load (0.2ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" IN (22, 27) /*application:web,correlation_id:01GM3GVSADWSKFH16VDGVN1HK3,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/user_max_access_level_in_groups_preloader.rb:48:in `map'*/
↳ app/models/preloaders/user_max_access_level_in_groups_preloader.rb:48:in `map'
(0.8ms) SELECT MAX("members"."access_level") AS maximum_access_level, "hierarchy"."id" AS hierarchy_id FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" INNER JOIN (SELECT id, unnest(traversal_ids) as traversal_id FROM "namespaces" WHERE "namespaces"."id" IN (22, 27)) as hierarchy ON members.source_id = hierarchy.traversal_id WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "users"."state" = 'active' AND "members"."state" = 0 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5) AND "members"."user_id" = 1 GROUP BY "hierarchy"."id" /*application:web,correlation_id:01GM3GVSADWSKFH16VDGVN1HK3,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/user_max_access_level_in_groups_preloader.rb:39:in `preload_with_traversal_ids'*/
↳ app/models/preloaders/user_max_access_level_in_groups_preloader.rb:39:in `preload_with_traversal_ids'
Namespace Load (0.5ms) SELECT namespaces.*, root_query.id as source_id FROM "namespaces" INNER JOIN (SELECT id, traversal_ids[1] as root_id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (22, 27)) as root_query ON root_query.root_id = namespaces.id /*application:web,correlation_id:01GM3GVSADWSKFH16VDGVN1HK3,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/group_root_ancestor_preloader.rb:19:in `group_by'*/
↳ app/models/preloaders/group_root_ancestor_preloader.rb:19:in `group_by'
IpRestriction Load (0.2ms) SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" IN (22, 27) /*application:web,correlation_id:01GM3GVSADWSKFH16VDGVN1HK3,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/group_root_ancestor_preloader.rb:19:in `group_by'*/
↳ app/models/preloaders/group_root_ancestor_preloader.rb:19:in `group_by'
SamlProvider Load (0.2ms) SELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" IN (22, 27) /*application:web,correlation_id:01GM3GVSADWSKFH16VDGVN1HK3,endpoint_id:GraphqlController#execute,db_config_name:main,line:/app/models/preloaders/group_root_ancestor_preloader.rb:19:in `group_by'*/
↳ app/models/preloaders/group_root_ancestor_preloader.rb:19:in `group_by'
IpRestriction Load (0.1ms) SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 22 /*application:web,correlation_id:01GM3GVSADWSKFH16VDGVN1HK3,endpoint_id:GraphqlController#execute,db_config_name:main,line:/ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'*/
↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
IpRestriction Load (0.2ms) SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 27 /*application:web,correlation_id:01GM3GVSADWSKFH16VDGVN1HK3,endpoint_id:GraphqlController#execute,db_config_name:main,line:/ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'*/
↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
Completed 200 OK in 581ms (Views: 0.5ms | ActiveRecord: 11.3ms | Elasticsearch: 0.0ms | Allocations: 81387)Edited by Alper Akgun