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