[no subject]

[Ludovic Courtès]

branch: main
commit 17338588d4862b04e9e405c1244a2ea703b50d98
Author: Ludovic Courtès <ludo@gnu.org>
AuthorDate: Tue May 28 16:12:39 2024 +0200

    database: Rewrite query for ‘db-get-pending-build’.
    
    Previous query would perform several sequential scans, ending up taking
    ~50s on a database with 4M rows in ‘Builds’.
    
    This, in turn, would prevent ‘cuirass remote-worker’ from processing
    incoming messages in a timely fashion: it would reply to
    ‘worker-request-work’ messages after an ever-increasing amount of time;
    having not seen ‘worker-ping’ messages in a timely fashion, it would
    occasionally remove workers.
    
    This new query takes less than a second (thanks, Chris!).
    
    * src/cuirass/database.scm (db-get-pending-build): Rewrite query.
    Lower #:threshold.
    
    Co-authored-by: Christopher Baines <mail@cbaines.net>
---
 src/cuirass/database.scm | 27 ++++++++++++++++-----------
 1 file changed, 16 insertions(+), 11 deletions(-)

diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index 2f29d45..456b624 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -1646,18 +1646,23 @@ highest priority (lowest integer value)."
               ;; Note: Keep ordering in sync with that of the
               ;; 'status+submission-time' filter of 'db-get-builds'.
               (exec-query/bind db "
-WITH pending_dependencies AS
-(SELECT Builds.id, count(dep.id) as deps FROM Builds
-LEFT JOIN BuildDependencies as bd ON bd.source = Builds.id
-LEFT JOIN Builds AS dep ON bd.target = dep.id AND dep.status != 0
-WHERE Builds.status = " (build-status scheduled)
-" AND Builds.system = " system
-" GROUP BY Builds.id
-ORDER BY Builds.priority ASC, Builds.timestamp ASC)
-SELECT id FROM pending_dependencies WHERE deps = 0 LIMIT 1;"))
+  SELECT Builds.id
+  FROM Builds
+  WHERE Builds.status = " (build-status scheduled) "
+    AND Builds.system =  " system "
+    AND NOT EXISTS (
+      SELECT 1
+      FROM BuildDependencies as bd
+      INNER JOIN Builds AS dep
+        ON bd.target = dep.id
+       AND dep.status != 0
+      WHERE bd.source = Builds.id
+    )
+  ORDER BY Builds.priority ASC, Builds.timestamp ASC
+  LIMIT 1;"))
         ((id) (db-get-build (string->number id)))
-        (else #f)))
-    #:threshold 20))
+        (_ #f)))
+    #:threshold 10))
 
 (define-record-type* <checkout> checkout make-checkout
   checkout?