Skip to content

Destroy merge request diffs in batches

Vasilii Iakliushin requested to merge 352511_delete_approval_rules_in_batches into master

What does this MR do and why?

Problem

Project delete causes a timeout error, because of DELETE FROM projects query.

Solution

projects table has multiple triggers on cascade delete. One of them took significant time to process. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11757/commands/41704

      {
        "Trigger Name": "RI_ConstraintTrigger_a_20354",
        "Constraint Name": "fk_06067f5644",
        "Relation": "merge_request_diffs",
        "Time": 24507.703,
        "Calls": 11344
      },

Extract merge_request_diffs deletion process into a separate statement.

Database

SELECT "merge_requests"."iid" FROM "merge_requests" WHERE "merge_requests"."target_project_id" = 33887545 ORDER BY "merge_requests"."iid" ASC LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11757/commands/41705

SELECT "merge_requests"."iid" FROM "merge_requests" WHERE "merge_requests"."target_project_id" = 33887545 AND "merge_requests"."iid" >= 1 ORDER BY "merge_requests"."iid" ASC LIMIT 1 OFFSET 100

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11757/commands/41706

DELETE FROM "merge_request_diffs" WHERE "merge_request_diffs"."id" IN (SELECT "merge_request_diffs"."id" FROM "merge_request_diffs" WHERE "merge_request_diffs"."merge_request_id" IN (SELECT "merge_requests"."id" FROM "merge_requests" WHERE "merge_requests"."target_project_id" = 33 AND "merge_requests"."iid" >= 1 AND "merge_requests"."iid" < 101) LIMIT 1000)

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11825/commands/41952

Screenshots or screen recordings

Before

Project Destroy (81.7ms)  DELETE FROM "projects" WHERE "projects"."id" = 33 /*application:console,db_config_name:main,line:/app/services/projects/destroy_service.rb:146:in `destroy_project_related_records'*/

After

MergeRequest Load (0.1ms)  SELECT "merge_requests"."iid" FROM "merge_requests" WHERE "merge_requests"."target_project_id" = 33 ORDER BY "merge_requests"."iid" ASC LIMIT 1 /*application:console,db_config_name:main,line:/app/models/concerns/each_batch.rb:62:in `each_batch'*/
MergeRequest Load (0.1ms)  SELECT "merge_requests"."iid" FROM "merge_requests" WHERE "merge_requests"."target_project_id" = 33 AND "merge_requests"."iid" >= 1 ORDER BY "merge_requests"."iid" ASC LIMIT 1 OFFSET 100 /*application:console,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
MergeRequestDiff Destroy (11.0ms)  DELETE FROM "merge_request_diffs" WHERE "merge_request_diffs"."id" IN (SELECT "merge_request_diffs"."id" FROM "merge_request_diffs" WHERE "merge_request_diffs"."merge_request_id" IN (SELECT "merge_requests"."id" FROM "merge_requests" WHERE "merge_requests"."target_project_id" = 33 AND "merge_requests"."iid" >= 1 AND "merge_requests"."iid" < 101) LIMIT 1000) /*application:console,db_config_name:main,line:/app/services/projects/destroy_service.rb:195:in `block (2 levels) in destroy_merge_request_diffs!'*/
MergeRequestDiff Destroy (0.4ms)  DELETE FROM "merge_request_diffs" WHERE "merge_request_diffs"."id" IN (SELECT "merge_request_diffs"."id" FROM "merge_request_diffs" WHERE "merge_request_diffs"."merge_request_id" IN (SELECT "merge_requests"."id" FROM "merge_requests" WHERE "merge_requests"."target_project_id" = 33 AND "merge_requests"."iid" >= 1 AND "merge_requests"."iid" < 101) LIMIT 1000) /*application:console,db_config_name:main,line:/app/services/projects/destroy_service.rb:195:in `block (2 levels) in destroy_merge_request_diffs!'*/
MergeRequest Load (0.2ms)  SELECT "merge_requests"."iid" FROM "merge_requests" WHERE "merge_requests"."target_project_id" = 33 AND "merge_requests"."iid" >= 101 ORDER BY "merge_requests"."iid" ASC LIMIT 1 OFFSET 100 /*application:console,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
MergeRequestDiff Destroy (7.8ms)  DELETE FROM "merge_request_diffs" WHERE "merge_request_diffs"."id" IN (SELECT "merge_request_diffs"."id" FROM "merge_request_diffs" WHERE "merge_request_diffs"."merge_request_id" IN (SELECT "merge_requests"."id" FROM "merge_requests" WHERE "merge_requests"."target_project_id" = 33 AND "merge_requests"."iid" >= 101) LIMIT 1000) /*application:console,db_config_name:main,line:/app/services/projects/destroy_service.rb:195:in `block (2 levels) in destroy_merge_request_diffs!'*/
MergeRequestDiff Destroy (0.5ms)  DELETE FROM "merge_request_diffs" WHERE "merge_request_diffs"."id" IN (SELECT "merge_request_diffs"."id" FROM "merge_request_diffs" WHERE "merge_request_diffs"."merge_request_id" IN (SELECT "merge_requests"."id" FROM "merge_requests" WHERE "merge_requests"."target_project_id" = 33 AND "merge_requests"."iid" >= 101) LIMIT 1000) /*application:console,db_config_name:main,line:/app/services/projects/destroy_service.rb:195:in `block (2 levels) in destroy_merge_request_diffs!'*/
...
Project Destroy (52.0ms)  DELETE FROM "projects" WHERE "projects"."id" = 33 /*application:console,db_config_name:main,line:/app/services/projects/destroy_service.rb:146:in `destroy_project_related_records'*/

How to set up and validate locally

Feature.enable(:extract_mr_diff_deletions)

user = User.first
project = FactoryBot.create(:project, :repository, namespace: user.namespace)

# create 200 merge requests
200.times { |i| FactoryBot.create(:merge_request, source_project: project, target_branch: "test-#{i}") }

# show SQL queries in console
ActiveRecord::Base.logger = Logger.new(STDOUT)

# execute project deletion process and roll it back (to avoid constant project recreation)`
ActiveRecord::Base.transaction { Projects::DestroyService.new(project, user, {}).execute; raise ActiveRecord::Rollback; }

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Vasilii Iakliushin

Merge request reports