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

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/SQL Scripts/Prepare Hive Database.sql (modified) (1 diff)

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)
-