Optimize batching for NullifyOrphanRunnerIdOnCiBuilds
requested to merge 30159-optimize-batching-for-nullify-orphan-runner-id-on-ci-builds-migration into master
What does this MR do and why?
This MR optimizes the batching for the NullifyOrphanRunnerIdOnCiBuilds
migration.
This migration could not complete because of timeout errors: #30159 (comment 956410367)
This MR changes the batching to optimize the query.
Related to #30159 (closed)
Screenshots or screen recordings
SQL output of a test case with NullifyOrphanRunnerIdOnCiBuilds#perform(1, 10, :ci_builds, :id, 3, 0)
Before
SELECT "ci_builds"."id"
FROM "ci_builds"
LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
WHERE (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
AND "ci_builds"."id" BETWEEN 1 AND 10
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC LIMIT 1;
UPDATE "ci_builds" SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
SELECT "ci_builds"."id"
FROM "ci_builds"
LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
WHERE (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
AND "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 1
AND "ci_builds"."id" < 6
);
UPDATE "ci_builds" SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
SELECT "ci_builds"."id"
FROM "ci_builds"
LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
WHERE (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
AND "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 6
AND "ci_builds"."id" < 10
);
UPDATE "ci_builds" SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
SELECT "ci_builds"."id"
FROM "ci_builds"
LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
WHERE (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
AND "ci_builds"."id" BETWEEN 1 AND 10
AND "ci_builds"."id" >= 10
);
After
SELECT "ci_builds"."id"
FROM "ci_builds"
WHERE "ci_builds"."id" BETWEEN 1 AND 10
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC
LIMIT 1;
SELECT "ci_builds"."id"
FROM "ci_builds"
WHERE "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 1
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC
LIMIT 1 OFFSET 3;
UPDATE "ci_builds"
SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
SELECT "ci_builds"."id"
FROM "ci_builds"
LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
WHERE "ci_builds"."id" BETWEEN 1 AND 10
AND "ci_builds"."id" >= 1
AND "ci_builds"."id" < 4
AND (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
);
SELECT "ci_builds"."id"
FROM "ci_builds"
WHERE "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 4
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC
LIMIT 1 OFFSET 3;
UPDATE "ci_builds"
SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
SELECT "ci_builds"."id"
FROM "ci_builds"
LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
WHERE "ci_builds"."id" BETWEEN 1 AND 10
AND "ci_builds"."id" >= 4
AND "ci_builds"."id" < 7
AND (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
);
SELECT "ci_builds"."id"
FROM "ci_builds"
WHERE "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 7
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC
LIMIT 1 OFFSET 3;
UPDATE "ci_builds"
SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
SELECT "ci_builds"."id"
FROM "ci_builds"
LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
WHERE "ci_builds"."id" BETWEEN 1 AND 10
AND "ci_builds"."id" >= 7
AND "ci_builds"."id" < 10
AND (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
);
SELECT "ci_builds"."id"
FROM "ci_builds"
WHERE "ci_builds"."id" BETWEEN 1 AND 10 AND "ci_builds"."id" >= 10
ORDER BY "ci_builds"."id" ASC, "ci_builds"."type" ASC
LIMIT 1 OFFSET 3;
UPDATE "ci_builds"
SET "runner_id" = NULL, "lock_version" = COALESCE("lock_version", 0) + 1
WHERE "ci_builds"."id" IN (
SELECT "ci_builds"."id"
FROM "ci_builds"
LEFT OUTER JOIN ci_runners ON ci_runners.id = ci_builds.runner_id
WHERE "ci_builds"."id" BETWEEN 1 AND 10
AND "ci_builds"."id" >= 10
AND (ci_builds.runner_id IS NOT NULL AND ci_runners.id IS NULL)
);
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Furkan Ayhan