diff options
author | Christopher Baines <mail@cbaines.net> | 2021-11-07 19:29:44 +0000 |
---|---|---|
committer | Christopher Baines <mail@cbaines.net> | 2021-11-07 19:29:44 +0000 |
commit | d4a262db292a8ac311ef72760c63701f7b632be4 (patch) | |
tree | 9c69e931ec6e270b5fee1e0e1b00dd796a106309 /sqitch/sqlite | |
parent | a4d0ec6bc564bbe98d3e6c03be41de16edbda928 (diff) | |
download | build-coordinator-d4a262db292a8ac311ef72760c63701f7b632be4.tar build-coordinator-d4a262db292a8ac311ef72760c63701f7b632be4.tar.gz |
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.
Diffstat (limited to 'sqitch/sqlite')
3 files changed, 81 insertions, 0 deletions
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; |