Create project smoke test fails due to timeout
Summary
Production and Canary smoke tests fail in /qa/specs/features/browser_ui/7_configure/auto_devops/create_project_with_auto_devops_spec.rb and /qa/specs/features/browser_ui/1_manage/project/create_project_spec.rb because of the timeout.
Proposal
Clean up projects in gitlab-qa-sandbox-group?
Tool: delete_subgroups.rb
How to reproduce
- Login to https://gitlab.com/gitlab-org/gitlab-ce with username
gitlab-qaand password from the 1password team vault - Navigate to https://gitlab.com/gitlab-qa-sandbox-group
- Try to create a project
Logs
Job: https://ops.gitlab.net/gitlab-org/quality/canary/-/jobs/710891
Canary Pipeline: https://ops.gitlab.net/gitlab-org/quality/canary/pipelines/89857
Production Pipeline: https://ops.gitlab.net/gitlab-org/quality/production/pipelines/89863
Query:
SELECT COUNT(*) FROM "projects" WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "members"."type" IN ('GroupMember') AND "members"."source_type" = 'Namespace' AND "namespaces"."type" IN ('Group') AND "members"."user_id" = 1254817 AND "members"."requested_at" IS NULL AND (members.access_level >= 30)) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT DISTINCT "namespaces"."id" FROM "base_and_descendants" AS "namespaces" WHERE "namespaces"."id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND (EXISTS (SELECT 1 FROM "plans" INNER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."hosted_plan_id" = "plans"."id" WHERE "plans"."name" IN ('silver', 'gold') AND (gitlab_subscriptions.namespace_id = namespaces.id)))) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT DISTINCT "custom_project_templates_group_id" FROM "base_and_descendants" AS "namespaces" INNER JOIN projects ON projects.namespace_id = namespaces.custom_project_templates_group_id)) Logs:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
Aggregate (cost=25627003.07..25627003.08 rows=1 width=8) (actual time=12499.552..12499.552 rows=1 loops=1)
-> Nested Loop (cost=14703604.10..25626997.83 rows=2095 width=0) (actual time=11722.805..12499.541 rows=6 loops=1)
-> Unique (cost=14703603.66..25626632.98 rows=131 width=4) (actual time=11722.732..12499.412 rows=4 loops=1)
CTE base_and_descendants
-> Recursive Union (cost=0.87..1273.67 rows=131 width=322) (actual time=0.200..3.696 rows=229 loops=1)
-> Nested Loop (cost=0.87..20.29 rows=1 width=322) (actual time=0.179..1.445 rows=13 loops=1)
-> Index Scan using index_members_on_user_id on members (cost=0.43..16.83 rows=1 width=4) (actual time=0.118..1.102 rows=13 loops=1)
Index Cond: (user_id = 1254817)
Filter: ((requested_at IS NULL) AND (access_level >= 30) AND ((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text))
Rows Removed by Filter: 59
-> Index Scan using namespaces_pkey on namespaces namespaces_2 (cost=0.43..3.45 rows=1 width=322) (actual time=0.024..0.025 rows=1 loops=13)
Index Cond: (id = members.source_id)
Filter: ((type)::text = 'Group'::text)
-> Nested Loop (cost=0.43..125.08 rows=13 width=322) (actual time=0.030..0.243 rows=32 loops=7)
-> WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.005 rows=33 loops=7)
-> Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_3 (cost=0.43..12.48 rows=1 width=322) (actual time=0.005..0.007 rows=1 loops=229)
Index Cond: (parent_id = base_and_descendants.id)
Filter: ((type)::text = 'Group'::text)
-> Merge Join (cost=14702330.00..25625358.99 rows=131 width=4) (actual time=11722.679..12499.356 rows=4 loops=1)
Merge Cond: (namespaces_1.custom_project_templates_group_id = namespaces.id)
-> Unique (cost=14702322.77..25625347.30 rows=200 width=4) (actual time=10028.041..12494.909 rows=131 loops=1)
CTE base_and_descendants
-> Recursive Union (cost=99669.41..8206565.42 rows=37719680 width=322) (actual time=35.264..7804.524 rows=22059 loops=1)
-> Hash Semi Join (cost=99669.41..204608.55 rows=280440 width=322) (actual time=35.243..1793.765 rows=2281 loops=1)
Hash Cond: (namespaces_4.id = gitlab_subscriptions.namespace_id)
-> Index Scan using index_namespaces_on_type_partial on namespaces namespaces_4 (cost=0.43..98903.64 rows=1110869 width=322) (actual time=0.117..1457.430 r
ows=1105529 loops=1)
Index Cond: ((type)::text = 'Group'::text)
-> Hash (cost=81420.66..81420.66 rows=1459866 width=4) (actual time=24.926..24.926 rows=4252 loops=1)
Buckets: 2097152 Batches: 1 Memory Usage: 16534kB
-> Nested Loop (cost=0.43..81420.66 rows=1459866 width=4) (actual time=0.143..23.343 rows=4253 loops=1)
-> Seq Scan on plans (cost=0.00..4.06 rows=2 width=4) (actual time=0.027..0.034 rows=2 loops=1)
Filter: ((name)::text = ANY ('{silver,gold}'::text[]))
Rows Removed by Filter: 3
-> Index Scan using index_gitlab_subscriptions_on_hosted_plan_id on gitlab_subscriptions (cost=0.43..31584.44 rows=912386 width=8) (actual time
=0.062..11.227 rows=2126 loops=2)
Index Cond: (hosted_plan_id = plans.id)
-> Merge Join (cost=356429.31..724756.33 rows=3743924 width=322) (actual time=295.465..655.940 rows=2198 loops=9)
Merge Cond: (namespaces_5.parent_id = base_and_descendants_1.id)
-> Index Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_5 (cost=0.43..309391.42 rows=1110869 width=322) (actual time=0.016..631.8
91 rows=297372 loops=9)
Filter: ((type)::text = 'Group'::text)
-> Sort (cost=356428.87..363439.87 rows=2804400 width=4) (actual time=1.332..2.180 rows=4244 loops=9)
Sort Key: base_and_descendants_1.id
Sort Method: quicksort Memory: 25kB
-> WorkTable Scan on base_and_descendants base_and_descendants_1 (cost=0.00..56088.00 rows=2804400 width=4) (actual time=0.003..0.722 rows=2451 loops
=9)
-> Merge Join (cost=6495757.35..15910447.21 rows=603333869 width=4) (actual time=10028.039..12494.744 rows=451 loops=1)
Merge Cond: (projects_1.namespace_id = namespaces_1.custom_project_templates_group_id)
-> Index Only Scan using index_projects_on_namespace_id on projects projects_1 (cost=0.43..242968.08 rows=10965990 width=4) (actual time=0.071..3959.230 rows=10957
322 loops=1)
Heap Fetches: 689795
-> Materialize (cost=6495756.92..6684355.32 rows=37719680 width=4) (actual time=7840.227..7841.175 rows=458 loops=1)
-> Sort (cost=6495756.92..6590056.12 rows=37719680 width=4) (actual time=7840.206..7841.070 rows=140 loops=1)
Sort Key: namespaces_1.custom_project_templates_group_id
Sort Method: quicksort Memory: 1460kB
-> CTE Scan on base_and_descendants namespaces_1 (cost=0.00..754393.60 rows=37719680 width=4) (actual time=35.268..7835.042 rows=22059 loops=1)
-> Sort (cost=7.23..7.55 rows=131 width=4) (actual time=4.193..4.306 rows=226 loops=1)
Sort Key: namespaces.id
Sort Method: quicksort Memory: 35kB
-> CTE Scan on base_and_descendants namespaces (cost=0.00..2.62 rows=131 width=4) (actual time=0.210..4.094 rows=229 loops=1)
-> Index Only Scan using index_projects_on_namespace_id on projects (cost=0.43..2.62 rows=16 width=4) (actual time=0.016..0.017 rows=2 loops=4)
Index Cond: (namespace_id = namespaces.id)
Heap Fetches: 0
Planning time: 8.934 ms
Execution time: 12581.935 ms
(61 rows)Edited by Nailia Iskhakova