Resolve deployments.fast_destroy_all N+1 (!141295) · Merge requests · GitLab.org / GitLab

Hunter Stewart requested to merge hustewart-preload-envs-fast-destroy into master Jan 08, 2024

Why

Lack of preloading environments in the Deployment FastDestroyAll implementation causes N+1 on SELECT on environments.

See Preload environments to resolve N+1 (#437674 - closed) issue for more.

What

Add spec to verify N+1
update implementation to resolve N+1

Spec details

Here's the failure output of the new spec before it was resolved by adding the preload:

Failures:

  1) Deployment.fast_destroy_all does not trigger N+1 queries
     Failure/Error: expect { project.deployments.fast_destroy_all }.not_to exceed_query_limit(control)

       Expected a maximum of 3 queries, got 4:

       Query Diff:
       -----------
       SELECT "environments".* FROM "environments"...
       -- (expected: 1, got: 2)
          WHERE "environments"."id" = 4 LIMIT 1

Finished in 5.22 seconds (files took 9.82 seconds to load)
1 example, 1 failure

Failed examples:

rspec ./spec/models/deployment_spec.rb:1541 # Deployment.fast_destroy_all does not trigger N+1 queries

SQL

Before

I didn't include all the N+1 SELECT on environments because there were a lot, so this is truncated.

[2] pry(main)> Deployment.preload(:project).find_each.map do |deployment|
  [deployment.project, deployment.ref_path]
end
  Deployment Load (1.6ms)  SELECT "deployments"."id", "deployments"."iid", "deployments"."project_id", "deployments"."environment_id", "deployments"."ref", "deployments"."tag", "deployments"."sha", "deployments"."user_id", "deployments"."deployable_type", "deployments"."created_at", "deployments"."updated_at", "deployments"."on_stop", "deployments"."status", "deployments"."finished_at", "deployments"."deployable_id", "deployments"."archived" FROM "deployments" ORDER BY "deployments"."id" ASC LIMIT 1000 /*application:console,db_config_name:main,line:(pry):4:in `each'*/
  Project Load (0.9ms)  SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."avatar", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."pending_delete", "projects"."public_builds", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."repository_read_only", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."ci_config_path", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."service_desk_enabled", "projects"."cached_markdown_version", "projects"."delete_error", "projects"."last_repository_updated_at", "projects"."disable_overriding_approvers_per_merge_request", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."pages_https_only", "projects"."external_webhook_token", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."autoclose_referenced_issues", "projects"."suggestion_commit_message", "projects"."project_namespace_id", "projects"."hidden", "projects"."organization_id" FROM "projects" WHERE "projects"."id" IN (7, 1, 5, 6, 8, 19, 29, 239, 341, 544, 996) /*application:console,db_config_name:main,line:(pry):4:in `each'*/
  Environment Load (0.3ms)  SELECT "environments".* FROM "environments" WHERE "environments"."id" = 8 LIMIT 1 /*application:console,db_config_name:main,line:/app/models/deployment.rb:432:in `ref_path'*/
  Environment Load (0.2ms)  SELECT "environments".* FROM "environments" WHERE "environments"."id" = 8 LIMIT 1 /*application:console,db_config_name:main,line:/app/models/deployment.rb:432:in `ref_path'*/
  Environment Load (0.2ms)  SELECT "environments".* FROM "environments" WHERE "environments"."id" = 8 LIMIT 1 /*application:console,db_config_name:main,line:/app/models/deployment.rb:432:in `ref_path'*/
  Environment Load (0.1ms)  SELECT "environments".* FROM "environments" WHERE "environments"."id" = 8 LIMIT 1 /*application:console,db_config_name:main,line:/app/models/deployment.rb:432:in `ref_path'*/
  Environment Load (0.1ms)  SELECT "environments".* FROM "environments" WHERE "environments"."id" = 7 LIMIT 1 /*application:console,db_config_name:main,line:/app/models/deployment.rb:432:in `ref_path'*/
  Environment Load (0.1ms)  SELECT "environments".* FROM "environments" WHERE "environments"."id" = 7 LIMIT 1 /*application:console,db_config_name:main,line:/app/models/deployment.rb:432:in `ref_path'*/
  Environment Load (0.1ms)  SELECT "environments".* FROM "environments" WHERE "environments"."id" = 7 LIMIT 1 /*application:console,db_config_name:main,line:/app/models/deployment.rb:432:in `ref_path'*/

After

The SQL generated with the new preloaded environments looks like this:

[1] pry(main)> Deployment.preload(:project, :environment).find_each.map do |deployment|
  [deployment.project, deployment.ref_path]
end
  Deployment Load (3.3ms)  SELECT "deployments"."id", "deployments"."iid", "deployments"."project_id", "deployments"."environment_id", "deployments"."ref", "deployments"."tag", "deployments"."sha", "deployments"."user_id", "deployments"."deployable_type", "deployments"."created_at", "deployments"."updated_at", "deployments"."on_stop", "deployments"."status", "deployments"."finished_at", "deployments"."deployable_id", "deployments"."archived" FROM "deployments" ORDER BY "deployments"."id" ASC LIMIT 1000 /*application:console,db_config_name:main,console_hostname:,line:(pry):1:in `each'*/
  Project Load (5.2ms)  SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."avatar", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."pending_delete", "projects"."public_builds", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."repository_read_only", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."ci_config_path", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."service_desk_enabled", "projects"."cached_markdown_version", "projects"."delete_error", "projects"."last_repository_updated_at", "projects"."disable_overriding_approvers_per_merge_request", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."pages_https_only", "projects"."external_webhook_token", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."autoclose_referenced_issues", "projects"."suggestion_commit_message", "projects"."project_namespace_id", "projects"."hidden", "projects"."organization_id" FROM "projects" WHERE "projects"."id" IN (7, 1, 5, 6, 8, 19, 29, 239, 341, 544, 996) /*application:console,db_config_name:main,line:(pry):1:in `each'*/
  Environment Load (3.2ms)  SELECT "environments".* FROM "environments" WHERE "environments"."id" IN (8, 7, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 2, 1, 10, 9, 21, 22, 23, 24, 25, 26, 27, 28, 30, 231, 332, 333, 334, 335, 336, 337, 338, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 354, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372, 373, 374, 375, 376, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 421, 422, 423, 424, 425, 426, 427, 428, 429, 532, 533, 534, 535, 536, 537, 538, 539, 540, 541, 542, 543, 544, 545, 546, 547, 548, 549, 550, 551, 552, 553, 554, 555, 556, 557, 558, 559, 560, 561, 562, 563, 564, 565, 566, 567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581, 582, 583, 584, 585, 586, 587, 588, 589, 590, 591, 592, 593, 594, 595, 596, 597, 598, 599, 600, 601, 602, 603, 604, 605, 606, 607, 608, 609, 610, 611, 612, 613, 614, 615, 616, 617, 618, 619, 620, 621, 622, 623, 624, 625, 626, 627, 628, 629, 630, 631, 977, 978, 979) /*application:console,db_config_name:main,line:(pry):1:in `each'*/

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before	After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

Edited Jan 09, 2024 by Hunter Stewart

Resolve deployments.fast_destroy_all N+1

Why