Skip to content

Add index to the pages_metadata to fix timeout in migration task

Vladimir Shushlin requested to merge 323175-database-timeout-in-pages-migration-task into master

What does this MR do?

Fixes #323175 (closed).

The problem is this query:

D, [2021-03-02T11:24:54.249832 #20163] DEBUG -- :   ProjectPagesMetadatum Load (15003.9ms)  /*application:console*/ SELECT "project_pages_metadata"."project_id" FROM "project_pages_metadata" WHERE "project_pages_metadata"."deployed" = TRUE AND "project_pages_metadata"."pages_deployment_id" IS NULL AND "project_pages_metadata"."project_id" >= 305893 ORDER BY "project_pages_metadata"."project_id" ASC LIMIT 1 OFFSET 1000
Traceback (most recent call last):
        8: from (irb):2
        7: from app/models/concerns/each_batch.rb:68:in `each_batch'
        6: from app/models/concerns/each_batch.rb:68:in `step'
        5: from app/models/concerns/each_batch.rb:80:in `block in each_batch'
        4: from ee/lib/gitlab/database/load_balancing/connection_proxy.rb:44:in `select_all'
        3: from ee/lib/gitlab/database/load_balancing/connection_proxy.rb:71:in `read_using_load_balancer'
        2: from ee/lib/gitlab/database/load_balancing/load_balancer.rb:34:in `read'
        1: from ee/lib/gitlab/database/load_balancing/connection_proxy.rb:72:in `block in read_using_load_balancer'
ActiveRecord::QueryCanceled (PG::QueryCanceled: ERROR:  canceling statement due to statement timeout)

Migration output

vlad @ gdk1 ➜  gitlab git:(323175-database-timeout-in-pages-migration-task) ✗  ./bin/rails db:migrate
== 20210302103851 AddDeployedDeploymentIdIndexToProjectPagesMetadata: migrating 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:project_pages_metadata, :project_id, {:where=>"deployed = TRUE AND pages_deployment_id is NULL", :name=>"index_on_pages_metadata_not_migrated", :algorithm=>:concurrently})
   -> 0.0088s
-- execute("SET statement_timeout TO 0")
   -> 0.0014s
-- add_index(:project_pages_metadata, :project_id, {:where=>"deployed = TRUE AND pages_deployment_id is NULL", :name=>"index_on_pages_metadata_not_migrated", :algorithm=>:concurrently})
   -> 0.0106s
-- execute("RESET ALL")
   -> 0.0020s
== 20210302103851 AddDeployedDeploymentIdIndexToProjectPagesMetadata: migrated (0.0255s) 

vlad @ gdk1 ➜  gitlab git:(323175-database-timeout-in-pages-migration-task) ✗  ./bin/rails db:rollback
== 20210302103851 AddDeployedDeploymentIdIndexToProjectPagesMetadata: reverting 
-- transaction_open?()
   -> 0.0000s
-- indexes(:project_pages_metadata)
   -> 0.0086s
-- execute("SET statement_timeout TO 0")
   -> 0.0013s
-- remove_index(:project_pages_metadata, {:algorithm=>:concurrently, :name=>"index_on_pages_metadata_not_migrated"})
   -> 0.0037s
-- execute("RESET ALL")
   -> 0.0014s
== 20210302103851 AddDeployedDeploymentIdIndexToProjectPagesMetadata: reverted (0.0172s)

Execution plans

Before


Time: 213.289 ms
  - planning: 0.148 ms
  - execution: 213.141 ms (estimated* for prod: 0.208 s)
    - I/O read: N/A
    - I/O write: N/A

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

 Limit  (cost=142.87..143.01 rows=1 width=8) (actual time=213.115..213.117 rows=0 loops=1)
   Buffers: shared hit=256442
   ->  Index Scan using index_project_pages_metadata_on_project_id_and_deployed_is_true on public.project_pages_metadata  (cost=0.42..38467.61 rows=270041 width=8) (actual time=0.052..213.098 rows=72 loops=1)
         Index Cond: (project_pages_metadata.project_id >= 305893)
         Filter: (project_pages_metadata.pages_deployment_id IS NULL)
         Rows Removed by Filter: 262376
         Buffers: shared hit=256442

After

Time: 1.257 ms
  - planning: 0.714 ms
  - execution: 0.543 ms
    - I/O read: 0.051 ms
    - I/O write: N/A

Shared buffers:
  - hits: 58 (~464.00 KiB) from the buffer pool
  - reads: 1 (~8.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

 Limit  (cost=30.56..30.59 rows=1 width=8) (actual time=0.523..0.524 rows=0 loops=1)
   Buffers: shared hit=58 read=1
   I/O Timings: read=0.051
   ->  Index Only Scan using index_on_pages_metadata_not_migrated on public.project_pages_metadata  (cost=0.14..8213.69 rows=270041 width=8) (actual time=0.139..0.517 rows=72 loops=1)
         Index Cond: (project_pages_metadata.project_id >= 305893)
         Heap Fetches: 38
         Buffers: shared hit=58 read=1
         I/O Timings: read=0.051

See https://gitlab.slack.com/archives/CLJMDRD8C/p1614684707218200 (internal) and the above

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

Related to #323175 (closed)

Edited by Vladimir Shushlin

Merge request reports