Investigation: Reduce number of queries executed by *::CreatePipelineWorker
Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.
MergeRequests::CreatePipelineWorker and CreatePipelineWorker execute more than 100 SQL queries, the maximum number of queries allowed as per MR !154254 (merged). The number of queries should be reduced to less than 100 and the corresponding whitelisted sections of code (Gitlab::QueryLimiting.disable('...')) should be removed.
See https://gitlab.com/gitlab-org/gitlab/-/pipelines/1307856850 for failing pipeline
Reference: https://docs.gitlab.com/ee/development/database/query_count_limits.html
Proposal
In order to reduce the number of queries, we need to know what queries are being made. The deliverable from this issue is data on what queries are made during pipeline creation. Then use that data to create issues to reduce the number of queries and add them to &14102
Note: Get query data using complex pipeline configurations! For example, pipelines that make deployments will have more queries
Data from other issues
From #325998 (closed);
Click to expand
On POST /api/:version/projects/:id/merge_requests/:merge_request_iid/pipelines request, the request sometimes executes hundreds of SQL calls to get label names:
https://log.gprd.gitlab.net/goto/51b4e225175b1aa7471edbe9bbb86d5c
Triggered e.g. on POST /api/v4/projects/278964/merge_requests/55998/pipelines
Sample backtrace:
"sql": "SELECT \"labels\".\"title\" FROM \"labels\" INNER JOIN \"label_links\" ON \"labels\".\"id\" = \"label_links\".\"label_id\" WHERE \"label_links\".\"target_id\" = 93095897 AND \"label_links\".\"target_type\" = 'Merg
eRequest' ORDER BY \"labels\".\"title\" ASC, title ASC",
"backtrace": [
"ee/lib/ee/peek/views/active_record.rb:11:in `generate_detail'",
"lib/peek/views/active_record.rb:42:in `block in setup_subscribers'",
"app/models/concerns/issuable.rb:479:in `label_names'",
"app/models/merge_request.rb:1503:in `block in predefined_variables'",
"app/models/merge_request.rb:1492:in `tap'",
"app/models/merge_request.rb:1492:in `predefined_variables'",
"app/models/ci/pipeline.rb:801:in `block in predefined_variables'",
"app/models/ci/pipeline.rb:783:in `tap'",
"app/models/ci/pipeline.rb:783:in `predefined_variables'",
"app/models/concerns/ci/contextable.rb:16:in `block in scoped_variables'",
"app/models/concerns/ci/contextable.rb:13:in `tap'",
"app/models/concerns/ci/contextable.rb:13:in `scoped_variables'",
"lib/gitlab/ci/build/context/build.rb:24:in `block in variables'",
"lib/gitlab/utils/strong_memoize.rb:30:in `strong_memoize'",
"lib/gitlab/ci/build/context/build.rb:19:in `variables'",
"lib/gitlab/ci/build/rules/rule/clause/if.rb:13:in `satisfied_by?'",
"lib/gitlab/ci/build/rules/rule.rb:27:in `block in matches?'",
"lib/gitlab/ci/build/rules/rule.rb:27:in `all?'",
"lib/gitlab/ci/build/rules/rule.rb:27:in `matches?'",
"lib/gitlab/ci/build/rules.rb:46:in `block in match_rule'",
"lib/gitlab/ci/build/rules.rb:46:in `each'",
"lib/gitlab/ci/build/rules.rb:46:in `find'",
"lib/gitlab/ci/build/rules.rb:46:in `match_rule'",
"lib/gitlab/ci/build/rules.rb:31:in `evaluate'",
"lib/gitlab/ci/pipeline/seed/build.rb:190:in `block in rules_result'",
"lib/gitlab/utils/strong_memoize.rb:30:in `strong_memoize'",
"lib/gitlab/ci/pipeline/seed/build.rb:189:in `rules_result'",
"lib/gitlab/ci/pipeline/seed/build.rb:42:in `block in included?'",
"lib/gitlab/utils/strong_memoize.rb:30:in `strong_memoize'",
"lib/gitlab/ci/pipeline/seed/build.rb:40:in `included?'",
"lib/gitlab/ci/pipeline/seed/stage.rb:32:in `select'",
"lib/gitlab/ci/pipeline/seed/stage.rb:32:in `block in seeds'",
"lib/gitlab/utils/strong_memoize.rb:30:in `strong_memoize'",
"lib/gitlab/ci/pipeline/seed/stage.rb:31:in `seeds'",
"lib/gitlab/ci/pipeline/seed/stage.rb:49:in `included?'",
"lib/gitlab/ci/pipeline/seed/pipeline.rb:44:in `select'",
"lib/gitlab/ci/pipeline/seed/pipeline.rb:44:in `block in stage_seeds'",
"lib/gitlab/utils/strong_memoize.rb:30:in `strong_memoize'",
"lib/gitlab/ci/pipeline/seed/pipeline.rb:38:in `stage_seeds'",
"lib/gitlab/ci/pipeline/seed/pipeline.rb:16:in `errors'",
"lib/gitlab/ci/pipeline/chain/seed.rb:25:in `perform!'",
"lib/gitlab/ci/pipeline/chain/sequence.rb:19:in `block in build!'",
"lib/gitlab/ci/pipeline/chain/sequence.rb:16:in `each'",
"lib/gitlab/ci/pipeline/chain/sequence.rb:16:in `build!'",
"app/services/ci/create_pipeline_service.rb:83:in `execute'",
"ee/app/services/ee/merge_requests/create_pipeline_service.rb:28:in `create_merged_result_pipeline_for'",
"ee/app/services/ee/merge_requests/create_pipeline_service.rb:10:in `execute'",