Repository returning 500 due to CI pipeline timeout
Description
A user is having trouble accessing a repository on a self-hosted instance. When browsing the repository they get a 500 error:
ActionView::Template::Error (PG::QueryCanceled: ERROR: canceling statement due to statement timeout
: SELECT "ci_pipelines"."sha", "ci_pipelines"."status" FROM "ci_pipelines" LEFT OUTER JOIN "ci_pipelines" "ci_pipelines_2" ON "ci_pipelines"."sha" = "ci_pipelines_2"."sha" AND "ci_pipelines"."project_id" = "ci_pipelines_2"."project_id" AND "ci_pipelines"."id" < "ci_pipelines_2"."id" AND "ci_pipelines"."ref" = "ci_pipelines_2"."ref" WHERE "ci_pipelines"."project_id" = 1620 AND "ci_pipelines"."sha" = '1a2c1459da701664b2030f27bb47b78347d1b4fd' AND "ci_pipelines_2"."id" IS NULL AND "ci_pipelines"."ref" = 'master'):
4: - ref = local_assigns.fetch(:ref) { merge_request&.source_branch }
5:
6: - link = commit_path(project, commit, merge_request: merge_request)
7: - cache_key = [project.full_path,
8: ref,
9: commit.id,
10: Gitlab::CurrentSettings.current_application_settings,
app/models/ci/pipeline.rb:235:in `latest_status_per_commit'
The query:
gitlabhq_production=# SET statement_timeout = 0;
SET
gitlabhq_production=# EXPLAIN ANALYZE SELECT "ci_pipelines"."sha", "ci_pipelines"."status" FROM "ci_pipelines" LEFT OUTER JOIN "ci_pipelines" "ci_pipelines_2" ON "ci_pipelines"."sha" = "ci_pipelines_2"."sha" AND "ci_pipelines"."project_id" = "ci_pipelines_2"."project_id" AND "ci_pipelines"."id" < "ci_pipelines_2"."id" AND "ci_pipelines"."ref" = "ci_pipelines_2"."ref" WHERE "ci_pipelines"."project_id" = 1620 AND "ci_pipelines"."sha" = '1a2c1459da701664b2030f27bb47b78347d1b4fd' AND "ci_pipelines_2"."id" IS NULL AND "ci_pipelines"."ref" = 'master';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=6836.73..11541.15 rows=8166 width=48) (actual time=90240.507..90355.464 rows=1 loops=1)
Hash Cond: (((ci_pipelines.sha)::text = (ci_pipelines_2.sha)::text) AND (ci_pipelines.project_id = ci_pipelines_2.project_id) AND ((ci_pipelines.ref)::text = (ci_pipelines_2.ref)::text))
Join Filter: (ci_pipelines.id < ci_pipelines_2.id)
Rows Removed by Join Filter: 281023110
-> Bitmap Heap Scan on ci_pipelines (cost=1072.79..5549.58 rows=12249 width=70) (actual time=11.983..63.576 rows=54334 loops=1)
Recheck Cond: ((project_id = 1620) AND ((sha)::text = '1a2c1459da701664b2030f27bb47b78347d1b4fd'::text))
Filter: ((ref)::text = 'master'::text)
Heap Blocks: exact=2154
-> Bitmap Index Scan on index_ci_pipelines_on_project_id_and_sha (cost=0.00..1069.73 rows=19931 width=0) (actual time=11.693..11.693 rows=54334 loops=1)
Index Cond: ((project_id = 1620) AND ((sha)::text = '1a2c1459da701664b2030f27bb47b78347d1b4fd'::text))
-> Hash (cost=5549.58..5549.58 rows=12249 width=63) (actual time=47.707..47.707 rows=54334 loops=1)
Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 5341kB
-> Bitmap Heap Scan on ci_pipelines ci_pipelines_2 (cost=1072.79..5549.58 rows=12249 width=63) (actual time=10.575..32.688 rows=54334 loops=1)
Recheck Cond: ((project_id = 1620) AND ((sha)::text = '1a2c1459da701664b2030f27bb47b78347d1b4fd'::text))
Filter: ((ref)::text = 'master'::text)
Heap Blocks: exact=2154
-> Bitmap Index Scan on index_ci_pipelines_on_project_id_and_sha (cost=0.00..1069.73 rows=19931 width=0) (actual time=10.299..10.299 rows=54334 loops=1)
Index Cond: ((project_id = 1620) AND ((sha)::text = '1a2c1459da701664b2030f27bb47b78347d1b4fd'::text))
Planning time: 0.481 ms
Execution time: 90355.942 ms
(20 rows)
Running 11.0.2-ee https://gitlab.zendesk.com/agent/tickets/99563
Edited by Cindy Pallares 🦉