Skip to content

GitLab Next

Closed
Opened by Ash McKenzie@ashmckenzieMaintainer

Geo: SQL statement times out whilst determining Ci::JobArtifact records to sync

Problem

The following SQL query times out on the gitlab.com secondary DB when Geo::FileDownloadDispatchWorker.new.perform is executed from the Rails console:

SQL

SELECT "ci_job_artifacts"."id"
FROM "ci_job_artifacts"
INNER JOIN "projects" ON "projects"."id" = "ci_job_artifacts"."project_id"
WHERE "projects"."id" IN
    (SELECT "projects"."id"
     FROM "projects"
     WHERE "projects"."namespace_id" IN
         (WITH RECURSIVE "base_and_descendants" AS
            (SELECT "namespaces".*
             FROM "namespaces"
             INNER JOIN "geo_node_namespace_links" ON "namespaces"."id" = "geo_node_namespace_links"."namespace_id"
             WHERE "geo_node_namespace_links"."geo_node_id" = xxxx
             UNION SELECT "namespaces".*
             FROM "namespaces",
                  "base_and_descendants"
             WHERE "namespaces"."parent_id" = "base_and_descendants"."id") SELECT "id"
          FROM "base_and_descendants" AS "namespaces"))
  AND ("ci_job_artifacts"."file_store" = 1
       OR "ci_job_artifacts"."file_store" IS NULL)
  AND (expire_at IS NULL
       OR expire_at > '2019-03-08 00:43:17.331089')
LIMIT 1000

Output

[ xx ] production> Geo::FileDownloadDispatchWorker.new.perform
Traceback (most recent call last):
       16: from /opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/database/load_balancing/connection_proxy.rb:41:in `select_all'
       15: from /opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/database/load_balancing/connection_proxy.rb:62:in `read_using_load_balancer'
       14: from /opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/database/load_balancing/load_balancer.rb:32:in `read'
       13: from /opt/gitlab/embedded/service/gitlab-rails/ee/lib/gitlab/database/load_balancing/connection_proxy.rb:63:in `block in read_using_load_balancer'
       12: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/abstract/query_cache.rb:95:in `select_all'
       11: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/abstract/database_statements.rb:41:in `select_all'
       10: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/abstract/database_statements.rb:373:in `select'
        9: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:103:in `exec_query'
        8: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:587:in `execute_and_clear'
        7: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:600:in `exec_no_cache'
        6: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:583:in `log'
        5: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activesupport-5.0.7.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
        4: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/abstract_adapter.rb:590:in `block in log'
        3: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/activerecord-5.0.7.1/lib/active_record/connection_adapters/postgresql_adapter.rb:600:in `block in exec_no_cache'
        2: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/peek-pg-1.3.0/lib/peek/views/pg.rb:17:in `async_exec'
        1: from /opt/gitlab/embedded/lib/ruby/gems/2.5.0/gems/peek-pg-1.3.0/lib/peek/views/pg.rb:17:in `async_exec'
ActiveRecord::StatementInvalid (PG::QueryCanceled: ERROR:  canceling statement due to statement timeout)
: SELECT  "ci_job_artifacts"."id" FROM "ci_job_artifacts" INNER JOIN "projects" ON "projects"."id" = "ci_job_artifacts"."project_id" WHERE "projects"."id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS (SELECT "namespaces".* FROM "namespaces" INNER JOIN "geo_node_namespace_links" ON "namespaces"."id" = "geo_node_namespace_links"."namespace_id" WHERE "geo_node_namespace_links"."geo_node_id" = xxxx
UNION
SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."parent_id" = "base_and_descendants"."id") SELECT "id" FROM "base_and_descendants" AS "namespaces")) AND ("ci_job_artifacts"."file_store" = 1 OR "ci_job_artifacts"."file_store" IS NULL) AND (expire_at IS NULL OR expire_at > '2019-03-08 00:43:17.331089') LIMIT 1000
Edited by Ash McKenzie