Skip to content

Denormalize remaining CI minutes to CI::PendingBuild

Max Orefice requested to merge mo-add-minutes-exceeded-to-pending-builds into master

Ref: #329763 (closed)

What does this MR do?

This MR denormalizes remaining CI minutes available in our new ci_pending_builds table.

It includes the following items:

  • Add 2 new columns to ci_pending_builds
    • minutes_exceeded: we can drop this build if it's set to true
    • namespace_id: allow efficiently to update pending builds when a user buy more minutes
  • Introduce new service to encapsulate what happens when a user purchase more minutes
  • Persist this data when creating a new entry to ci_pending_builds behind a feature flag

Why are we doing this?

This is part of our epic to scale up the Ci::Build table.

By avoiding to query the ci_builds we simplify our queuing system to determing which build will get pick up by runners.

Database review

1️⃣ New columns

DB migration 
$ bin/rails db:migrate
== 20210706151154 AddRemainingCiMinutesToCiPendingBuild: migrating ============
-- add_column(:ci_pending_builds, :namespace_id, :bigint)
   -> 0.0015s
-- add_column(:ci_pending_builds, :minutes_exceeded, :boolean, {:null=>false, :default=>false})
   -> 0.0020s
== 20210706151154 AddRemainingCiMinutesToCiPendingBuild: migrated (0.0109s) ===
DB rollback 
$ bin/rails db:rollback
== 20210706151154 AddRemainingCiMinutesToCiPendingBuild: reverting ============
-- remove_column(:ci_pending_builds, :minutes_exceeded)
   -> 0.0015s
-- remove_column(:ci_pending_builds, :namespace_id)
   -> 0.0007s
== 20210706151154 AddRemainingCiMinutesToCiPendingBuild: reverted (0.0100s) ===

2️⃣ Foreign key & index

DB migration 
$ bin/rails db:migrate
== 20210706151446 AddNamespaceForeignKeyToCiPendingBuild: migrating ===========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:ci_pending_builds, :namespace_id, {:name=>"index_ci_pending_builds_on_namespace_id", :algorithm=>:concurrently})
   -> 0.0053s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:ci_pending_builds, :namespace_id, {:name=>"index_ci_pending_builds_on_namespace_id", :algorithm=>:concurrently})
   -> 0.0035s
-- execute("RESET ALL")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:ci_pending_builds)
   -> 0.0042s
-- execute("ALTER TABLE ci_pending_builds\nADD CONSTRAINT fk_fdc0137e4a\nFOREIGN KEY (namespace_id)\nREFERENCES namespaces (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0029s
-- execute("ALTER TABLE ci_pending_builds VALIDATE CONSTRAINT fk_fdc0137e4a;")
   -> 0.0133s
== 20210706151446 AddNamespaceForeignKeyToCiPendingBuild: migrated (0.0349s) ==
DB rollback 
$ bin/rails db:rollback
== 20210706151446 AddNamespaceForeignKeyToCiPendingBuild: reverting ===========
-- foreign_keys(:ci_pending_builds)
   -> 0.0044s
-- remove_foreign_key(:ci_pending_builds, {:column=>:namespace_id})
   -> 0.0045s
-- transaction_open?()
   -> 0.0000s
-- indexes(:ci_pending_builds)
   -> 0.0031s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- remove_index(:ci_pending_builds, {:algorithm=>:concurrently, :name=>"index_ci_pending_builds_on_namespace_id"})
   -> 0.0039s
-- execute("RESET ALL")
   -> 0.0006s
== 20210706151446 AddNamespaceForeignKeyToCiPendingBuild: reverted (0.0178s) ==

3️⃣ Database query

SQL query 
UPDATE
    "ci_pending_builds"
SET
    "minutes_exceeded" = TRUE
WHERE
    "ci_pending_builds"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
                SELECT
                    "namespaces".*
                FROM
                    "namespaces"
                WHERE
                    "namespaces"."id" = 9970)
            UNION (
                SELECT
                    "namespaces".*
                FROM
                    "namespaces",
                    "base_and_descendants"
                WHERE
                    "namespaces"."parent_id" = "base_and_descendants"."id"))
            SELECT
                "id"
            FROM
                "base_and_descendants" AS "namespaces")

Does this MR meet the acceptance criteria?

Conformity

Edited by Max Orefice

Merge request reports