Skip to content

Populate and migrate issue_email_participants

Lee Tickett requested to merge 288715-populate-and-migration-issue_email_participants into master

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

Availability and Testing

Related to #288715 (closed)

Edited by Lee Tickett

Merge request reports