Changeset 17574 for trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/JobDao.cs

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

File:

• trunk/HeuristicLab.Services.Hive.DataAccess/3.3/Daos/JobDao.cs (modified) (5 diffs)

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.*