Avoid DB timeouts when scheduling migrations
What does this MR do?
https://gitlab.com/gitlab-org/gitlab-ce/issues/63036 shows that https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/28843 introduces a background migration scheduling query that can timeout, given it doesn't seem to be using the added index (from https://gitlab.com/gitlab-org/gitlab-ce/issues/63036#note_179684901):
gitlabhq_production=# EXPLAIN ANALYZE SELECT "merge_requests"."id" FROM "merge_requests" WHERE "merge_requests"."state" = 'opened' AND "merge_requests"."merge_status" = 'can_be_merged' AND ("merge_requests"."id" >= 35) ORDER BY id LIMIT 1 OFFSET 10000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=56228.78..56234.40 rows=1 width=4) (actual time=6428.158..6428.159 rows=1 loops=1)
-> Index Scan using merge_requests_pkey on merge_requests (cost=0.56..4882920.89 rows=868411 width=4) (actual time=0.013..6427.389 rows=10001 loops=1)
Index Cond: (id >= 35)
Filter: (((state)::text = 'opened'::text) AND ((merge_status)::text = 'can_be_merged'::text))
Rows Removed by Filter: 2462615
Planning time: 0.215 ms
Execution time: 6428.176 ms
(7 rows)
For work this around, we're able to iterate at every existing MR, given ResetMergeStatus
already handles the filter with a BETWEEN
query (with the given IDs).
The update query explain analyze
Update on merge_requests (cost=5312.71..5745.20 rows=278 width=1183) (actual time=123.595..123.595 rows=0 loops=1)
-> Bitmap Heap Scan on merge_requests (cost=5312.71..5745.20 rows=278 width=1183) (actual time=102.980..103.986 rows=56 loops=1)
Recheck Cond: ((id >= 1) AND (id <= 10000) AND ((state)::text = 'opened'::text) AND ((merge_status)::text = 'can_be_merged'::text))
Heap Blocks: exact=53
-> BitmapAnd (cost=5312.71..5312.71 rows=278 width=0) (actual time=102.921..102.921 rows=0 loops=1)
-> Bitmap Index Scan on merge_requests_pkey (cost=0.00..163.15 rows=7859 width=0) (actual time=2.038..2.038 rows=7761 loops=1)
Index Cond: ((id >= 1) AND (id <= 10000))
-> Bitmap Index Scan on index_merge_requests_on_state_and_merge_status (cost=0.00..5149.17 rows=859922 width=0) (actual time=98.689..98.689 rows=143693 loops=1)
Index Cond: (((state)::text = 'opened'::text) AND ((merge_status)::text = 'can_be_merged'::text))
Planning time: 5.454 ms
Execution time: 123.725 ms
This will take a bit longer, though.
Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/63036
Does this MR meet the acceptance criteria?
Conformity
Edited by 🤖 GitLab Bot 🤖