Draft: Bound pipeline lookups that scan every pipeline of a commit

Tiger Watsonrequested to merge
fix/bound-pipeline-lookups-for-heavy-commits into master

What does this MR do and why?

Some projects run pipelines for other projects through pipeline schedules. Their default branch rarely changes, so one commit can collect tens of thousands of pipelines. Any lookup for the newest pipeline of that commit then reads every one of them: the only sha index on p_ci_pipelines is (project_id, sha) without id, Postgres estimates a single row for the sha, and it heap-fetches all of them to apply the source/ref filters before sorting. On Database Lab such a lookup took 38.9 s cold (282 MiB read) and hit the statement timeout, which failed the blob page, the commit page, merge request diffs and the merge request widget.

This MR bounds each of those lookups with the existing with_fast_read_statement_timeout helper (4.5 s, read replicas) and omits the optional section when the bound is hit, following !257704 (merged):

  • Gitlab::CodeNavigationPath#build (blob page and all merge request diff renders): the page renders without code navigation. Projects without LSIF artifacts already skip this lookup since !264106 (merged); the bound covers projects that have them.
  • Ci::CommitWithPipeline#last_pipeline (commit page, project cards, GraphQL project.pipeline): the commit box renders without the pipeline summary. GraphQL callers cannot distinguish "no pipeline" from "lookup timed out"; previously the query errored instead.
  • MergeRequest#merge_pipeline (cached_widget.json for merged merge requests): the widget renders without the merged result pipeline, the same JSON a merged merge request without pipelines already returns.

The rescue sits inside each memoized block so a timeout is cached per request and later calls on the same object (two in define_commit_box_vars, one per diff file) do not wait again. The code navigation circuit breaker is not tripped on a database timeout: its key is global, so a trip would disable code navigation for everyone for 24 h.

The commit page Pipelines badge count, which had the same cost, was capped separately in !264101 (merged).

Not covered here, both GraphQL requests that run after the page has rendered: the commit Pipelines tab table (project.pipelines(sha:) with count) and the blob/tree last-commit pipeline status.

Database

No schema change and no new query shapes. The bounded lookups run unchanged SQL under SET LOCAL statement_timeout = 4500 on the CI database.

For reference: the unbounded newest-pipeline lookup for the affected commit on Database Lab (`gitlab-production-ci`, literals replaced), 36,110 buffers (282 MiB); 38.9 s on the first, cold run, the attached plan is the warm rerun 
SELECT "p_ci_pipelines".* FROM "p_ci_pipelines"
WHERE "p_ci_pipelines"."project_id" = ?
  AND ("p_ci_pipelines"."source" IN (1, 2, 3, 4, 5, 6, 7, 8, 10, 11) OR "p_ci_pipelines"."source" IS NULL)
  AND "p_ci_pipelines"."sha" = ?
ORDER BY "p_ci_pipelines"."id" DESC, "p_ci_pipelines"."partition_id" DESC LIMIT 1
execution plan (EXPLAIN ANALYZE):
 Limit  (cost=54.57..54.57 rows=1 width=485) (actual time=83.372..83.384 rows=1 loops=1)
   Buffers: shared hit=36110
   I/O Timings: read=0.000 write=0.000
   ->  Sort  (cost=54.57..54.61 rows=16 width=485) (actual time=83.370..83.373 rows=1 loops=1)
         Sort Key: p_ci_pipelines.id DESC, p_ci_pipelines.partition_id DESC
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=36110
         I/O Timings: read=0.000 write=0.000
         ->  Append  (cost=0.73..54.49 rows=16 width=485) (actual time=0.052..63.470 rows=36055 loops=1)
               Buffers: shared hit=36107
               I/O Timings: read=0.000 write=0.000
               ->  Index Scan using index_ci_pipelines_on_project_id_and_sha on gitlab_partitions_dynamic.ci_pipelines p_ci_pipelines_1  (cost=0.73..3.75 rows=1 width=395) (actual time=0.051..60.578 rows=36055 loops=1)
                     Index Cond: ((p_ci_pipelines_1.project_id = ?) AND ((p_ci_pipelines_1.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_1.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_1.source IS NULL))
                     Buffers: shared hit=36051
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_103_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_103 p_ci_pipelines_2  (cost=0.59..3.62 rows=1 width=389) (actual time=0.026..0.026 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_2.project_id = ?) AND ((p_ci_pipelines_2.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_2.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_2.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_104_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_104 p_ci_pipelines_3  (cost=0.59..3.62 rows=1 width=387) (actual time=0.023..0.023 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_3.project_id = ?) AND ((p_ci_pipelines_3.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_3.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_3.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_105_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_105 p_ci_pipelines_4  (cost=0.59..3.62 rows=1 width=383) (actual time=0.017..0.017 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_4.project_id = ?) AND ((p_ci_pipelines_4.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_4.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_4.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_106_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_106 p_ci_pipelines_5  (cost=0.59..3.62 rows=1 width=384) (actual time=0.023..0.023 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_5.project_id = ?) AND ((p_ci_pipelines_5.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_5.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_5.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_107_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_107 p_ci_pipelines_6  (cost=0.60..3.62 rows=1 width=408) (actual time=0.013..0.013 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_6.project_id = ?) AND ((p_ci_pipelines_6.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_6.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_6.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_108_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_108 p_ci_pipelines_7  (cost=0.59..3.62 rows=1 width=390) (actual time=0.013..0.014 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_7.project_id = ?) AND ((p_ci_pipelines_7.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_7.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_7.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_109_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_109 p_ci_pipelines_8  (cost=0.59..3.62 rows=1 width=377) (actual time=0.009..0.009 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_8.project_id = ?) AND ((p_ci_pipelines_8.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_8.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_8.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_110_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_110 p_ci_pipelines_9  (cost=0.59..3.62 rows=1 width=406) (actual time=0.012..0.012 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_9.project_id = ?) AND ((p_ci_pipelines_9.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_9.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_9.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_111_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_111 p_ci_pipelines_10  (cost=0.59..3.62 rows=1 width=372) (actual time=0.012..0.012 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_10.project_id = ?) AND ((p_ci_pipelines_10.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_10.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_10.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_112_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_112 p_ci_pipelines_11  (cost=0.59..3.62 rows=1 width=386) (actual time=0.014..0.015 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_11.project_id = ?) AND ((p_ci_pipelines_11.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_11.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_11.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_113_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_113 p_ci_pipelines_12  (cost=0.59..3.62 rows=1 width=417) (actual time=0.009..0.009 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_12.project_id = ?) AND ((p_ci_pipelines_12.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_12.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_12.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_114_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_114 p_ci_pipelines_13  (cost=0.59..3.62 rows=1 width=390) (actual time=0.013..0.013 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_13.project_id = ?) AND ((p_ci_pipelines_13.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_13.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_13.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_115_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_115 p_ci_pipelines_14  (cost=0.59..3.62 rows=1 width=397) (actual time=0.020..0.020 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_14.project_id = ?) AND ((p_ci_pipelines_14.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_14.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_14.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using ci_pipelines_116_project_id_sha_idx on gitlab_partitions_dynamic.ci_pipelines_116 p_ci_pipelines_15  (cost=0.59..3.62 rows=1 width=422) (actual time=0.015..0.015 rows=0 loops=1)
                     Index Cond: ((p_ci_pipelines_15.project_id = ?) AND ((p_ci_pipelines_15.sha)::text = '?'::text))
                     Filter: ((p_ci_pipelines_15.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_15.source IS NULL))
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
               ->  Seq Scan on gitlab_partitions_dynamic.ci_pipelines_117 p_ci_pipelines_16  (cost=0.03..0.03 rows=1 width=1816) (actual time=0.014..0.014 rows=0 loops=1)
                     Filter: ((p_ci_pipelines_16.project_id = ?) AND ((p_ci_pipelines_16.sha)::text = '?'::text) AND ((p_ci_pipelines_16.source = ANY ('{1,2,3,4,5,6,7,8,10,11}'::integer[])) OR (p_ci_pipelines_16.source IS NULL)))
                     I/O Timings: read=0.000 write=0.000


stats:

Time: 135.523 ms
  - planning: 51.818 ms
  - execution: 83.705 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 36110 (~282.10 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

References

  • Follow-up to !257704 (merged), which bounded the coverage delta lookup for the same class of project.
  • Split out of this MR and already merged: !264101 (merged) (commit page badge cap) and !264106 (merged) (skip code navigation lookups without LSIF artifacts).

Screenshots or screen recordings

No visual change.

How to set up and validate locally

  1. To simulate a slow lookup, temporarily add Ci::Pipeline.connection.select_value('SELECT pg_sleep(5)') as the first line inside the with_fast_read_statement_timeout block in Ci::CommitWithPipeline#last_pipeline, MergeRequest#merge_pipeline and Gitlab::CodeNavigationPath#latest_pipeline_for, then restart Rails.
  2. Open a commit page: it loads without the pipeline summary in the commit box.
  3. Open a merged merge request: cached_widget.json returns 200 with merge_pipeline: null.
  4. On a project with an LSIF artifact (create(:ci_job_artifact, :lsif, ...) in the console; projects without one skip the lookup), open a file: it loads without code navigation.
  5. log/exceptions_json.log has an ActiveRecord::QueryCanceled entry for each, with the project, commit sha or merge request id. Revert the temporary change.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Tiger Watson

Merge request reports