Fix storing first_mentioned_in_commit_at attribute
What does this MR do and why?
This change fixes the way we calculate and store the first_mentioned_in_commit_at attribute. Instead of looking at the most recent commit date, the system will check the earlies commit date looking back to a maximum of 100 commits. The change also includes a BG migration that attempts to fix the existing records.
BG migration:
- Batch over
issue_metrics
viaissue_id
wherefirst_mentioned_in_commit_at
was set later than 2019 - For each batch, do sub-batching. For each
issue_metrics_record
: - Find
merge_requests_closing_isues
records related to the issue. - Look up the MR diffs, look at the most recent diff.
- Look at the diff commits, take the
authored_date
column. - If the found
authored_date
is earlier thanfirst_mentioned_in_commit_at
, then update theissue_metrics
row.
Database
Queries
- Scheduling query 1: https://explain.depesz.com/s/b8S9
- Scheduling query 2: https://explain.depesz.com/s/d6DX
- Update query: https://explain.depesz.com/s/U29q (timing jumps between 300ms and 1s)
Migration
The index creation took 2 minutes on db-lab.
Up:
== 20211004110500 AddTemporaryIndexToIssueMetrics: migrating ==================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:issue_metrics, :issue_id, {:where=>"EXTRACT(YEAR FROM first_mentioned_in_commit_at) > 2019", :name=>"index_issue_metrics_first_mentioned_in_commit", :algorithm=>:concurrently})
-> 0.0030s
-- execute("SET statement_timeout TO 0")
-> 0.0006s
-- add_index(:issue_metrics, :issue_id, {:where=>"EXTRACT(YEAR FROM first_mentioned_in_commit_at) > 2019", :name=>"index_issue_metrics_first_mentioned_in_commit", :algorithm=>:concurrently})
-> 0.0070s
-- execute("RESET statement_timeout")
-> 0.0006s
== 20211004110500 AddTemporaryIndexToIssueMetrics: migrated (0.0161s) =========
== 20211004110927 ScheduleFixFirstMentionedInCommitAtJob: migrating ===========
-- Scheduled 1 FixFirstMentionedInCommitAt jobs with a maximum of 10000 records per batch and an interval of 120 seconds.
The migration is expected to take at least 120 seconds. Expect all jobs to have completed after 2021-10-04 17:42:20 UTC."
== 20211004110927 ScheduleFixFirstMentionedInCommitAtJob: migrated (0.0147s) ==
Down:
== 20211004110927 ScheduleFixFirstMentionedInCommitAtJob: reverting ===========
== 20211004110927 ScheduleFixFirstMentionedInCommitAtJob: reverted (0.0000s) ==
== 20211004110500 AddTemporaryIndexToIssueMetrics: reverting ==================
-- transaction_open?()
-> 0.0000s
-- indexes(:issue_metrics)
-> 0.0025s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- remove_index(:issue_metrics, {:algorithm=>:concurrently, :name=>"index_issue_metrics_first_mentioned_in_commit"})
-> 0.0059s
-- execute("RESET statement_timeout")
-> 0.0006s
== 20211004110500 AddTemporaryIndexToIssueMetrics: reverted (0.0134s) =========
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #339508 (closed)
Edited by Adam Hegyi