Skip to content

Decouple project runners queuing query from projects table

Marius Bobin requested to merge 340583-remove-projects-from-project-runners-queuing-query into master

What does this MR do and why?

Related to #340583 (closed)

Old query

SELECT "ci_pending_builds"."build_id" FROM "ci_pending_builds" WHERE "ci_pending_builds"."project_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN "ci_runner_projects" ON "projects"."id" = "ci_runner_projects"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "ci_runner_projects"."runner_id" = 357328 AND "projects"."pending_delete" = FALSE AND ("project_features"."builds_access_level" > 0 OR "project_features"."builds_access_level" IS NULL)) AND (tag_ids <@ ARRAY[NULL]::int[]) ORDER BY build_id ASC

New query

SELECT "ci_pending_builds"."build_id" FROM "ci_pending_builds" WHERE "ci_pending_builds"."project_id" IN (SELECT "ci_runner_projects"."project_id" FROM "ci_runner_projects" WHERE "ci_runner_projects"."runner_id" = 357328) AND (tag_ids <@ ARRAY[NULL]::int[]) ORDER BY build_id ASC

I'm using the runner with the maximum number of projects on .com for the queries, but it looks like there are no pending jobs for those projects at the moment.

gitlabhq_production=> select runner_id, count(*) from ci_runner_projects group by runner_id order by 2 desc limit 1;
 runner_id | count
-----------+-------
    357328 |   736
(1 row)

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

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.

Edited by Marius Bobin

Merge request reports