Add index to find stuck merge requests.
What does this MR do?
It adds an index to speed up a query for the stuck merge requests worker.
In staging, this yields a nice and fast plan now after adding the index:
gitlabhq_production=# explain analyze SELECT id, merge_jid FROM "merge_requests" WHERE ("merge_requests"."state" IN ('locked')) AND ("merge_requests"."merge_jid" IS NOT NULL) ORDER BY "merge_requests"."id" ASC LIMIT 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=491703.90..491704.15 rows=100 width=29) (actual time=5242.025..5242.025 rows=0 loops=1)
-> Sort (cost=491703.90..491704.28 rows=153 width=29) (actual time=5242.024..5242.024 rows=0 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on merge_requests (cost=0.00..491698.35 rows=153 width=29) (actual time=5241.997..5241.997 rows=0 loops=1)
Filter: ((merge_jid IS NOT NULL) AND ((state)::text = 'locked'::text))
Rows Removed by Filter: 4223289
Planning time: 2.103 ms
Execution time: 5242.054 ms
(9 rows)
gitlabhq_production=# create index concurrently abrandl_tmp1 ON merge_requests (id, merge_jid) WHERE merge_jid IS NOT NULL AND state='locked';
CREATE INDEX
gitlabhq_production=# explain analyze SELECT id, merge_jid FROM "merge_requests" WHERE ("merge_requests"."state" IN ('locked')) AND ("merge_requests"."merge_jid" IS NOT NULL) ORDER BY "merge_requests"."id" ASC LIMIT 100;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.12..4.10 rows=100 width=29) (actual time=0.003..0.003 rows=0 loops=1)
-> Index Only Scan using abrandl_tmp1 on merge_requests (cost=0.12..6.13 rows=151 width=29) (actual time=0.002..0.002 rows=0 loops=1)
Heap Fetches: 0
Planning time: 0.549 ms
Execution time: 0.043 ms
(5 rows)
Migration:
abrandl-gl:gitlab/ (ab-45608-stuck-mr-query) $ spring rake db:migrate [17:28:07]
Running via Spring preloader in process 16711
/home/abrandl-gl/workspace/gitlab-org/gdk-ce/gitlab/spec/factories/projects.rb:4: warning: already initialized constant PAGES_ACCESS_LEVEL_SCHEMA_VERSION
/home/abrandl-gl/workspace/gitlab-org/gdk-ce/gitlab/spec/factories/projects.rb:4: warning: previous definition of PAGES_ACCESS_LEVEL_SCHEMA_VERSION was here
== 20181101144347 AddIndexForStuckMrQuery: migrating ==========================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:merge_requests, [:id, :merge_jid], {:where=>"merge_jid IS NOT NULL and state = 'locked'", :algorithm=>:concurrently})
-> 0.0106s
-- execute("SET statement_timeout TO 0")
-> 0.0003s
-- add_index(:merge_requests, [:id, :merge_jid], {:where=>"merge_jid IS NOT NULL and state = 'locked'", :algorithm=>:concurrently})
-> 0.0081s
-- execute("RESET ALL")
-> 0.0007s
== 20181101144347 AddIndexForStuckMrQuery: migrated (0.0202s) =================
s% abrandl-gl:gitlab/ (ab-45608-stuck-mr-query) $ spring rake db:rollback [17:28:13]
Running via Spring preloader in process 16777
/home/abrandl-gl/workspace/gitlab-org/gdk-ce/gitlab/spec/factories/projects.rb:4: warning: already initialized constant PAGES_ACCESS_LEVEL_SCHEMA_VERSION
/home/abrandl-gl/workspace/gitlab-org/gdk-ce/gitlab/spec/factories/projects.rb:4: warning: previous definition of PAGES_ACCESS_LEVEL_SCHEMA_VERSION was here
== 20181101144347 AddIndexForStuckMrQuery: reverting ==========================
-- transaction_open?()
-> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
-> 0.0006s
-- index_exists?(:merge_requests, [:id, :merge_jid], {:where=>"merge_jid IS NOT NULL and state = 'locked'", :algorithm=>:concurrently})
-> 0.0111s
-- execute("SET statement_timeout TO 0")
-> 0.0003s
-- remove_index(:merge_requests, {:where=>"merge_jid IS NOT NULL and state = 'locked'", :algorithm=>:concurrently, :column=>[:id, :merge_jid]})
-> 0.0109s
-- execute("RESET ALL")
-> 0.0005s
== 20181101144347 AddIndexForStuckMrQuery: reverted (0.0240s) =================
What are the relevant issue numbers?
See https://gitlab.com/gitlab-org/gitlab-ce/issues/45608.
Does this MR meet the acceptance criteria?
-
Changelog entry added, if necessary -
Documentation created/updated -
Tests added for this feature/bug -
Conforms to the code review guidelines -
Conforms to the merge request performance guidelines -
Conforms to the style guides -
Conforms to the database guides -
Link to e2e tests MR added if this MR has Requires e2e tests label. See the Test Planning Process.
Merge request reports
Activity
mentioned in issue #45608 (closed)
1 Error f19b6ef3: The commit subject must not end with a period 1 Warning You’ve made some app changes, but didn’t add any tests.
That’s OK as long as you’re refactoring existing code,
but please consider adding any of the ~backstage, ~Documentation, QA labels.1 Message This merge request adds or changes files that require a review from the Database team. Database Review
The following files require a review from the Database team:
db/migrate/20181101144347_add_index_for_stuck_mr_query.rb
db/schema.rb
To make sure these changes are reviewed, take the following steps:
- Edit your merge request, and add
gl-database
to the list of Group approvers. - Mention
@gl-database
in a separate comment, and explain what needs to be reviewed by the team. Please don't mention the team until your changes are ready for review.
Commit message standards
One or more commit messages do not meet our Git commit message standards. For more information on how to write a good commit message, take a look at How to Write a Git Commit Message.
Here is an example of a good commit message:
Reject ruby interpolation in externalized strings When using ruby interpolation in externalized strings, they can't be detected. Which means they will never be presented to be translated. To mix variables into translations we need to use `sprintf` instead. Instead of: _("Hello #{subject}") Use: _("Hello %{subject}") % { subject: 'world' }
This is an example of a bad commit message:
updated README.md
This commit message is bad because although it tells us that README.md is updated, it doesn't tell us why or how it was updated.
Generated by
DangerEdited by 🤖 GitLab Bot 🤖marked the checklist item Changelog entry added, if necessary as completed
marked the checklist item Conforms to the database guides as completed
added bugperformance label
changed milestone to %11.6
Would you mind taking a look and approve/merge please? @smcgivern
assigned to @smcgivern
Thanks @abrandl, very nice! We're still on 11.5, so I'll set that milestone to be explicit
changed milestone to %11.5
mentioned in commit 0ae4eb03
mentioned in issue gitlab-org/release/tasks#515 (closed)