Improve PipelineProcessWorker performance by using the replica for reads

Originating discussion: gitlab-com/gl-infra/scalability#3771 (comment 2074733142)

Also see #432989. The analysis may be dated since its 9 months ago but we see PipelineProcessWorker contributing to the to 3rd highest query by total time.

Given PipelineProcessWorker's notable footprint on the main db, we should re-examine the worker's logic to identify areas where we could use the replica.

Proposal(s)

1. Move pipeline.all_merge_requests out of the Pipeline state machine transition and into a background worker

The query to fetch all merge requests for a pipeline is not a good one: (query 3) https://console.postgres.ai/gitlab/gitlab_production/reports/3403/files/558154/md

As of the most recent available sample, execution from the PipelineProcessWorker alone accounts for 5.71% of CPU time on the primary (non-CI) database. To mitigate this, we're moving the execution of the query to the top of the AutoMergeProcessWorker and changing it's data consistency configuration to sticky. It can still run queries against the primary as it has before, but it will do this relatively cheap but very high volume read query first against replicas, which is exactly what we have replicas for.

Bonus points that it moves AutoMergeProcess work out of the PipelineProcessWorker, which is kind of overloaded with a ton of stuff because it has historically handled everything in the state transitions.

pipeline.all_merge_requests may have, once upon a time, been cheap enough to leave in the state transition block, but that is no longer the case.

MRs

  1. Set AutoMergeProcessWorker data consistency to ... (!167234 - merged) • drew stachon • 17.5
  2. Set up the AutoMergeProcessWorker to accept a h... (!168123 - merged) • drew stachon • 17.5
  3. Move a field check into the query instead of th... (!168004 - merged) • drew stachon • 17.5
  4. Move completed pipeline merge request querying ... (!167095 - merged) • drew stachon • 17.6
  5. Remove AutoMergeProcessWorker parameter backwar... (#497247) • Unassigned • Backlog

Rollout Issues

  1. [Feature flag] Rollout of `auto_merge_process_w... (#496567 - closed) • drew stachon • 17.9 • On track
  2. [Feature flag] Enable auto_merge_process_worker... (#499877 - closed) • drew stachon • 17.9 • On track

2. Set per-database data consistency for PipelineProcessWorker to main: sticky and ci: always

This sets the data consistency for connections the main database as sticky while preserving the behaviour of ci connections to always use the primary db.

See context in #483008 (comment 2170392523) and gitlab-com/gl-infra/scalability#3805.

This was shipped behind a feature flag in !166729 (merged)

Rollout Issues

  1. #500401 (closed)
Edited by drew stachon