Changeset 17574 for trunk/HeuristicLab.Services.Hive.DataAccess

Timestamp:

05/29/20 13:28:25 (4 years ago)

Author:

jkarder

Message:

#3062: overhauled statistics generation and cleanup

• switched to a single thread for database cleanup and statistics generation (executed sequentially)
• switched to preemptive deletion of items that are in status DeletionPending (for jobs: statelogs, taskdata, tasks)
• added code that aborts tasks whose jobs have already been marked for deletion
• added method UseTransactionAndSubmit in addition to UseTransaction in PersistenceManager
• updated DAO methods and introduced more bare metal sql
• introduced DAO methods for batch deletion
• fixed usage of enum values in DAO sql queries
• deleted unnecessary triggers tr_JobDeleteCascade and tr_TaskDeleteCascade in Prepare Hive Database.sql
• changed scheduling for less interference with janitor and other heartbeats
  • increased scheduling patience from 20 to 70 seconds (to wait longer to get the mutex for scheduling)
  • changed signature of ITaskScheduler.Schedule
  • added base class for TaskSchedulers and moved assignment of tasks to slaves into it
  • changed RoundRobinTaskScheduler to use bare metal sql
• made MessageContainer a storable type (leftover)
• updated HiveJanitorServiceInstaller.nsi

Location:

trunk/HeuristicLab.Services.Hive.DataAccess/3.3

Files:

• Daos/HiveStatistics/DimJobDao.cs (modified) (2 diffs)
• Daos/JobDao.cs (modified) (5 diffs)
• Daos/StateLogDao.cs (modified) (2 diffs)
• Daos/TaskDao.cs (modified) (4 diffs)
• Daos/TaskDataDao.cs (modified) (2 diffs)
• Interfaces/IPersistenceManager.cs (modified) (1 diff)
• Manager/PersistenceManager.cs (modified) (2 diffs)
• SQL Scripts/Prepare Hive Database.sql (modified) (1 diff)

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/HiveStatistics/DimJobDao.cs

@@ -44 +44 @@
     }
 
+    public void UpdateExistingDimJobs() {
+      DataContext.ExecuteCommand(UpdateExistingDimJobsQuery);
+    }
+
     #region Compiled queries
     private static readonly Func<DataContext, Guid, DimJob> GetByIdQuery =
@@ -51 +55 @@
          select dimJob).SingleOrDefault());
     #endregion
+
+    #region String queries
+    private const string UpdateExistingDimJobsQuery = @"
+UPDATE u
+SET
+  u.JobName = case when x.JobId is null then u.JobName else x.JobName end,
+  u.TotalTasks = x.TotalTasks,
+  u.CompletedTasks = x.CompletedTasks,
+  u.DateCompleted =
+    case when x.totaltasks = x.CompletedTasks
+      then (case when x.JobId is null and x.DateCompleted is null then GETDATE() else x.DateCompleted end)
+    else u.DateCompleted
+  end,
+  u.ProjectId = case when x.JobId is null then u.ProjectId else x.ProjectId end
+FROM [statistics].dimjob u
+JOIN (
+  SELECT
+    dj.JobId as DimJobId,
+    j.JobId as JobId,
+    j.Name as JobName,
+    COUNT(*) as TotalTasks,
+    SUM(
+    CASE
+    WHEN TaskState in ('Finished', 'Aborted', 'Failed') then 1
+    ELSE 0
+    END) as CompletedTasks,
+    MAX(EndTime) as DateCompleted,
+    dp.ProjectId as ProjectId
+  from [statistics].DimJob dj
+  join [statistics].FactTask ft on dj.JobId = ft.JobId
+  left join Job j on j.JobId = dj.JobId
+  left join [statistics].DimProject dp on j.ProjectId = dp.ProjectId 
+  where dj.DateCompleted is null and dp.DateExpired is null
+  group by dj.JobId, j.JobId, j.Name, dp.ProjectId 
+) as x on u.JobId = x.DimJobId";
+    #endregion
   }
 }

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/JobDao.cs

@@ -37 +37 @@
     }
 
+    public int DeleteByState(JobState state, int batchSize) {
+      return DataContext.ExecuteCommand(DeleteTopNByStateQueryString, batchSize, Enum.GetName(typeof(JobState), state));
+    }
+
     public IEnumerable<Job> GetByProjectId(Guid id) {
       return GetByProjectIdQuery(DataContext, id);
@@ -43 +47 @@
     public IEnumerable<Job> GetByProjectIds(IEnumerable<Guid> projectIds) {
       string paramProjectIds = string.Join(",", projectIds.ToList().Select(x => string.Format("'{0}'", x)));
-      if(!string.IsNullOrWhiteSpace(paramProjectIds)) {
+      if (!string.IsNullOrWhiteSpace(paramProjectIds)) {
         string queryString = string.Format(GetByProjectIdsQueryString, paramProjectIds);
         return DataContext.ExecuteQuery<Job>(queryString);
@@ -51 +55 @@
 
     public IEnumerable<Job> GetByState(JobState state) {
-      return GetByStateQuery(DataContext, state);
+      return GetByStateQuery(DataContext, state.ToString());
     }
 
     public IEnumerable<Guid> GetJobIdsByState(JobState state) {
-      return GetJobIdsByStateQuery(DataContext, state);
+      return GetJobIdsByStateQuery(DataContext, state.ToString());
     }
 
@@ -74 +78 @@
          where job.ProjectId == projectId
          select job));
-    private static readonly Func<DataContext, JobState, IEnumerable<Job>> GetByStateQuery =
-      CompiledQuery.Compile((DataContext db, JobState jobState) =>
+    private static readonly Func<DataContext, string, IEnumerable<Job>> GetByStateQuery =
+      CompiledQuery.Compile((DataContext db, string jobState) =>
         (from job in db.GetTable<Job>()
-         where job.State == jobState
+         where job.State.ToString() == jobState
          select job));
-    private static readonly Func<DataContext, JobState, IEnumerable<Guid>> GetJobIdsByStateQuery =
-      CompiledQuery.Compile((DataContext db, JobState jobState) =>
+    private static readonly Func<DataContext, string, IEnumerable<Guid>> GetJobIdsByStateQuery =
+      CompiledQuery.Compile((DataContext db, string jobState) =>
         (from job in db.GetTable<Job>()
-         where job.State == jobState
+         where job.State.ToString() == jobState
          select job.JobId));
     private static readonly Func<DataContext, IEnumerable<Job>> GetJobsReadyForDeletionQuery =
@@ -101 +105 @@
       WHERE JobState = {0}
     ";
+    private const string DeleteTopNByStateQueryString = @"
+      DELETE TOP ({0})
+      FROM [Job]
+      WHERE JobState = {1}
+    ";
     private const string GetStatisticsPendingJobs = @"
       SELECT DISTINCT j.*

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/StateLogDao.cs

@@ -40 +40 @@
     }
 
+    public int DeleteObsolete(int batchSize) {
+      return DataContext.ExecuteCommand(DeleteObsoleteQueryString, batchSize);
+    }
+
     #region Compiled queries
     private static readonly Func<DataContext, Guid, StateLog> GetByIdQuery =
@@ -54 +58 @@
          select stateLog).First(x => x.SlaveId != null));
     #endregion
+
+    #region String queries
+    private const string DeleteObsoleteQueryString = @"
+delete top ({0}) s
+from statelog s
+  join task t on t.taskid = s.taskid
+  join job j on j.jobid = t.jobid
+where j.jobstate = 'deletionpending'
+    ";
+    #endregion
   }
 }

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/TaskDao.cs

@@ -50 +50 @@
       //Because there is at the moment no case where this makes sense (there don't exist parent tasks which need to be calculated), 
       //we skip this step because it's wasted runtime
-      return DataContext.ExecuteQuery<TaskPriorityInfo>(GetWaitingTasksQueryString, 
-        slave.ResourceId, 
-        Enum.GetName(typeof(TaskState), TaskState.Waiting), 
-        slave.FreeCores, 
+      return DataContext.ExecuteQuery<TaskPriorityInfo>(GetWaitingTasksQueryString,
+        slave.ResourceId,
+        Enum.GetName(typeof(TaskState), TaskState.Waiting),
+        slave.FreeCores,
         slave.FreeMemory).ToList();
     }
@@ -65 +65 @@
     /// <returns></returns>
     public IEnumerable<Task> GetParentTasks(IEnumerable<Guid> resourceIds, int count, bool finished) {
-    var query = from t in Table
-                where t.State == TaskState.Waiting
-                    && t.IsParentTask
-                    && t.Job.AssignedJobResources.All(x => resourceIds.ToList().Contains(x.ResourceId))
-                    && t.FinishWhenChildJobsFinished == finished
-                    && t.ChildJobs.Any()
-                    && t.ChildJobs.All(x =>
-                      x.State == TaskState.Finished
-                      || x.State == TaskState.Aborted
-                      || x.State == TaskState.Failed)
+      var query = from t in Table
+                  where t.State == TaskState.Waiting
+                      && t.IsParentTask
+                      && t.Job.AssignedJobResources.All(x => resourceIds.ToList().Contains(x.ResourceId))
+                      && t.FinishWhenChildJobsFinished == finished
+                      && t.ChildJobs.Any()
+                      && t.ChildJobs.All(x =>
+                        x.State == TaskState.Finished
+                        || x.State == TaskState.Aborted
+                        || x.State == TaskState.Failed)
                   orderby t.Priority descending
                   select t;
@@ -82 +82 @@
     public void UpdateExecutionTime(Guid taskId, double executionTime) {
       DataContext.ExecuteCommand(UpdateExecutionTimeQuery, executionTime, DateTime.Now, taskId);
+    }
+
+    public int DeleteObsolete(int batchSize) {
+      return DataContext.ExecuteCommand(DeleteObsoleteQueryString, batchSize);
     }
 
@@ -129 +133 @@
        WHERE TaskId = {2}
     ";
+
+    private const string DeleteObsoleteQueryString = @"
+delete top ({0}) t1
+from task t1
+  left join task t2 on t1.taskid = t2.parenttaskid
+  join job j on j.jobid = t1.jobid
+where j.jobstate = 'deletionpending' and t2.taskid is null
+";
     #endregion
   }

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/TaskDataDao.cs

@@ -32 +32 @@
     }
 
+    public int DeleteObsolete(int batchSize) {
+      return DataContext.ExecuteCommand(DeleteObsoleteQueryString, batchSize);
+    }
+
     #region Compiled queries
     private static readonly Func<DataContext, Guid, TaskData> GetByIdQuery =
@@ -39 +43 @@
          select taskData).SingleOrDefault());
     #endregion
+
+    #region String queries
+    private const string DeleteObsoleteQueryString = @"
+delete top ({0}) td
+from taskdata td
+  join task t on t.taskid = td.taskid
+  join job j on j.jobid = t.jobid
+where j.jobstate = 'deletionpending'
+    ";
+    #endregion
   }
 }

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Interfaces/IPersistenceManager.cs

@@ -64 +64 @@
     #region Transaction management
     void UseTransaction(Action call, bool repeatableRead = false, bool longRunning = false);
+    void UseTransactionAndSubmit(Action call, bool repeatableRead = false, bool longRunning = false);
     T UseTransaction<T>(Func<T> call, bool repeatableRead = false, bool longRunning = false);
+    T UseTransactionAndSubmit<T>(Func<T> call, bool repeatableRead = false, bool longRunning = false);
     #endregion
 

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Manager/PersistenceManager.cs

@@ -184 +184 @@
     #region Transaction management
     public void UseTransaction(Action call, bool repeatableRead = false, bool longRunning = false) {
-      UseTransaction<object>(() => {
-        call();
-        return null;
-      });
+      UseTransaction<object>(() => { call(); return null; }, repeatableRead, longRunning);
+    }
+
+    public void UseTransactionAndSubmit(Action call, bool repeatableRead = false, bool longRunning = false) {
+      UseTransaction(() => { call(); SubmitChanges(); }, repeatableRead, longRunning);
     }
 
@@ -211 +212 @@
     }
 
+    public T UseTransactionAndSubmit<T>(Func<T> call, bool repeatableRead = false, bool longRunning = false) {
+      return UseTransaction(() => { var res = call(); SubmitChanges(); return res; }, repeatableRead, longRunning);
+    }
+
     private static TransactionScope CreateTransaction(bool repeatableRead, bool longRunning) {
       var options = new TransactionOptions {

trunk/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql

@@ -141 +141 @@
 INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId])
 GO
-
-
-
--- OBSOLETE - DO NOT PERFORM (start)
-/****** Object:  Trigger [dbo].[tr_JobDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
-SET ANSI_NULLS ON
-GO
-SET QUOTED_IDENTIFIER ON
-GO
--- =============================================
--- Author:    cneumuel
--- Create date: 19.04.2011
--- Description: (1) Writes the execution times of deleted jobs into DeletedJobStats to ensure correct statistics
---        (2) Deletes all associated jobs. This cannot be done with cascading delete, 
---              because the job table defines a INSTEAD OF DELETE trigger itself, which
---              is not compatible with cascading deletes.
--- =============================================
-CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS 
-BEGIN
-    DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
-    DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
-END
-GO
-
--- =============================================
--- Author:    cneumuel
--- Create date: 11.11.2010
--- Description: Recursively deletes all child-jobs of a job when it is deleted. (Source: http://devio.wordpress.com/2008/05/23/recursive-delete-in-sql-server/)
--- =============================================DeletedJobStatistics
-CREATE TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS 
-BEGIN
-    -- recursively delete jobs
-    CREATE TABLE #Table( 
-        TaskId uniqueidentifier 
-    )
-    INSERT INTO #Table (TaskId)
-    SELECT TaskId FROM deleted
-    
-    DECLARE @c INT
-    SET @c = 0
-    
-    WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
-        SELECT @c = COUNT(TaskId) FROM #Table
-        
-        INSERT INTO #Table (TaskId)
-            SELECT Task.TaskId
-            FROM Task
-            LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
-            WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
-                AND #Table.TaskId IS NULL
-    END
-    
-    DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
-    DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
-END
-GO
--- OBSOLETE (end)
-