Skip to content

Add merge requests total time to merge field on project

Magdalena Frankiewicz requested to merge add-mean-time-to-merge-be into master

What does this MR do?

This MR exposes the total_time_to_merge aggregation via the MergeRequestConnectionType GraphQL API. We sum the time between merge_request_metrics.created_at (created when MR is created) and merge_request_metrics.merged_at records.

Note: this API will be used by the merge request analytics frontend. We decided to expose this data via GraphQL because the merge request analytics UI is already using GraphQL.

The main goal is to calculate MTTM (mean time to merge) for one year.

How:

The frontend will issue several, smaller GraphQL queries (querying 1 year could time out) with a particular date range (1 month) and request the totalTimeToMerge and count fields.

By aggregating the data, we get the MTTM. Formula: SUM(total_time_to_merges) / SUM(counts)

Resolves Backend portion of #246820 (closed)

Getting the totalTimeToMerge and count:

{
  project(fullPath: $fullPath) {    
    mergeRequests(state: merged, mergedAfter: "2020-06-01", mergedBefore: "2020-07-01") {
      totalTimeToMerge,
      count
    }
  }
}

Database review

  1. total_time_to_merge
SELECT EXTRACT(epoch
               FROM SUM(AGE(merge_request_metrics.merged_at, merge_request_metrics.created_at)))
FROM "merge_requests"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE "merge_requests"."target_project_id" = 278964
  AND ("merge_requests"."state_id" IN (3))
  AND "merge_requests"."target_project_id" = "merge_request_metrics"."target_project_id"
  AND "merge_request_metrics"."merged_at" >= '2020-03-01 23:00:00'
  AND "merge_request_metrics"."merged_at" <= '2020-04-01 23:00:00'
  AND "merge_request_metrics"."merged_at" > "merge_request_metrics"."created_at"

Execution plan:

Looks like slow I/O on db-lab, I get pretty good numbers on PRD though.

Migration

Up:

== 20201104142036 AddIndexToMergeRequestMetricsTargetProjectId: migrating =====
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:merge_request_metrics, "target_project_id, merged_at, created_at", {:where=>"merged_at > created_at", :name=>"index_mr_metrics_on_target_project_id_merged_at_created_at", :algorithm=>:concurrently})
   -> 0.0035s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- add_index(:merge_request_metrics, "target_project_id, merged_at, created_at", {:where=>"merged_at > created_at", :name=>"index_mr_metrics_on_target_project_id_merged_at_created_at", :algorithm=>:concurrently})
   -> 0.0118s
-- execute("RESET ALL")
   -> 0.0003s
== 20201104142036 AddIndexToMergeRequestMetricsTargetProjectId: migrated (0.0161s)

Down:

== 20201104142036 AddIndexToMergeRequestMetricsTargetProjectId: reverting =====
-- transaction_open?()
   -> 0.0001s
-- indexes(:merge_request_metrics)
   -> 0.0044s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- remove_index(:merge_request_metrics, {:algorithm=>:concurrently, :name=>"index_mr_metrics_on_target_project_id_merged_at_created_at})
   -> 0.0030s
-- execute("RESET ALL")
   -> 0.0005s
== 20201104142036 AddIndexToMergeRequestMetricsTargetProjectId: reverted (0.0095s)

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • [-] Label as security and @ mention @gitlab-com/gl-security/appsec
  • [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • [-] Security reports checked/validated by a reviewer from the AppSec team
Edited by Mayra Cabrera

Merge request reports