Populate MR metrics with events table information (migration)
What does this MR do?
Problem
merge_request_metrics
has outdated (or nullified) data on latest_closed_at
, latest_closed_by_id
and merged_by_id
.
This is the 2nd try on that task as we've experienced timeouts in the first one (see https://gitlab.com/gitlab-org/gitlab-ce/issues/47676). Specifically, we had timeouts in the previous UPDATE
statement, not in the INSERT
, but since we had to flush Redis to stop the processing, now we do both again.
How we're solving
Similarly as the first try, we're scheduling batches of 10k merge requests, where we fetch the latest data from events
table and update merge_request_metrics
in the process. But now we're using a slightly more performatic approach (mentioned by NikolayS
at https://gitlab.com/gitlab-org/gitlab-ce/issues/47676#note_101536973).
What that would improve
In the codebase we now have to:
- Check if the
merge_request_metrics
record has a particular value - If it doesn't we fallback to
events
Depending on the use-case we may run into N+1's, which isn't good :)
Query plan
Taken from https://gitlab.com/gitlab-org/gitlab-ce/issues/47676#note_101536973
gitlab_dbteam=# explain analyze
update merge_request_metrics
set
(latest_closed_at, latest_closed_by_id) = (
select updated_at, author_id
from events
where
target_id = merge_request_id
and target_type = 'MergeRequest'
and action = 3
order by id desc
limit 1
),
merged_by_id = (
select author_id
from events
where
target_id = merge_request_id
and target_type = 'MergeRequest'
and action = 7
order by id desc
limit 1
)
where
merge_request_id between 1343658 and 1353658
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on merge_request_metrics (cost=0.43..80452.97 rows=9308 width=114) (actual time=181.252..181.252 rows=0 loops=1)
-> Index Scan using index_merge_request_metrics on merge_request_metrics (cost=0.43..80452.97 rows=9308 width=114) (actual time=0.081..109.625 rows=8719 loops=1)
Index Cond: ((merge_request_id >= 1343658) AND (merge_request_id <= 1353658))
SubPlan 1
-> Limit (cost=3.60..3.61 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=8719)
-> Sort (cost=3.60..3.61 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=8719)
Sort Key: events.id DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_events_on_target_type_and_target_id on events (cost=0.57..3.59 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=8719)
Index Cond: (((target_type)::text = 'MergeRequest'::text) AND (target_id = merge_request_metrics.merge_request_id))
Filter: (action = 7)
Rows Removed by Filter: 1
SubPlan 2 (returns $2,$3)
-> Limit (cost=3.60..3.61 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=8719)
-> Sort (cost=3.60..3.61 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=8719)
Sort Key: events_1.id DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_events_on_target_type_and_target_id on events events_1 (cost=0.57..3.59 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=8719)
Index Cond: (((target_type)::text = 'MergeRequest'::text) AND (target_id = merge_request_metrics.merge_request_id))
Filter: (action = 3)
Rows Removed by Filter: 2
Planning time: 0.248 ms
Execution time: 181.302 ms
(23 rows)
Time: 191.894 ms
What are the relevant issue numbers?
Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/41587
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. -
Security reports checked/validated by reviewer