`Projects::Settings::RepositoryController#show` executes more than 100 SQL queries
This controller action executes more than 100 SQL queries, the maximum number of queries allowed after the changes in the MR: !162208 (merged). This cause ee/spec/features/projects/settings/ee/protected_branches_spec.rb
to fail (example).
The number of queries should be reduced to less than 100 and the corresponding allow-listed sections of code (Gitlab::QueryLimiting.disable!('...')
) should be removed. The following tests should pass:
ee/spec/features/projects/settings/ee/protected_branches_spec.rb
ee/spec/features/protected_branches_spec.rb
spec/features/protected_branches_spec.rb
Designs
- Show closed items
Activity
-
Newest first Oldest first
-
Show all activity Show comments only Show history only
- Chaoyue Zhao added groupsource code typemaintenance labels
added groupsource code typemaintenance labels
added Category:Source Code Management devopscreate sectiondev labels
- Chaoyue Zhao mentioned in merge request !164964 (merged)
mentioned in merge request !164964 (merged)
- Hunter Stewart added workflowrefinement label
added workflowrefinement label
- Author Developer
- Chaoyue Zhao changed milestone to %17.6
changed milestone to %17.6
- Chaoyue Zhao added backend label
added backend label
- Susmikshatha Devi Poosarla set weight to 2
set weight to 2
- Susmikshatha Devi Poosarla assigned to @vyaklushin
assigned to @vyaklushin
- Vasilii Iakliushin added workflowin dev label and removed workflowrefinement label
added workflowin dev label and removed workflowrefinement label
- Vasilii Iakliushin mentioned in commit 670b1111
mentioned in commit 670b1111
- Vasilii Iakliushin mentioned in merge request !169163 (merged)
mentioned in merge request !169163 (merged)
- Susmikshatha Devi Poosarla added Deliverable label
added Deliverable label
- Vasilii Iakliushin mentioned in commit 9e5ad5aa
mentioned in commit 9e5ad5aa
- Vasilii Iakliushin mentioned in merge request !169276 (merged)
mentioned in merge request !169276 (merged)
- Vasilii Iakliushin mentioned in merge request !169276 (merged)
mentioned in merge request !169276 (merged)
- Vasilii Iakliushin mentioned in commit c4e93f2e
mentioned in commit c4e93f2e
- Vasilii Iakliushin mentioned in merge request !169280 (merged)
mentioned in merge request !169280 (merged)
- Vasilii Iakliushin mentioned in commit fea653f2
mentioned in commit fea653f2
- Maintainer
Setting health status to
on track
as the milestone has just begun.Issue participants are welcome to override this by setting the health status to another value.
- 🤖 GitLab Bot 🤖 changed health status to on track
changed health status to on track
- 🤖 GitLab Bot 🤖 mentioned in issue gitlab-org/quality/triage-reports#20326 (closed)
mentioned in issue gitlab-org/quality/triage-reports#20326 (closed)
- Vasilii Iakliushin mentioned in commit 86dd58fc
mentioned in commit 86dd58fc
- Vasilii Iakliushin mentioned in commit 2fe6f40c
mentioned in commit 2fe6f40c
- Vasilii Iakliushin added workflowin review label and removed workflowin dev label
added workflowin review label and removed workflowin dev label
- Vasilii Iakliushin mentioned in merge request !169948 (merged)
mentioned in merge request !169948 (merged)
- Maintainer
Update
We had to revert the fix - Revert "Projects::Settings::RepositoryControlle... (!170458 - merged).
The endpoint was still generating more than 100 requests: https://gitlab.com/gitlab-org/gitlab/-/jobs/8176220360
I've checked a list of queries to see if there are any repetitions and found several of them. I will take a closer look to see if they can be optimized:
-
SELECT "appearances".* FROM "appearances" ORDER BY "appearances"."id" ASC LIMIT 1
- repeated 3 times. -
SELECT "integrations".* FROM "integrations"
- repeated twice -
SELECT "projects"."id", "projects"."namespace_id" FROM "projects" WHERE "projects"."id" = 67
- 16 times -
SELECT namespaces.*, root_query.project_id as source_id FROM "namespaces" INNER JOIN (SELECT "projects"."id", "projects"."namespace_id", projects.id as project_id, namespaces.traversal_ids[1] as root_id FROM "projects" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" WHERE "projects"."id" = 67) as root_query ON root_query.root_id = namespaces.id
- 16 times -
SELECT "routes".* FROM "routes"
- 5/6 times -
SELECT "security_orchestration_policy_configurations"."id"
- 13 times (on GitLab.com)
Edited by Vasilii Iakliushin -
Collapse replies - Maintainer
SELECT "projects"."id", "projects"."namespace_id" FROM "projects" WHERE "projects"."id" = 67
- 16 timesIt comes from custom abilities implementation that loads all custom abilities in cycle and preloads projects for each ability.
MemberRole.all_customizable_project_permissions.each do |ability| desc "Custom role on project that enables #{ability.to_s.tr('_', ' ')}" condition("custom_role_enables_#{ability}".to_sym) do ::Authz::CustomAbility.allowed?(@user, ability, @subject) end end
I took some time to resolve it, but it seems to be in groupauthorization scope.
I will check other queries to see if they can be optimized.
Edited by Vasilii Iakliushin - Maintainer
I found the way to limit the number of queries: Optimize: custom ability verification (!170610 - merged).
- Vasilii Iakliushin added workflowin dev label and removed workflowin review label
added workflowin dev label and removed workflowin review label
- Vasilii Iakliushin mentioned in commit 2f45216c
mentioned in commit 2f45216c
- Vasilii Iakliushin mentioned in merge request !170610 (merged)
mentioned in merge request !170610 (merged)
- Vasilii Iakliushin mentioned in commit a5b987d1
mentioned in commit a5b987d1
- 🤖 GitLab Bot 🤖 mentioned in issue gitlab-org/quality/triage-reports#20442 (closed)
mentioned in issue gitlab-org/quality/triage-reports#20442 (closed)
- Vasilii Iakliushin mentioned in commit 4b8edcfe
mentioned in commit 4b8edcfe
- Vasilii Iakliushin added workflowin review label and removed workflowin dev label
added workflowin review label and removed workflowin dev label
- Vasilii Iakliushin mentioned in commit 01168462
mentioned in commit 01168462
- Vasilii Iakliushin mentioned in commit 62f3c471
mentioned in commit 62f3c471
- Vasilii Iakliushin mentioned in merge request !171082 (merged)
mentioned in merge request !171082 (merged)
- Maintainer
After applying various optimizations:
- Optimization: don't load a User when user_id is... (!169163 - merged)
- Optimization: don't load group if not needed (!169276 - merged)
- Optimize: association loading for RepositoryCon... (!169280 - merged)
- Optimize: custom ability verification (!170610 - merged)
It was possible to re-enable the query limit: Projects::Settings::RepositoryController#show: ... (!171082 - merged).
The issue is resolved.
1 1 - Vasilii Iakliushin closed
closed
- Vasilii Iakliushin added workflowcomplete label and removed workflowin review label
added workflowcomplete label and removed workflowin review label
- Vasilii Iakliushin mentioned in commit 0bbbcc63
mentioned in commit 0bbbcc63
- Vasilii Iakliushin mentioned in issue #482784 (closed)
mentioned in issue #482784 (closed)
- Vasilii Iakliushin mentioned in issue #482783 (closed)
mentioned in issue #482783 (closed)
- Vasilii Iakliushin mentioned in issue #482781 (closed)
mentioned in issue #482781 (closed)
- Vasilii Iakliushin mentioned in issue #482779 (closed)
mentioned in issue #482779 (closed)
- Vasilii Iakliushin mentioned in issue #482787 (closed)
mentioned in issue #482787 (closed)
- Vasilii Iakliushin mentioned in issue #482778 (closed)
mentioned in issue #482778 (closed)