Draft: Clean up stale runner failed build records
pedropombeiro/421889/2-create-runner_failed_build-records branch, please change to master before merging
What does this MR do and why?
| MR | Description |
|---|---|
| !129972 (closed) | Create p_ci_runner_failed_builds partitioned table |
| !130174 (closed) | Populate p_ci_runner_failed_builds table with failed builds |
|
!130203 (closed) |
Clean up stale runner failed build records |
| omnibus-gitlab!7113 (closed) | Add cronjob to clean up stale runner failed bui... (omnibus-gitlab!7113 - closed) |
| gitlab-org/charts/gitlab!3361 (closed) | Add cronjob to clean up stale runner failed bui... (gitlab-org/charts/gitlab!3361 - closed) |
This MR follows up on !130174 (closed) to ensure that we clean up unnecessary p_ci_runner_failed_builds records (we try to leave the latest 2 records, or anything within the last hour).
The project-scoped FF :runner_failed_builds (rollout issue) will control the build cleanup, but the cleanup will only happen when the FF is enabled globally. The plan is to start with a smallish project in prod and test some builds. We can then start rolling out to more projects and then globally, at which point the cleaning will kick in.
Part of #421889 (closed)
Related MRs
- https://gitlab.com/gitlab-org/omnibus-gitlab/-/merge_requests/
- https://gitlab.com/gitlab-org/charts/gitlab/-/merge_requests/
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.
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.
Database query plan
SQL
INSERT INTO p_ci_runner_failed_builds (build_id, partition_id, finished_at)
SELECT id AS build_id, partition_id,
finished_at
FROM ci_builds
WHERE finished_at IS NOT NULL
ORDER BY id DESC
LIMIT 1000;
DELETE FROM "p_ci_runner_failed_builds" WHERE "p_ci_runner_failed_builds"."id" IN (SELECT "p_ci_runner_failed_builds"."id" FROM "p_ci_runner_failed_builds" WHERE "p_ci_runner_failed_builds"."id" IN (SELECT "p_ci_runner_failed_builds"."id" FROM ((SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."contacted_at" IS NULL)
UNION ALL
(SELECT "ci_runner_machines".* FROM "ci_runner_machines" WHERE "ci_runner_machines"."contacted_at" <= '2023-01-17 10:06:04.652717')) ci_runner_machines WHERE "ci_runner_machines"."created_at" <= '2023-01-17 10:06:04.651305') LIMIT 1000)