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)
Merge request reports
Activity
changed milestone to %14.4
assigned to @ahegyi
Reviewer roulette
Changes that require review have been detected!
Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:
Category Reviewer Maintainer backend Hordur Freyr Yngvason ( @hfyngvason
) (UTC-4, 6 hours behind@ahegyi
)Kerri Miller ( @kerrizor
) (UTC-7, 9 hours behind@ahegyi
)database Diogo Frazão ( @dfrazao-gitlab
) (UTC+2, same timezone as@ahegyi
)Mayra Cabrera ( @mayra-cabrera
) (UTC-5, 7 hours behind@ahegyi
)~migration No reviewer available No maintainer available To spread load more evenly across eligible reviewers, Danger has picked a candidate for each review slot, based on their timezone. Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.
To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.
Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.
If needed, you can retry the
danger-review
job that generated this comment.Generated by
Dangeradded 1 commit
- 4f42f34c - Fix storing first_mentioned_in_commit_at attribute
added 1 commit
- fa00a9d3 - Fix storing first_mentioned_in_commit_at attribute
- A deleted user
added databasereview pending label
- A deleted user
added database label
removed ~20757942 database databasereview pending labels
added 1 commit
- 62675d69 - Fix storing first_mentioned_in_commit_at attribute
- A deleted user
added databasereview pending label
- A deleted user
added database label
removed ~20757942 database databasereview pending labels
added 1 commit
- ab06a58e - Fix storing first_mentioned_in_commit_at attribute
- A deleted user
added databasereview pending label
- A deleted user
added database label
removed ~20757942 database databasereview pending labels
added 1 commit
- 900db121 - Fix storing first_mentioned_in_commit_at attribute
- A deleted user
added databasereview pending label
- A deleted user
added database label
Database migrations
2 Warnings 20211004110500 - AddTemporaryIndexToIssueMetrics had a query that exceeded timing
guidelines. Run time should not exceed 100ms, but it was 121.93ms. Please consider possible options
to improve the query performance.select pg_database_size(current_database())
/*application:test,db_config_name:main*/20211004110927 - ScheduleFixFirstMentionedInCommitAtJob had a query that exceeded timing
guidelines. Run time should not exceed 100ms, but it was 118.73ms. Please consider possible options
to improve the query performance.select pg_database_size(current_database())
/*application:test,db_config_name:main*/Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).
Migration Type Total runtime Result DB size change 20211004110500 - AddTemporaryIndexToIssueMetrics Post deploy 114.8 s +84.63 MiB 20211004110927 - ScheduleFixFirstMentionedInCommitAtJob Post deploy 93.9 s +8.00 KiB Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 1187 0.1 seconds - 1 second 10 1 second - 5 minutes 1 5 minutes + 0 Migration: 20211004110500 - AddTemporaryIndexToIssueMetrics- Type: Post deploy
- Duration: 114.8 s
- Database size change: +84.63 MiB
Query Calls Total Time Max Time Mean Time Rows CREATE INDEX CONCURRENTLY "index_issue_metrics_first_mentioned_in_commit" ON "issue_metrics" ("issue_id")
WHERE EXTRACT(YEAR
FROM first_mentioned_in_commit_at) > 2019 /*application:test,db_config_name:main*/1 112846.6 ms 112846.6 ms 112846.6 ms 0 select pg_database_size(current_database()) /*application:test,db_config_name:main*/
1 121.9 ms 121.9 ms 121.9 ms 1 INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version" /*application:test,db_config_name:main*/
1 30.9 ms 30.9 ms 30.9 ms 1 SELECT "feature_gates".*
FROM "feature_gates"
WHERE "feature_gates"."feature_key" = $1 /*application:test,db_config_name:main*/1 1.7 ms 1.7 ms 1.7 ms 1 SELECT "schema_migrations"."version"
FROM "schema_migrations"
ORDER BY "schema_migrations"."version" ASC /*application:test,db_config_name:main*/1 1.3 ms 1.3 ms 1.3 ms 4218 select pg_stat_statements_reset() /*application:test,db_config_name:main*/
1 0.2 ms 0.2 ms 0.2 ms 1 SELECT "postgres_async_indexes".*
FROM "postgres_async_indexes"
WHERE "postgres_async_indexes"."name" = $1
LIMIT $2 /*application:test,db_config_name:main*/1 0.1 ms 0.1 ms 0.1 ms 0 Histogram for AddTemporaryIndexToIssueMetrics
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 4 0.1 seconds - 1 second 1 1 second - 5 minutes 1 5 minutes + 0 Migration: 20211004110927 - ScheduleFixFirstMentionedInCommitAtJob- Type: Post deploy
- Duration: 93.9 s
- Database size change: +8.00 KiB
Query Calls Total Time Max Time Mean Time Rows SELECT "issue_metrics"."issue_id"
FROM "issue_metrics"
WHERE (EXTRACT($1
FROM first_mentioned_in_commit_at) > $2) AND "issue_metrics"."issue_id" >= $3
ORDER BY "issue_metrics"."issue_id" ASC
LIMIT $4
OFFSET $5 /*application:test,db_config_name:main*/396 3804.6 ms 89.1 ms 9.6 ms 395 INSERT INTO "background_migration_jobs" ("created_at", "updated_at", "class_name", "arguments") VALUES ($1, $2, $3, $4) RETURNING "id" /*application:test,db_config_name:main*/
396 150.4 ms 48.3 ms 0.4 ms 396 select pg_database_size(current_database()) /*application:test,db_config_name:main*/
1 118.7 ms 118.7 ms 118.7 ms 1 SELECT MIN("issue_metrics"."issue_id"), MAX("issue_metrics"."issue_id")
FROM "issue_metrics"
WHERE (EXTRACT($1
FROM first_mentioned_in_commit_at) > $2) AND "issue_metrics"."issue_id" >= $3 AND "issue_metrics"."issue_id" < $4 /*application:test,db_config_name:main*/395 29.8 ms 1.8 ms 0.1 ms 395 SELECT "schema_migrations"."version"
FROM "schema_migrations"
ORDER BY "schema_migrations"."version" ASC /*application:test,db_config_name:main*/1 1.5 ms 1.5 ms 1.5 ms 4219 select pg_stat_statements_reset() /*application:test,db_config_name:main*/
1 0.2 ms 0.2 ms 0.2 ms 1 INSERT INTO "schema_migrations" ("version") VALUES ($1) RETURNING "version" /*application:test,db_config_name:main*/
1 0.1 ms 0.1 ms 0.1 ms 1 SELECT "issue_metrics"."issue_id"
FROM "issue_metrics"
WHERE (EXTRACT($1
FROM first_mentioned_in_commit_at) > $2)
ORDER BY "issue_metrics"."issue_id" ASC
LIMIT $3 /*application:test,db_config_name:main*/1 0.0 ms 0.0 ms 0.0 ms 1 SELECT MIN("issue_metrics"."issue_id"), MAX("issue_metrics"."issue_id")
FROM "issue_metrics"
WHERE (EXTRACT($1
FROM first_mentioned_in_commit_at) > $2) AND "issue_metrics"."issue_id" >= $3 /*application:test,db_config_name:main*/1 0.0 ms 0.0 ms 0.0 ms 1 Histogram for ScheduleFixFirstMentionedInCommitAtJob
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 1183 0.1 seconds - 1 second 9 1 second - 5 minutes 0 5 minutes + 0
Other migrations pending on GitLab.com
Migration Type Total runtime Result DB size change Clone Details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-826061
2021-10-05 09:29:15 UTC 2021-10-05 07:59:56 UTC 2021-10-05 21:35:18 +0000 Artifacts
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
- A deleted user
added database-testing-automation label
Hi, @m_frankiewicz,
Could you do the BE review?
requested review from @m_frankiewicz
- Resolved by Vitali Tatarintev
Hi, @alexbuijs! Could you do the DB review?
requested review from @alexbuijs
- Resolved by Vitali Tatarintev
removed review request for @m_frankiewicz
added databasereviewed label and removed databasereview pending label
requested review from @tigerwnz and removed review request for @alexbuijs
@alexbuijs
, thanks for approving this merge request.This is the first time the merge request is approved. To ensure full test coverage, a new pipeline has been started.
For more info, please refer to the following links:
added databaseapproved label and removed databasereviewed label
removed review request for @tigerwnz
requested review from @ck3g
enabled an automatic merge when the pipeline for 825630b5 succeeds
mentioned in commit ac1c0694
added workflowstaging-canary label and removed workflowready for development label
added workflowstaging label and removed workflowstaging-canary label
added workflowcanary label and removed workflowstaging label
added workflowproduction label and removed workflowcanary label
added releasedcandidate label
mentioned in merge request kubitus-project/kubitus-installer!306 (merged)
mentioned in issue #343724 (closed)
mentioned in issue #345416
added releasedpublished label and removed releasedcandidate label
mentioned in merge request !81409 (merged)
mentioned in merge request !113303 (merged)