Skip to content

GitLab Next

  • Projects
  • Groups
  • Snippets
  • Help
    • Loading...
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in / Register
GitLab
GitLab
  • Project overview
    • Project overview
    • Details
    • Activity
    • Releases
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
    • Locked Files
  • Issues 34,916
    • Issues 34,916
    • List
    • Boards
    • Labels
    • Service Desk
    • Milestones
    • Iterations
  • Merge Requests 1,220
    • Merge Requests 1,220
  • Requirements
    • Requirements
    • List
  • CI / CD
    • CI / CD
    • Pipelines
    • Jobs
    • Schedules
    • Test Cases
  • Security & Compliance
    • Security & Compliance
    • Dependency List
    • License Compliance
  • Operations
    • Operations
    • Metrics
    • Incidents
    • Environments
  • Packages & Registries
    • Packages & Registries
    • Container Registry
  • Analytics
    • Analytics
    • CI / CD
    • Code Review
    • Insights
    • Issue
    • Repository
    • Value Stream
  • Snippets
    • Snippets
  • Members
    • Members
  • Collapse sidebar
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
  • GitLab.org
  • GitLabGitLab
  • Issues
  • #10286

Closed
Open
Opened Mar 08, 2019 by Ash McKenzie@ashmckenzie🌴Maintainer

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 Mar 08, 2019 by Ash McKenzie
Assignee
Assign to
12.6
Milestone
12.6 (Past due)
Assign milestone
Time tracking
None
Due date
None
Reference: gitlab-org/gitlab#10286