From d4a262db292a8ac311ef72760c63701f7b632be4 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Sun, 7 Nov 2021 19:29:44 +0000 Subject: Refactor how unprocessed builds are tracked Previously, the allocator worked out the derived priorities for each build. Unfortunately this is quite a complex query, and took lots of time. As a result of this, I think the WAL could grow excessively while this long query was running. To try and mitigate this, add a new table to keep track of the derived priorities for unprocessed builds. This requires some maintenance to keep up to date, which in turn will make things like submitting builds slower, but I think this might help keep transaction length and WAL size down overall. --- ..._unprocessed_builds_with_derived_priorities.sql | 7 +++ ..._unprocessed_builds_with_derived_priorities.sql | 7 +++ ..._unprocessed_builds_with_derived_priorities.sql | 7 +++ sqitch/sqitch.plan | 1 + ..._unprocessed_builds_with_derived_priorities.sql | 67 ++++++++++++++++++++++ ..._unprocessed_builds_with_derived_priorities.sql | 7 +++ ..._unprocessed_builds_with_derived_priorities.sql | 7 +++ 7 files changed, 103 insertions(+) create mode 100644 sqitch/pg/deploy/create_unprocessed_builds_with_derived_priorities.sql create mode 100644 sqitch/pg/revert/create_unprocessed_builds_with_derived_priorities.sql create mode 100644 sqitch/pg/verify/create_unprocessed_builds_with_derived_priorities.sql create mode 100644 sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql create mode 100644 sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql create mode 100644 sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql (limited to 'sqitch') diff --git a/sqitch/pg/deploy/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/deploy/create_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..adfda14 --- /dev/null +++ b/sqitch/pg/deploy/create_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Deploy guix-build-coordinator:create_unprocessed_builds_with_derived_priorities to pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/pg/revert/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/revert/create_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..e16cdf5 --- /dev/null +++ b/sqitch/pg/revert/create_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Revert guix-build-coordinator:create_unprocessed_builds_with_derived_priorities from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/pg/verify/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/pg/verify/create_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..a8a5251 --- /dev/null +++ b/sqitch/pg/verify/create_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Verify guix-build-coordinator:create_unprocessed_builds_with_derived_priorities on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index 198b4de..68e770e 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -34,3 +34,4 @@ fix_allocated_builds_attempt_2 2021-03-30T08:27:18Z Christopher Baines # Fix setup_failure_missing_inputs create_outputs 2021-05-21T13:37:49Z Christopher Baines # Create the outputs table create_systems 2021-05-21T17:22:52Z Christopher Baines # Create the systems table +create_unprocessed_builds_with_derived_priorities 2021-10-22T14:52:18Z Christopher Baines # Create unprocessed_builds_with_derived_priorities diff --git a/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..67cc872 --- /dev/null +++ b/sqitch/sqlite/deploy/create_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,67 @@ +-- Deploy guix-build-coordinator:create_unprocessed_builds_with_derived_priorities to sqlite + +BEGIN; + +CREATE TABLE unprocessed_builds_with_derived_priorities ( + build_id INTEGER PRIMARY KEY REFERENCES builds (id), + derived_priority INTEGER NOT NULL, + all_inputs_built BOOLEAN NOT NULL +); + +INSERT INTO unprocessed_builds_with_derived_priorities WITH RECURSIVE unprocessed_builds AS ( + SELECT id + FROM builds + WHERE processed = 0 + AND canceled = 0 + AND ( + deferred_until IS NULL + OR + deferred_until < datetime('now') + ) +), builds_with_derived_priority( + id, derivation_id, derived_priority + ) AS ( + SELECT builds.id, builds.derivation_id, builds.priority + FROM builds + INNER JOIN unprocessed_builds + ON builds.id = unprocessed_builds.id +UNION + SELECT builds.id, builds.derivation_id, + max(builds.priority, builds_with_derived_priority.derived_priority) + FROM builds_with_derived_priority + INNER JOIN derivation_outputs + ON builds_with_derived_priority.derivation_id = + derivation_outputs.derivation_id + INNER JOIN derivation_outputs AS all_derivation_outputs + ON all_derivation_outputs.output_id = derivation_outputs.output_id + INNER JOIN derivation_inputs + ON derivation_inputs.derivation_output_id = all_derivation_outputs.id + INNER JOIN builds + ON builds.processed = 0 + AND builds.derivation_id = derivation_inputs.derivation_id +), data AS ( + SELECT builds.id AS build_id, MAX(derived_priority) + FROM builds_with_derived_priority + INNER JOIN builds + ON builds.id = builds_with_derived_priority.id + WHERE builds.processed = 0 + GROUP BY builds.id +) +SELECT data.*, + NOT EXISTS ( + SELECT 1 + FROM derivation_inputs + INNER JOIN derivation_outputs + ON derivation_inputs.derivation_output_id = derivation_outputs.id + INNER JOIN unbuilt_outputs + ON unbuilt_outputs.output_id = derivation_outputs.output_id + WHERE builds.derivation_id = derivation_inputs.derivation_id + ) +FROM data +INNER JOIN builds ON builds.id = data.build_id; + +CREATE INDEX unprocessed_builds_with_derived_priorities_derived_priorities_index_for_all_inputs_built + ON unprocessed_builds_with_derived_priorities (derived_priority) + WHERE all_inputs_built = 1; + +COMMIT; diff --git a/sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..51b7f92 --- /dev/null +++ b/sqitch/sqlite/revert/create_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Revert guix-build-coordinator:create_unprocessed_builds_with_derived_priorities from sqlite + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql b/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql new file mode 100644 index 0000000..3a17113 --- /dev/null +++ b/sqitch/sqlite/verify/create_unprocessed_builds_with_derived_priorities.sql @@ -0,0 +1,7 @@ +-- Verify guix-build-coordinator:create_unprocessed_builds_with_derived_priorities on sqlite + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- cgit v1.2.3