1000+ SQL queries in PipelinesController#index.json

From https://gitlab.com/gitlab-org/gitlab/pipelines:

Attached is the performance bar data:

pipelines_perf_bar_1576475008083.json.zip

The breakdown seems to be:

# cat /tmp/pipelines_perf_bar_1576475008083.json| jq -C | grep sql | sort -nr | uniq -c | sort -nr | less -R
 260             "sql": "SELECT  \"protected_environments\".* FROM \"protected_environments\" WHERE \"protected_environments\".\"project_id\" = 278964 AND \"protected_environments\".\"name\" IS NULL LIMIT 1",
 208             "sql": "SELECT  \"projects\".* FROM \"projects\" WHERE \"projects\".\"id\" = 278964 LIMIT 1",
 204             "sql": "SELECT  \"routes\".* FROM \"routes\" WHERE \"routes\".\"source_id\" = 9970 AND \"routes\".\"source_type\" = 'Namespace' LIMIT 1",
 203             "sql": "SELECT  \"namespaces\".* FROM \"namespaces\" WHERE \"namespaces\".\"id\" = 9970 LIMIT 1",
  78             "sql": "SELECT  \"routes\".* FROM \"routes\" WHERE \"routes\".\"source_id\" = 278964 AND \"routes\".\"source_type\" = 'Project' LIMIT 1",
  38             "sql": "SELECT  \"environments\".* FROM \"environments\" WHERE \"environments\".\"name\" = 'review/auto-cleanup' AND \"environments\".\"project_id\" = 278964 LIMIT 1",
  26               "app/models/ci/pipeline.rb:372:in `legacy_stages_using_sql'",
  16             "sql": "SELECT  \"ci_pipelines\".* FROM \"ci_pipelines\" WHERE \"ci_pipelines\".\"id\" = 103266983 LIMIT 1",
  10             "sql": "SELECT \"protected_branches\".* FROM \"protected_branches\" WHERE \"protected_branches\".\"project_id\" = 278964",
  10             "sql": "SELECT  \"ci_pipelines\".* FROM \"ci_pipelines\" WHERE \"ci_pipelines\".\"id\" = 103268758 LIMIT 1",
   8             "sql": "SELECT \"ci_variables\".* FROM \"ci_variables\" WHERE \"ci_variables\".\"project_id\" = 278964 AND \"ci_variables\".\"protected\" = FALSE AND \"ci_variables\".\"environment_scope\" = '*'",
   8             "sql": "SELECT \"ci_group_variables\".* FROM \"ci_group_variables\" WHERE \"ci_group_variables\".\"group_id\" = 9970 AND \"ci_group_variables\".\"protected\" = FALSE",
   8             "sql": "SELECT  \"users\".* FROM \"users\" WHERE \"users\".\"id\" = 3945330 LIMIT 1",
   8             "sql": "SELECT  \"users\".* FROM \"users\" WHERE \"users\".\"id\" = 3397881 LIMIT 1",
   8             "sql": "SELECT  \"users\".* FROM \"users\" WHERE \"users\".\"id\" = 2880930 LIMIT 1",
   6             "sql": "SELECT \"projects\".* FROM \"projects\" WHERE \"projects\".\"id\" = 278964",
   6             "sql": "SELECT  \"users\".* FROM \"users\" WHERE \"users\".\"id\" = 4092171 LIMIT 1",
   5             "sql": "SELECT  \"saml_providers\".* FROM \"saml_providers\" WHERE \"saml_providers\".\"group_id\" = 9970 LIMIT 1",
   5             "sql": "SELECT  \"project_features\".* FROM \"project_features\" WHERE \"project_features\".\"project_id\" = 278964 LIMIT 1",
   5             "sql": "SELECT  \"namespaces\".* FROM \"namespaces\" WHERE \"namespaces\".\"type\" IN ('Group') AND \"namespaces\".\"id\" = 9970 AND \"namespaces\".\"type\" = 'Group' LIMIT 1",
   4             "sql": "SELECT \"routes\".* FROM \"routes\" WHERE \"routes\".\"source_type\" = 'Namespace' AND \"routes\".\"source_id\" = 9970",
   4             "sql": "SELECT \"namespaces\".* FROM \"namespaces\" WHERE \"namespaces\".\"id\" = 9970",

/cc: @matteeyah

It's not clear to me whether this is a new N+1 issue. These queries seem to be coming from retryable?:

            "sql": "SELECT  \"protected_environments\".* FROM \"protected_environments\" WHERE \"protected_environments\".\"project_id\" = 278964 AND \"protected_environments\".\"name\" IS NULL LIMIT 1",
           "backtrace": [
              "ee/app/models/ee/project.rb:589:in `protected_environment_by_name'",
              "ee/app/models/ee/project.rb:581:in `protected_environment_accessible_to?'",
              "ee/app/policies/ee/ci/build_policy.rb:46:in `deployable_by_user?'",
              "ee/app/policies/ee/ci/build_policy.rb:8:in `block (2 levels) in <module:BuildPolicy>'",
              "lib/declarative_policy/condition.rb:23:in `instance_eval'",
              "lib/declarative_policy/condition.rb:23:in `compute'",
              "lib/declarative_policy/condition.rb:44:in `block in pass?'",
              "lib/declarative_policy/base.rb:282:in `cache'",
              "lib/declarative_policy/condition.rb:44:in `pass?'",
              "lib/declarative_policy/rule.rb:81:in `pass?'",
              "lib/declarative_policy/rule.rb:292:in `pass?'",
              "lib/declarative_policy/step.rb:81:in `pass?'",
              "lib/declarative_policy/runner.rb:100:in `block in run'",
              "lib/declarative_policy/runner.rb:179:in `block in steps_by_score'",
              "lib/declarative_policy/runner.rb:148:in `loop'",
              "lib/declarative_policy/runner.rb:148:in `steps_by_score'",
              "lib/declarative_policy/runner.rb:81:in `run'",
              "lib/declarative_policy/runner.rb:59:in `pass?'",
              "lib/declarative_policy/base.rb:217:in `block in allowed?'",
              "lib/declarative_policy/base.rb:217:in `all?'",
              "lib/declarative_policy/base.rb:217:in `allowed?'",
              "lib/declarative_policy/base.rb:209:in `can?'",
              "app/models/ability.rb:72:in `allowed?'",
              "lib/gitlab/allowable.rb:6:in `can?'",
              "app/serializers/job_entity.rb:54:in `retryable?'",
              "app/serializers/job_entity.rb:16:in `block in <class:JobEntity>'",
              "app/serializers/base_serializer.rb:16:in `represent'",
              "app/serializers/concerns/with_pagination.rb:21:in `represent'",
              "app/serializers/pipeline_serializer.rb:21:in `represent'",
              "app/controllers/projects/pipelines_controller.rb:182:in `serialize_pipelines'",
              "app/controllers/projects/pipelines_controller.rb:42:in `block (2 levels) in index'",
              "app/controllers/projects/pipelines_controller.rb:36:in `index'",
              "app/controllers/application_controller.rb:545:in `block in allow_gitaly_ref_name_caching'",
              "lib/gitlab/gitaly_client.rb:314:in `allow_ref_name_caching'",
              "app/controllers/application_controller.rb:544:in `allow_gitaly_ref_name_caching'",
              "ee/lib/gitlab/ip_address_state.rb:10:in `with'",
              "ee/app/controllers/ee/application_controller.rb:43:in `set_current_ip_address'",
              "lib/gitlab/session.rb:11:in `with_session'",
              "app/controllers/application_controller.rb:462:in `set_session_storage'",
              "app/controllers/application_controller.rb:456:in `set_locale'",
              "ee/lib/omni_auth/strategies/group_saml.rb:41:in `other_phase'",
              "ee/lib/gitlab/jira/middleware.rb:19:in `call'"
            ]

            "sql": "SELECT  \"projects\".* FROM \"projects\" WHERE \"projects\".\"id\" = 278964 LIMIT 1",
            "backtrace": [
              "app/policies/commit_status_policy.rb:4:in `block in <class:CommitStatusPolicy>'",
              "lib/declarative_policy/base.rb:312:in `instance_eval'",
              "lib/declarative_policy/base.rb:312:in `block in delegated_policies'",
              "lib/declarative_policy/base.rb:311:in `transform_values'",
              "lib/declarative_policy/base.rb:311:in `delegated_policies'",
              "lib/declarative_policy/base.rb:268:in `runner'",
              "lib/declarative_policy/base.rb:217:in `block in allowed?'",
              "lib/declarative_policy/base.rb:217:in `all?'",
              "lib/declarative_policy/base.rb:217:in `allowed?'",
              "lib/declarative_policy/base.rb:209:in `can?'",
              "app/models/ability.rb:72:in `allowed?'",
              "lib/gitlab/allowable.rb:6:in `can?'",
              "app/serializers/job_entity.rb:54:in `retryable?'",
              "app/serializers/job_entity.rb:16:in `block in <class:JobEntity>'",
              "app/serializers/base_serializer.rb:16:in `represent'",
              "app/serializers/concerns/with_pagination.rb:21:in `represent'",
              "app/serializers/pipeline_serializer.rb:21:in `represent'",
              "app/controllers/projects/pipelines_controller.rb:182:in `serialize_pipelines'",
              "app/controllers/projects/pipelines_controller.rb:42:in `block (2 levels) in index'",
              "app/controllers/projects/pipelines_controller.rb:36:in `index'",
              "app/controllers/application_controller.rb:545:in `block in allow_gitaly_ref_name_caching'",
              "lib/gitlab/gitaly_client.rb:314:in `allow_ref_name_caching'",
              "app/controllers/application_controller.rb:544:in `allow_gitaly_ref_name_caching'",
              "ee/lib/gitlab/ip_address_state.rb:10:in `with'",
              "ee/app/controllers/ee/application_controller.rb:43:in `set_current_ip_address'",
              "lib/gitlab/session.rb:11:in `with_session'",
              "app/controllers/application_controller.rb:462:in `set_session_storage'",
              "app/controllers/application_controller.rb:456:in `set_locale'",
              "ee/lib/omni_auth/strategies/group_saml.rb:41:in `other_phase'",

@stanhu This is about the PipelinesController#index, right? https://gitlab.com/gitlab-org/gitlab/pipelines routes to that endpoint.

I'm getting different query counts for that same URL (https://gitlab.com/gitlab-org/gitlab/pipelines)

Yes, looks like #index. Updated the title.

I think there is a regression caused by 05f03b39 (comment 263908277) (!20615 (merged)).

The good news is - I double checked the PipelinesController#show endpoint (https://gitlab.com/gitlab-org/gitlab/pipelines/103373004) and we successfully halved the amount of queries used there.

changed title from 1000+ SQL queries in PipelinesController#show.json to 1000+ SQL queries in PipelinesController#index.json

added Category:Continuous Integration bugperformance devopsverify grouppipeline execution pipeline typebug labels

mentioned in issue #119030 (closed)

added regression:12.6 label

mentioned in commit ba52038d

mentioned in merge request !22189 (merged)

mentioned in commit 3491a674

mentioned in commit 3db71622

pipelines_104885244_perf_bar_1576996151956.json is the latest data for loading https://gitlab.com/gitlab-org/gitlab/pipelines/104885244:

 500             "sql": "SELECT  \"protected_environments\".* FROM \"protected_environments\" WHERE \"protected_environments\".\"project_id\" = 278964 AND \"protected_environments\".\"name\" IS NULL LIMIT 1",
 424             "sql": "SELECT  \"routes\".* FROM \"routes\" WHERE \"routes\".\"source_id\" = 9970 AND \"routes\".\"source_type\" = 'Namespace' LIMIT 1",
 413             "sql": "SELECT  \"namespaces\".* FROM \"namespaces\" WHERE \"namespaces\".\"id\" = 9970 LIMIT 1",
 408             "sql": "SELECT  \"projects\".* FROM \"projects\" WHERE \"projects\".\"id\" = 278964 LIMIT 1",
 253             "sql": "SELECT  \"users\".* FROM \"users\" WHERE \"users\".\"id\" = 1786152 LIMIT 1",
 158             "sql": "SELECT \"tags\".* FROM \"tags\" INNER JOIN \"taggings\" ON \"tags\".\"id\" = \"taggings\".\"tag_id\" WHERE \"taggings\".\"taggable_id\" = 380989 AND \"taggings\".\"taggable_type\" = 'Ci::Runner' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL)",
 158             "sql": "SELECT \"taggings\".* FROM \"taggings\" WHERE \"taggings\".\"taggable_id\" = 380989 AND \"taggings\".\"taggable_type\" = 'Ci::Runner'",
 158             "sql": "SELECT \"ci_runners\".* FROM ((SELECT \"ci_runners\".* FROM \"ci_runners\" INNER JOIN \"ci_runner_projects\" ON \"ci_runners\".\"id\" = \"ci_runner_projects\".\"runner_id\" WHERE \"ci_runner_projects\".\"project_id\" = 278964)\nUNION\n(SELECT \"ci_runners\".* FROM \"ci_runners\" INNER JOIN \"ci_runner_namespaces\" ON \"ci_runner_namespaces\".\"runner_id\" = \"ci_runners\".\"id\" INNER JOIN \"namespaces\" ON \"namespaces\".\"id\" = \"ci_runner_namespaces\".\"namespace_id\" AND \"namespaces\".\"type\" IN ('Group') WHERE \"namespaces\".\"id\" IN (WITH RECURSIVE \"base_and_ancestors\" AS ((SELECT \"namespaces\".* FROM \"namespaces\" INNER JOIN \"projects\" ON \"projects\".\"namespace_id\" = \"namespaces\".\"id\" WHERE \"namespaces\".\"type\" IN ('Group') AND \"projects\".\"id\" = 278964)\nUNION\n(SELECT \"namespaces\".* FROM \"namespaces\", \"base_and_ancestors\" WHERE \"namespaces\".\"type\" IN ('Group') AND \"namespaces\".\"id\" = \"base_and_ancestors\".\"parent_id\")) SELECT \"namespaces\".\"id\" FROM \"base_and_ancestors\" AS \"namespaces\"))\nUNION\n(SELECT \"ci_runners\".* FROM \"ci_runners\" WHERE \"ci_runners\".\"runner_type\" = 1)) ci_runners WHERE \"ci_runners\".\"active\" = TRUE",
 158             "sql": "SELECT  \"project_ci_cd_settings\".* FROM \"project_ci_cd_settings\" WHERE \"project_ci_cd_settings\".\"project_id\" = 278964

added regression label

mentioned in issue #121492 (closed)

changed milestone to %12.7

assigned to @stanhu

mentioned in commit ea971ad6

closed via merge request !22189 (merged)

mentioned in commit 7b2927dd

mentioned in commit 974d2968

mentioned in issue #37438 (closed)

added 1 deleted label

mentioned in commit 7816adee

mentioned in merge request !29945 (merged)

marked this issue as related to #268237