Skip to content

Draft: Move pipeline counts into DB

Kerri Miller requested to merge 276943-reduce-lcp-to-2-5s-for-sourcecode_mr_large into master

What does this MR do?

Initial investigation of performance hot spots on MR pages revealed that we had some queries that could use some deeper digging. I poked at the query that loads pipelines for the MR in order to display the number of pipelines in the MR page nav bar tab (#276943 (comment 452055216)) The main takeaway is that since we only use this large, complex, slow (250-300ms) query to generate a count, it seems like a good use case for some kind of cached counter. I proposed initially storing this in redis, but after some feedback I opted for a DB-based solution, at least for this MR.

Regardless of where we store this information, we need to store 3 different counts - the total number of pipelines, the number of pipelines created with the target project, and the number for the source project as well. This is to mirror the display contexts that change based on user read access, as we can see in Ci::PipelinesForMergeRequestFinder#execute. Piggybacking the counting access into this finder, we're able to take advantage of its routines and keep functionality together, although it might be a case for future extraction as we iterate.

Since the number of MergeRequests is very large, rather than doing a data migration, I chose to default the new counter columns to nil and use that state to force an initial population of the values when attempting to read the value. Additionally, we will attempt to update these values whenever a new Ci::Pipeline is created (via and after_create)

Migrations

up

== 20201128192713 AddSourceAndTargetProjectCountColumnsToMergeRequests: migrating
-- add_column(:merge_requests, :total_pipelines_count, :integer)
   -> 0.0015s
-- add_column(:merge_requests, :source_project_pipelines_count, :integer)
   -> 0.0004s
-- add_column(:merge_requests, :target_project_pipelines_count, :integer)
   -> 0.0003s
== 20201128192713 AddSourceAndTargetProjectCountColumnsToMergeRequests: migrated (0.0023s)

down

== 20201128192713 AddSourceAndTargetProjectCountColumnsToMergeRequests: reverting
-- remove_column(:merge_requests, :target_project_pipelines_count, :integer)
   -> 0.0013s
-- remove_column(:merge_requests, :source_project_pipelines_count, :integer)
   -> 0.0003s
-- remove_column(:merge_requests, :total_pipelines_count, :integer)
   -> 0.0003s
== 20201128192713 AddSourceAndTargetProjectCountColumnsToMergeRequests: reverted (0.0038s)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Related to #276943 (closed)

Edited by Kerri Miller

Merge request reports