Skip to content

Avoid DB timeouts when scheduling migrations

Oswaldo Ferreira requested to merge osw-fix-post-dep-migration-with-timeout into master

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 🤖

Merge request reports