Skip to content

Resolve "Timeout when trying to view CICD settings"

Bala Kumar requested to merge fix-query-timeout-in-cicd-settings into master

What does this MR do and why?

Add Index on releases table for the project_id, released_at, id columns so that we avoid the timeout caused when running query

SELECT "releases".* FROM "releases" WHERE "releases"."project_id" = 28054722 AND "releases"."tag" IS NOT NULL ORDER BY "releases"."released_at" DESC LIMIT 1

on the ci/cd settings page when fetching release badge.

See: #353371 (closed) & https://sentry.gitlab.net/gitlab/gitlabcom/issues/3200659

Also we are including additional column id to the index so that it is useful when we merge !80093 (merged) which improves ReleasesFinder performance in the group/subgroups case by using InOperatorOptimization. Related discussion: !82506 (comment 871350819)

Note: Null tag removal suggested in #353371 (comment 862382569) is tracked in separate issue #355459. Also the new query should still use this same index as verified in https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9156/commands/32460

New query expected to use this full index is in development - !80093 (merged)

And query plan and details from it are available in https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9064/commands/32073 (same index named as index_releases_on_project_released_at_id)

The sub-query from that plan which is expected to use this full index looks like

SELECT "releases"."released_at" AS released_at, "releases"."id" AS id FROM "releases" WHERE "releases"."project_id" = recursive_keyset_cte.array_cte_id_array[position] AND "releases"."released_at" = recursive_keyset_cte.releases_released_at_array[position] AND "releases"."id" > recursive_keyset_cte.releases_id_array[position] ORDER BY "releases"."released_at" DESC, "releases"."id" ASC)

Screenshots or screen recordings

Before Index

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9122/commands/32247 - 4.368 min

EXPLAIN SELECT "releases".* FROM "releases" WHERE "releases"."project_id" = 28054722 AND "releases"."tag" IS NOT NULL ORDER BY "releases"."released_at" DESC LIMIT 1

Execution plan 
Limit  (cost=0.43..251.65 rows=1 width=760) (actual time=262103.010..262103.016 rows=1 loops=1)
   Buffers: shared hit=691763 read=205064 dirtied=7467
   I/O Timings: read=257362.100 write=0.000
   ->  Index Scan using index_releases_on_released_at on public.releases  (cost=0.43..919960.87 rows=3662 width=760) (actual time=262103.006..262103.007 rows=1 loops=1)
         Filter: ((releases.tag IS NOT NULL) AND (releases.project_id = 28054722))
         Rows Removed by Filter: 900690
         Buffers: shared hit=691763 read=205064 dirtied=7467
         I/O Timings: read=257362.100 write=0.000
Statistics 
Time: 4.368 min
  - planning: 2.062 ms
  - execution: 4.368 min
    - I/O read: 4.289 min
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 691763 (~5.30 GiB) from the buffer pool
  - reads: 205064 (~1.60 GiB) from the OS file cache, including disk I/O
  - dirtied: 7467 (~58.30 MiB)
  - writes: 0

After Index

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9122/commands/32340 - 2.6 ms

EXPLAIN SELECT "releases".* FROM "releases" WHERE "releases"."project_id" = 28054722 AND "releases"."tag" IS NOT NULL ORDER BY "releases"."released_at" DESC LIMIT 1

Execution plan 
Limit  (cost=0.43..1.78 rows=1 width=760) (actual time=0.297..0.298 rows=1 loops=1)
   Buffers: shared read=4
   I/O Timings: read=0.233 write=0.000
   ->  Index Scan using index_releases_on_project_id_and_released_at_and_id on public.releases  (cost=0.43..4775.80 rows=3535 width=760) (actual time=0.294..0.295 rows=1 loops=1)
         Index Cond: (releases.project_id = 28054722)
         Filter: (releases.tag IS NOT NULL)
         Rows Removed by Filter: 0
         Buffers: shared read=4
         I/O Timings: read=0.233 write=0.000
Statistics 
Time: 2.600 ms  
  - planning: 2.254 ms  
  - execution: 0.346 ms  
    - I/O read: 0.233 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 0 from the buffer pool  
  - reads: 4 (~32.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

How to set up and validate locally

rails db:migrate:up VERSION=20220310134207 
== 20220310134207 AddIndexProjectIdAndReleasedAtAndIdOnReleases: migrating ====
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:releases, [:project_id, :released_at, :id], {:name=>"index_releases_on_project_id_and_released_at_and_id", :algorithm=>:concurrently})
   -> 0.0032s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:releases, [:project_id, :released_at, :id], {:name=>"index_releases_on_project_id_and_released_at_and_id", :algorithm=>:concurrently})
   -> 0.0048s
-- execute("RESET statement_timeout")
   -> 0.0006s
== 20220310134207 AddIndexProjectIdAndReleasedAtAndIdOnReleases: migrated (0.0155s)
rails db:migrate:down VERSION=20220310134207 
== 20220310134207 AddIndexProjectIdAndReleasedAtAndIdOnReleases: reverting ====
-- transaction_open?()
   -> 0.0000s
-- indexes(:releases)
   -> 0.0046s
-- execute("SET statement_timeout TO 0")
   -> 0.0009s
-- remove_index(:releases, {:algorithm=>:concurrently, :name=>"index_releases_on_project_id_and_released_at_and_id"})
   -> 0.0035s
-- execute("RESET statement_timeout")
   -> 0.0009s
== 20220310134207 AddIndexProjectIdAndReleasedAtAndIdOnReleases: reverted (0.0187s)

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #353371 (closed)

Edited by Bala Kumar

Merge request reports