Skip to content

Populate MR metrics with events table information (migration)

Oswaldo Ferreira requested to merge osw-update-mr-metrics-with-events-data into master

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:

  1. Check if the merge_request_metrics record has a particular value
  2. 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?

Edited by Oswaldo Ferreira

Merge request reports