Clean up stale runner managers in every partition

What does this MR do and why?

The stale runner manager cleanup only reaches the first partition of ci_runner_machines, so project runner managers are never cleaned up, and its rate of 1,000 deletions an hour falls behind on GitLab.com. Behind the stale_runner_manager_cleanup_by_partition feature flag, this MR makes the cleanup delete from every runner type partition in turn, every 10 minutes, and pause while loose foreign key cleanup is behind.

Details

Problem

  • ci_runner_machines is partitioned by runner_type. The stale scope is a UNION with no ordering, so its LIMIT is filled from the first partition it reads. The cleanup never reaches project_type managers.
  • It deletes at most 1,000 managers an hour, which is fewer than become stale, so the backlog keeps growing.

With the feature flag enabled

  • Ci::RunnerManager.delete_stale deletes up to a batch of stale managers of one runner type. It returns the number of managers selected and the number deleted.
  • Ci::Runners::StaleManagersCleanupService takes one batch of 100 from each runner type in turn, so a large backlog in one partition doesn't use up the run for the others. Each cron run starts at the next runner type, so the budget is shared evenly across runs. A runner type stays in the rotation while its batches come back full, even if a manager contacts GitLab between the SELECT and the DELETE.
  • Each run stops at the first of:
    • ci_stale_runner_managers_cleanup_deletions_per_run deletions (default 500)
    • four minutes
    • more than ci_stale_runner_managers_cleanup_backlog_limit pending loose foreign key records for ci_runner_machines (default 5000). The limit used is never above ci_runner_manager_cap_cleanup_backlog_limit, so this cleanup always pauses before the runner manager cap from !267942 (merged) does.
  • The run logs deleted_by_runner_type, stop_reason, and lfk_pending_records, so the defaults can be tuned from the logs.

With the feature flag disabled

  • The cron moves from 36 * * * * to 6-59/10 * * * *. The cron schedule can't be switched by a feature flag, so with the flag disabled, an exclusive lease of 55 minutes keeps the previous behavior of one run an hour with the previous query.

Why a feature flag

  • The cleanup deletes more managers, which adds work to LooseForeignKeys::CiRunnersCleanupCronWorker.
  • It deletes from the project_type partition for the first time.
  • The queries are new. Disabling the flag returns to the previous behavior without a deployment.

Database

Ci::RunnerManager.delete_stale runs once per batch, up to a few times per run, every 10 minutes. <stale_deadline> is seven days ago.

1. Select stale managers of one runner type

SELECT "ci_runner_machines"."id" FROM ((SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_type" = <runner_type> AND "ci_runner_machines"."created_at" <= '<stale_deadline>' AND "ci_runner_machines"."contacted_at" IS NULL ORDER BY "ci_runner_machines"."contacted_at" DESC, "ci_runner_machines"."id" DESC LIMIT 100) UNION ALL (SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_type" = <runner_type> AND "ci_runner_machines"."created_at" <= '<stale_deadline>' AND "ci_runner_machines"."contacted_at" <= '<stale_deadline>' ORDER BY "ci_runner_machines"."contacted_at" DESC, "ci_runner_machines"."id" DESC LIMIT 100)) ci_runner_machines LIMIT 100
Partition Plan Scan
instance_type (cold) https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/85597/commands/193262 Index Scan on index_inst_type_ci_runner_machines_on_contacted_at_desc_id_desc for the IS NULL branch. For the other, a Bitmap Heap Scan on that index and idx_instance_type_ci_runner_machines_on_created_at_and_id_desc, then a sort, because few managers match
instance_type (warm) https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/85597/commands/193263 Same as above
group_type (cold) https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/85597/commands/193264 Index Scan on idx_group_type_ci_runner_machines_on_contacted_at_desc_id_desc in both branches
group_type (warm) https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/85597/commands/193265 Same as above
project_type (cold) https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/85597/commands/193266 Index Scan on index_proj_type_ci_runner_machines_on_contacted_at_desc_id_desc in both branches
project_type (warm) https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/85597/commands/193268 Same as above
Why each branch has an order

Without the order, the planner read the project_type partition by a unique index and filtered out live managers. Each batch started from the beginning again, so it filtered out more managers than the last. With the order, each branch reads only the stale range of the contacted_at index, and the cost of a batch stays the same across batches. The comparison is in !268830 (comment 4210522550).

Why a union and not an OR

The same conditions with an OR (the shape of not_contacted_since) read the group_type partition with a sequential scan, which reads the whole partition once few stale managers remain. The union lets each branch use the contacted_at index.

SELECT "ci_runner_machines"."id" FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_type" = <runner_type> AND "ci_runner_machines"."created_at" <= '<stale_deadline>' AND ("ci_runner_machines"."contacted_at" IS NULL OR "ci_runner_machines"."contacted_at" <= '<stale_deadline>') LIMIT 100
Partition Plan Scan
instance_type https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/82524/commands/190101 Bitmap Heap Scan on index_inst_type_ci_runner_machines_on_contacted_at_desc_id_desc
group_type https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/82524/commands/190103 Seq Scan
project_type https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/82524/commands/190097 Index Scan on index_proj_type_ci_runner_machines_on_runner_id_type_system_xid with a filter

These plans are for the OR shape, before the order was added.

2. Delete the selected managers

The conditions are repeated, so that a manager that contacts GitLab after being selected is kept.

DELETE FROM "ci_runner_machines" WHERE "ci_runner_machines"."runner_type" = <runner_type> AND "ci_runner_machines"."created_at" <= '<stale_deadline>' AND ("ci_runner_machines"."contacted_at" IS NULL OR "ci_runner_machines"."contacted_at" <= '<stale_deadline>') AND "ci_runner_machines"."id" IN (<ids>)

Plan (project_type, 100 ids): https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/82524/commands/190106 (Index Scan on project_type_ci_runner_machines_pkey)

References

Screenshots or screen recordings

Not applicable. This MR has no UI changes.

How to set up and validate locally

  1. In the Rails console, make a few project runner managers stale:

    Ci::RunnerManager.project_type.limit(3).update_all(created_at: 8.days.ago, contacted_at: 8.days.ago)
  2. With the feature flag disabled, run the cleanup. The project managers are not deleted, unless there are no stale instance or group managers:

    Ci::Runners::StaleManagersCleanupService.new.execute.payload
  3. Enable the feature flag and run the cleanup again. deleted_by_runner_type shows the project managers:

    Feature.enable(:stale_runner_manager_cleanup_by_partition)
    Ci::Runners::StaleManagersCleanupService.new.execute.payload

MR acceptance checklist

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

Edited by Taka Nishida

Merge request reports

Loading
Loading