Use pending build table as a source for builds queue [RUN ALL RSPEC] [RUN AS-IF-FOSS] (!64093) · Merge requests · GitLab.org / GitLab

Grzegorz Bizon requested to merge feature/remove-ci-build-from-queuing-query into master Jun 15, 2021

What does this MR do?

Use pending build table as a source for builds queue.

SQL queries after refactoring remain unchanged (except minor readability improvements), see !64093 (comment 607722309).

Shared runners

New builds queuing query for shared runners (https://explain.depesz.com/s/XPKY):

SELECT ci_pending_builds.*
FROM ci_pending_builds
INNER JOIN projects ON ci_pending_builds.project_id = projects.id
LEFT JOIN project_features ON ci_pending_builds.project_id = project_features.project_id
LEFT JOIN
  (SELECT ci_running_builds.project_id,
          COUNT(*) AS running_builds
   FROM ci_running_builds
   WHERE ci_running_builds.runner_type = 1
   GROUP BY ci_running_builds.project_id) AS project_builds ON ci_pending_builds.project_id=project_builds.project_id
WHERE projects.shared_runners_enabled = TRUE
  AND projects.pending_delete = FALSE
  AND (project_features.builds_access_level IS NULL
       or project_features.builds_access_level > 0)
  AND (projects.visibility_level = 20
       OR EXISTS
         (SELECT 1
          FROM namespaces
          INNER JOIN namespaces as project_namespaces ON project_namespaces.id = projects.namespace_id
          LEFT JOIN namespace_statistics ON namespace_statistics.namespace_id = namespaces.id
          WHERE (namespaces.id = project_namespaces.traversal_ids[1])
            AND (COALESCE(namespaces.shared_runners_minutes_limit, 0, 0) = 0
                 OR COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE((namespaces.shared_runners_minutes_limit + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), (0 + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), 0) * 60)))
  AND (NOT EXISTS
         (SELECT 1
          FROM taggings
          WHERE taggings.taggable_type = 'CommitStatus'
            AND taggings.context = 'tags'
            AND (taggable_id = ci_pending_builds.build_id)
            AND 1=1))
ORDER BY COALESCE(project_builds.running_builds, 0) ASC, ci_pending_builds.build_id ASC;

Old query for shared runners

SELECT ci_builds.*
FROM ci_builds
INNER JOIN projects ON ci_builds.project_id = projects.id
LEFT JOIN project_features ON ci_builds.project_id = project_features.project_id
LEFT JOIN
  (SELECT ci_builds.project_id,
          COUNT(*) AS running_builds
   FROM ci_builds
   WHERE ci_builds.type = 'Ci::Build'
     AND (ci_builds.status IN ('running'))
     AND ci_builds.runner_id IN
       (SELECT ci_runners.id
        FROM ci_runners
        WHERE ci_runners.runner_type = 1)
   GROUP BY ci_builds.project_id) AS project_builds ON ci_builds.project_id = project_builds.project_id
WHERE ci_builds.type = 'Ci::Build'
  AND (ci_builds.status IN ('pending'))
  AND ci_builds.runner_id IS NULL
  AND projects.shared_runners_enabled = TRUE
  AND projects.pending_delete = FALSE
  AND (project_features.builds_access_level IS NULL
       or project_features.builds_access_level > 0)
  AND (projects.visibility_level = 20
       OR EXISTS
         (SELECT 1
          FROM namespaces
          INNER JOIN namespaces as project_namespaces ON project_namespaces.id = projects.namespace_id
          LEFT JOIN namespace_statistics ON namespace_statistics.namespace_id = namespaces.id
          WHERE (namespaces.id = project_namespaces.traversal_ids[1])
            AND (COALESCE(namespaces.shared_runners_minutes_limit, 0, 0) = 0
                 OR COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE((namespaces.shared_runners_minutes_limit + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), (0 + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), 0) * 60)))
  AND (NOT EXISTS
         (SELECT 1
          FROM taggings
          WHERE taggings.taggable_type = 'CommitStatus'
            AND taggings.context = 'tags'
            AND (taggable_id = ci_builds.id)
            AND 1=1))
ORDER BY COALESCE(project_builds.running_builds, 0) ASC, ci_builds.id ASC

Group runners

New query for group runners:

SELECT ci_pending_builds.*
FROM ci_pending_builds
WHERE ci_pending_builds.project_id IN
    (SELECT projects.id
     FROM projects
     INNER JOIN project_ci_cd_settings ON project_ci_cd_settings.project_id = projects.id
     LEFT JOIN project_features ON projects.id = project_features.project_id
     WHERE projects.namespace_id IN
         (SELECT namespaces.id
          FROM
            (SELECT namespaces.*
             FROM namespaces
             INNER JOIN
               (SELECT id,
                       depth
                FROM
                  (WITH RECURSIVE base_and_descendants AS (
                                                             (SELECT namespaces.*
                                                              FROM namespaces
                                                              INNER JOIN ci_runner_namespaces ON ci_runner_namespaces.namespace_id = namespaces.id
                                                              WHERE namespaces.type = 'Group'
                                                                AND ci_runner_namespaces.runner_id = 3)
                                                           UNION
                                                             (SELECT namespaces.*
                                                              FROM namespaces,
                                                                   base_and_descendants
                                                              WHERE namespaces.type = 'Group'
                                                                AND namespaces.parent_id = base_and_descendants.id)) SELECT DISTINCT namespaces.*,
                                                                                                                                     ROW_NUMBER() OVER () as depth
                   FROM base_and_descendants AS namespaces) AS namespaces
                WHERE namespaces.type = 'Group') namespaces_join_table on namespaces_join_table.id = namespaces.id
             WHERE namespaces.type = 'Group'
             ORDER BY namespaces_join_table.depth ASC) AS namespaces
          WHERE namespaces.type = 'Group')
       AND project_ci_cd_settings.group_runners_enabled = TRUE
       AND (project_features.builds_access_level > 0
            OR project_features.builds_access_level IS NULL)
       AND projects.pending_delete = FALSE)
  AND (NOT EXISTS
         (SELECT 1
          FROM taggings
          WHERE taggings.taggable_type = 'CommitStatus'
            AND taggings.context = 'tags'
            AND (taggable_id = ci_pending_builds.build_id)
            AND 1=1))
ORDER BY build_id ASC

Old query for group runners

SELECT ci_builds.*
FROM ci_builds
WHERE ci_builds.type = 'Ci::Build'
  AND (ci_builds.status IN ('pending'))
  AND ci_builds.runner_id IS NULL
  AND ci_builds.project_id IN
    (SELECT projects.id
     FROM projects
     INNER JOIN project_ci_cd_settings ON project_ci_cd_settings.project_id = projects.id
     LEFT JOIN project_features ON projects.id = project_features.project_id
     WHERE projects.namespace_id IN
         (SELECT namespaces.id
          FROM
            (SELECT namespaces.*
             FROM namespaces
             INNER JOIN
               (SELECT id,
                       depth
                FROM
                  (WITH RECURSIVE base_and_descendants AS (
                                                             (SELECT namespaces.*
                                                              FROM namespaces
                                                              INNER JOIN ci_runner_namespaces ON ci_runner_namespaces.namespace_id = namespaces.id
                                                              WHERE namespaces.type = 'Group'
                                                                AND ci_runner_namespaces.runner_id = 6)
                                                           UNION
                                                             (SELECT namespaces.*
                                                              FROM namespaces,
                                                                   base_and_descendants
                                                              WHERE namespaces.type = 'Group'
                                                                AND namespaces.parent_id = base_and_descendants.id)) SELECT DISTINCT namespaces.*,
                                                                                                                                     ROW_NUMBER() OVER () as depth
                   FROM base_and_descendants AS namespaces) AS namespaces
                WHERE namespaces.type = 'Group') namespaces_join_table on namespaces_join_table.id = namespaces.id
             WHERE namespaces.type = 'Group'
             ORDER BY namespaces_join_table.depth ASC) AS namespaces
          WHERE namespaces.type = 'Group')
       AND project_ci_cd_settings.group_runners_enabled = TRUE
       AND (project_features.builds_access_level > 0
            OR project_features.builds_access_level IS NULL)
       AND projects.pending_delete = FALSE)
  AND (NOT EXISTS
         (SELECT 1
          FROM taggings
          WHERE taggings.taggable_type = 'CommitStatus'
            AND taggings.context = 'tags'
            AND (taggable_id = ci_builds.id)
            AND 1=1))
ORDER BY id ASC

Project runners

New query for project runners

SELECT ci_pending_builds.*
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 = 2
       AND projects.pending_delete = FALSE
       AND (project_features.builds_access_level > 0
            OR project_features.builds_access_level IS NULL))
  AND (NOT EXISTS
         (SELECT 1
          FROM taggings
          WHERE taggings.taggable_type = 'CommitStatus'
            AND taggings.context = 'tags'
            AND (taggable_id = ci_pending_builds.build_id)
            AND 1=1))
ORDER BY build_id ASC

Old query for project runners:

SELECT ci_builds.*
FROM ci_builds
WHERE ci_builds.type = 'Ci::Build'
  AND (ci_builds.status IN ('pending'))
  AND ci_builds.runner_id IS NULL
  AND ci_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 = 5
       AND projects.pending_delete = FALSE
       AND (project_features.builds_access_level > 0
            OR project_features.builds_access_level IS NULL))
  AND (NOT EXISTS
         (SELECT 1
          FROM taggings
          WHERE taggings.taggable_type = 'CommitStatus'
            AND taggings.context = 'tags'
            AND (taggable_id = ci_builds.id)
            AND 1=1))
ORDER BY id ASC

Conformity

I have included changelog trailers.
I have self-reviewed this MR per code review guidelines.
This change is backwards compatible across updates.

Edited Jun 22, 2021 by Grzegorz Bizon

Use pending build table as a source for builds queue [RUN ALL RSPEC] [RUN AS-IF-FOSS]

What does this MR do?

Shared runners

Group runners

Project runners

Conformity

Merge request reports