Update dispatchQuery to use min_cores

DiegoTavares · DiegoTavares · commit 2eb4936cf579 · 2020-09-30T09:51:51.000-07:00
Sorting jobs only by priority causes a situation where low priority jobs can get starved by a constant flow of high priority jobs.
The new formula adds a modifier to the sorting rank to take into account the number of cores the job is requesting and also the number of days the job is waiting on the queue.
Priorities numbers over 200 will mostly override the formula and work as a priority only based scheduling.
sort = priority + (100 * (1 - (job.cores/job.int_min_cores))) + (age in days)

Besides that, also take layer_int_cores_min into account when filtering folder_resourse limitations to avoid allocating more cores than the folder limits.

(cherry picked from commit 566411aeeddc60983a30eabe121fd03263d05525)
diff --git a/cuebot/src/main/java/com/imageworks/spcue/dao/postgres/DispatchQuery.java b/cuebot/src/main/java/com/imageworks/spcue/dao/postgres/DispatchQuery.java
@@ -1,6 +1,6 @@
 
 /*
- * Copyright Contributors to the OpenCue Project
+ * Copyright (c) 2018 Sony Pictures Imageworks Inc.
  *
  * Licensed under the Apache License, Version 2.0 (the "License");
  * you may not use this file except in compliance with the License.
@@ -23,11 +23,23 @@ public class DispatchQuery {
 
     public static final String FIND_JOBS_BY_SHOW =
         "/* FIND_JOBS_BY_SHOW */ " +
-        "SELECT pk_job, int_priority, rank FROM ( " +
+            "SELECT pk_job, int_priority, rank FROM ( " +
             "SELECT " +
-                "ROW_NUMBER() OVER (ORDER BY job_resource.int_priority DESC) AS rank, " +
-                "job.pk_job, " +
-                "job_resource.int_priority " +
+                "ROW_NUMBER() OVER (ORDER BY int_priority DESC) AS rank, " +
+                "pk_job, " +
+                "int_priority " +
+            "FROM ( " +
+            "SELECT " +
+                "job.pk_job as pk_job, " +
+                "/* sort = priority + (100 * (1 - (job.cores/job.int_min_cores))) + (age in days) */ " +
+                "CAST( " +
+                        "job_resource.int_priority + ( " +
+                            "100 * (CASE WHEN job_resource.int_min_cores <= 0 THEN 0 " +
+                        "ELSE 1 - job_resource.int_cores/job_resource.int_min_cores " +
+                        "END) " +
+				") + ( " +
+                "(DATE_PART('days', NOW()) - DATE_PART('days', job.ts_updated)) " +
+            ") as INT) as int_priority " +
             "FROM " +
                 "job            , " +
                 "job_resource   , " +
@@ -50,7 +62,7 @@ public class DispatchQuery {
                     "(" +
                         "folder_resource.int_max_cores = -1 " +
                     "OR " +
-                        "folder_resource.int_cores < folder_resource.int_max_cores " +
+                        "folder_resource.int_cores + layer.int_cores_min < folder_resource.int_max_cores " +
                     ") " +
                 "AND job.str_state                  = 'PENDING' " +
                 "AND job.b_paused                   = false " +
@@ -91,7 +103,7 @@ public class DispatchQuery {
                         "sum_running.int_sum_running < limit_record.int_max_value " +
                         "OR sum_running.int_sum_running IS NULL " +
                 ") " +
-        ") AS t1 WHERE rank < ?";
+        ") AS t1 ) AS t2 WHERE rank < ?";
 
 
     public static final String FIND_JOBS_BY_GROUP =
