Populate and migrate issue_email_participants
What does this MR do?
Closes #288715 (closed) by ensuring new service desk issues populate issue_email_participants
(as well as issues.external_author
NOT instead of). Then copying everything from issues.external_author
in to issue_email_participants
.
Seed Script
Requires a few replacements (i.e. 43
= next iid for the project... 99
= support-bot user id and 6
= test project). This did the 150K service desk issues, then tweaked a little and changed 150000
to 6500000
to insert the non-service desk issues.
INSERT INTO
issues
(
title,
author_id,
project_id,
created_at,
updated_at,
iid,
service_desk_reply_to
)
SELECT
'Seed issue ' || seq as title,
99 AS author_id,
6 AS project_id,
NOW() AS created_at,
NOW() AS updated_at,
43 + seq,
'gl' || seq || '@gmail.com'
FROM generate_series(1, 150000) AS seq
DB Migration Log
lee@cc-gdk-2:~/gitlab-development-kit/gitlab$ bundle exec rake db:migrate:up VERSION=20201221225303 RAILS_ENV=development
== 20201221225303 AddServiceDeskReplyToIsNotNullIndexOnIssues: migrating ======
-- transaction_open?()
-> 0.0000s
-- index_exists?(:issues, [:id], {:name=>"idx_on_issues_where_service_desk_reply_to_is_not_null", :where=>"service_desk_reply_to IS NOT NULL", :algorithm=>:concurrently})
-> 0.0074s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- add_index(:issues, [:id], {:name=>"idx_on_issues_where_service_desk_reply_to_is_not_null", :where=>"service_desk_reply_to IS NOT NULL", :algorithm=>:concurrently})
-> 0.0056s
-- execute("RESET ALL")
-> 0.0002s
== 20201221225303 AddServiceDeskReplyToIsNotNullIndexOnIssues: migrated (0.0139s)
lee@cc-gdk-2:~/gitlab-development-kit/gitlab$ bundle exec rake db:migrate:down VERSION=20201221225303 RAILS_ENV=development
== 20201221225303 AddServiceDeskReplyToIsNotNullIndexOnIssues: reverting ======
-- transaction_open?()
-> 0.0000s
-- indexes(:issues)
-> 0.0077s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- remove_index(:issues, {:algorithm=>:concurrently, :name=>"idx_on_issues_where_service_desk_reply_to_is_not_null"})
-> 0.0016s
-- execute("RESET ALL")
-> 0.0002s
== 20201221225303 AddServiceDeskReplyToIsNotNullIndexOnIssues: reverted (0.0101s)
Background Migration Details:
We have around 225k issues
with service_desk_reply_to
gitlabhq_production=> SELECT COUNT(*) FROM issues WHERE issues.service_desk_reply_to IS NOT NULL;
count
--------
225523
(1 row)
With a batch size of 100_000
, post-migration will schedule 3
jobs, we don't have an accurate timing of how long the insert will take but since we're doing bulk insert, we assume it should be fast.
Query details
Index creation on postgres.ai
https://postgres.ai/console/shared/25e07884-2ca0-4033-8f39-eae069acfce5
CREATE INDEX idx_on_issues_where_desk_reply_not_null ON issues USING btree (service_desk_reply_to) WHERE (service_desk_reply_to IS NOT NULL);
The query has been executed. Duration: 4.373 min
Query to filter issues
SELECT
*
FROM
issues
WHERE
issues.service_desk_reply_to IS NOT NULL;
Cold cache: https://postgres.ai/console/shared/b178e5f4-f02e-4906-821f-4c85ec88e727
Time: 1.271 s
- planning: 0.845 ms
- execution: 1.270 s
- I/O read: 67.947 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 183205 (~1.40 GiB) from the buffer pool
- reads: 1045 (~8.20 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Warm cache: https://postgres.ai/console/shared/7c62cb3e-a4fd-41dc-99a5-d3091b160b19
Time: 317.445 ms
- planning: 0.256 ms
- execution: 317.189 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 98056 (~766.10 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry - [-] Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides - [-] Separation of EE specific content
Availability and Testing
- [-] Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
- [-] Tested in all supported browsers
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Related to #288715 (closed)