Free cookie consent management tool by TermsFeed Policy Generator

Ignore:
Timestamp:
08/18/15 10:08:58 (9 years ago)
Author:
ascheibe
Message:

#2388 merged hive statistics branch into trunk

Location:
trunk/sources
Files:
3 edited

Legend:

Unmodified
Added
Removed

  • trunk/sources

  • trunk/sources/HeuristicLab.Services.Hive.DataAccess

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

    r9665 r12878  
     1/* HeuristicLab
     2 * Copyright (C) 2002-2015 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
     3 *
     4 * This file is part of HeuristicLab.
     5 *
     6 * HeuristicLab is free software: you can redistribute it and/or modify
     7 * it under the terms of the GNU General Public License as published by
     8 * the Free Software Foundation, either version 3 of the License, or
     9 * (at your option) any later version.
     10 *
     11 * HeuristicLab is distributed in the hope that it will be useful,
     12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
     13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
     14 * GNU General Public License for more details.
     15 *
     16 * You should have received a copy of the GNU General Public License
     17 * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
     18 */
     19
     20/* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
    121USE [HeuristicLab.Hive-3.3]
    2 /* this script is supposed to be executed after the plain DB is generated by the linq-to-sql schema */
    3 /* adds default values */
    4 /* creates delete and update cascades */
    5 /* creates indices */
    6 /* creates views */
    7 /* creates triggers */
    8 
    922
    1023ALTER TABLE [dbo].[AssignedResources]  DROP  CONSTRAINT [Task_AssignedResource]
    … …  
    93106GO
    94107
    95 ALTER TABLE [dbo].[Statistics] ALTER COLUMN StatisticsId ADD ROWGUIDCOL;
    96 ALTER TABLE [dbo].[Statistics] WITH NOCHECK ADD CONSTRAINT [DF_Statistics_StatisticsId] DEFAULT (NEWSEQUENTIALID()) FOR StatisticsId;
    97 GO
    98 
    99 ALTER TABLE [dbo].[DeletedJobStatistics] ALTER COLUMN DeletedJobStatisticsId ADD ROWGUIDCOL;
    100 ALTER TABLE [dbo].[DeletedJobStatistics] WITH NOCHECK ADD CONSTRAINT [DF_DeletedJobStatistics_DeletedJobStatisticsId] DEFAULT (NEWSEQUENTIALID()) FOR DeletedJobStatisticsId;
    101 GO
    102 
    103 ALTER TABLE [dbo].[SlaveStatistics]  DROP  CONSTRAINT [Statistics_SlaveStatistics]
    104 ALTER TABLE [dbo].[SlaveStatistics]  WITH CHECK ADD  CONSTRAINT [Statistics_SlaveStatistics] FOREIGN KEY([StatisticsId])
    105 REFERENCES [dbo].[Statistics] ([StatisticsId])
    106 ON UPDATE CASCADE
    107 ON DELETE CASCADE
    108 GO
    109 
    110 ALTER TABLE [dbo].[UserStatistics]  DROP  CONSTRAINT [Statistics_UserStatistics]
    111 ALTER TABLE [dbo].[UserStatistics]  WITH CHECK ADD  CONSTRAINT [Statistics_UserStatistics] FOREIGN KEY([StatisticsId])
    112 REFERENCES [dbo].[Statistics] ([StatisticsId])
    113 ON UPDATE CASCADE
    114 ON DELETE CASCADE
    115 GO
    116 
    117108/* create indices */
    118109CREATE INDEX Index_RequiredPlugins_TaskId ON RequiredPlugins(TaskId);
    119110GO
    120111
    121 /* views */
    122 -- =============================================
    123 -- Author:    cneumuel
    124 -- Description: Returns the first StateLog entry for each job
    125 -- =============================================
    126 CREATE VIEW [dbo].[view_FirstState]
    127 AS
    128 SELECT     sl.TaskId, sl.DateTime, sl.State
    129 FROM         dbo.StateLog AS sl INNER JOIN
    130                           (SELECT     TaskId, MIN(DateTime) AS DateTime
    131                             FROM          dbo.StateLog
    132                             GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId
    133 
     112-- speed up joins between Job and Task
     113CREATE NONCLUSTERED INDEX [TaskJobIdIndex]
     114ON [dbo].[Task] ([JobId])
     115INCLUDE ([TaskId],[TaskState],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[CoresNeeded],[MemoryNeeded],[IsParentTask],[FinishWhenChildJobsFinished],[Command],[IsPrivileged])
    134116GO
    135117
    136 -- =============================================
    137 -- Author:    cneumuel
    138 -- Description: Returns the last StateLog entry for each job
    139 -- =============================================
    140 CREATE VIEW [dbo].[view_LastState]
    141 AS
    142 SELECT     sl.TaskId, sl.DateTime, sl.State
    143 FROM         dbo.StateLog AS sl INNER JOIN
    144                           (SELECT     TaskId, MAX(DateTime) AS DateTime
    145                             FROM          dbo.StateLog
    146                             GROUP BY TaskId) AS minDate ON sl.DateTime = minDate.DateTime AND sl.TaskId = minDate.TaskId
     118-- this is an index to speed up the GetWaitingTasks() method
     119CREATE NONCLUSTERED INDEX [TaskGetWaitingTasksIndex]
     120ON [dbo].[Task] ([TaskState],[IsParentTask],[FinishWhenChildJobsFinished],[CoresNeeded],[MemoryNeeded])
     121INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId],[IsPrivileged])
    147122GO
    148123
    149 -- =============================================
    150 -- Author:    cneumuel
    151 -- Description: returns aggregates statistic information for every minute
    152 -- =============================================
    153 CREATE VIEW [dbo].[view_Statistics]
    154 AS
    155 SELECT  CONVERT(VARCHAR(19), MIN(s.Timestamp), 120) AS DateTime, SUM(ss.Cores) AS Cores, SUM(ss.FreeCores) AS FreeCores,
    156         AVG(ss.CpuUtilization) AS CpuUtilization, SUM(ss.Memory) AS Memory, SUM(ss.FreeMemory) AS FreeMemory, x.exSum AS ExecutionTimeHours,
    157         x.exFinishedSum AS ExecutionTimeFinished, x.exStartToEndSum AS StartToEndTimeFinished
    158 FROM         dbo.SlaveStatistics AS ss INNER JOIN
    159                       dbo.[Statistics] AS s ON ss.StatisticsId = s.StatisticsId INNER JOIN
    160                       dbo.Resource AS r ON ss.SlaveId = r.ResourceId INNER JOIN
    161                           (SELECT     StatisticsId, SUM(ExecutionTimeMs) / 1000 / 60 / 60 AS exSum, SUM(ExecutionTimeMsFinishedJobs) / 1000 / 60 / 60 AS exFinishedSum,
    162                                                    SUM(StartToEndTimeMs) / 1000 / 60 / 60 AS exStartToEndSum
    163                             FROM          dbo.UserStatistics AS us
    164                             GROUP BY StatisticsId) AS x ON s.StatisticsId = x.StatisticsId
    165 GROUP BY s.StatisticsId, x.exSum, x.exFinishedSum, x.exStartToEndSum
    166124
    167 /* triggers */
    168 GO
    169125/****** Object:  Trigger [dbo].[tr_JobDeleteCascade]    Script Date: 04/19/2011 16:31:53 ******/
    170126SET ANSI_NULLS ON
    … …  
    182138CREATE TRIGGER [dbo].[tr_JobDeleteCascade] ON [dbo].[Job] INSTEAD OF DELETE AS
    183139BEGIN
    184   DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
    185   DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
     140    DELETE Task FROM deleted, Task WHERE deleted.JobId = Task.JobId
     141    DELETE Job FROM deleted, Job WHERE deleted.JobId = Job.JobId
    186142END
    187143GO
    … …  
    191147-- Create date: 11.11.2010
    192148-- 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/)
    193 -- =============================================
     149-- =============================================DeletedJobStatistics
    194150CREATE TRIGGER [dbo].[tr_TaskDeleteCascade] ON [dbo].[Task] INSTEAD OF DELETE AS
    195151BEGIN
    196   -- add statistics
    197   INSERT INTO dbo.DeletedJobStatistics (UserId, ExecutionTimeS, ExecutionTimeSFinishedJobs, StartToEndTimeS)
    198   SELECT
    199     he.OwnerUserId AS UserId,
    200     ROUND(SUM(j.ExecutionTimeMs) / 1000, 0) AS ExecutionTimeS, 
    201     ROUND(ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN j.ExecutionTimeMs END), 0) / 1000, 0) AS ExecutionTimeSFinishedJobs,
    202     ISNULL(SUM(CASE ls.State WHEN 'Finished' THEN DATEDIFF(s, fs.DateTime, ls.DateTime) ELSE 0 END), 0) AS StartToEndTimeS
    203   FROM
    204     deleted j,
    205     Job he,
    206     view_FirstState fs,
    207     view_LastState ls
    208   WHERE
    209     he.JobId = j.JobId AND
    210     fs.TaskId = j.TaskId AND
    211     ls.TaskId = j.TaskId
    212   GROUP BY he.OwnerUserId
    213 
    214   -- recursively delete jobs
    215   CREATE TABLE #Table(
    216     TaskId uniqueidentifier
    217   )
    218   INSERT INTO #Table (TaskId)
    219   SELECT TaskId FROM deleted
    220  
    221   DECLARE @c INT
    222   SET @c = 0
    223  
    224   WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
    225     SELECT @c = COUNT(TaskId) FROM #Table
    226    
    227     INSERT INTO #Table (TaskId)
    228       SELECT Task.TaskId
    229       FROM Task
    230       LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
    231       WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
    232         AND #Table.TaskId IS NULL
    233   END
    234  
    235   DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
    236   DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
     152    -- recursively delete jobs
     153    CREATE TABLE #Table(
     154        TaskId uniqueidentifier
     155    )
     156    INSERT INTO #Table (TaskId)
     157    SELECT TaskId FROM deleted
     158   
     159    DECLARE @c INT
     160    SET @c = 0
     161   
     162    WHILE @c <> (SELECT COUNT(TaskId) FROM #Table) BEGIN
     163        SELECT @c = COUNT(TaskId) FROM #Table
     164       
     165        INSERT INTO #Table (TaskId)
     166            SELECT Task.TaskId
     167            FROM Task
     168            LEFT OUTER JOIN #Table ON Task.TaskId = #Table.TaskId
     169            WHERE Task.ParentTaskId IN (SELECT TaskId FROM #Table)
     170                AND #Table.TaskId IS NULL
     171    END
     172   
     173    DELETE TaskData FROM TaskData INNER JOIN #Table ON TaskData.TaskId = #Table.TaskId
     174    DELETE Task FROM Task INNER JOIN #Table ON Task.TaskId = #Table.TaskId
    237175END
    238176GO
    239177
    240178
    241 CREATE TRIGGER [dbo].[tr_StatisticsDeleteCascade] ON [dbo].[Statistics] INSTEAD OF DELETE AS
    242 BEGIN
    243   DELETE SlaveStatistics FROM deleted, SlaveStatistics WHERE deleted.StatisticsId = SlaveStatistics.StatisticsId
    244   -- should also remove UserStatistics here
    245   DELETE [Statistics] FROM deleted, [Statistics] WHERE deleted.StatisticsId = [Statistics].StatisticsId
    246 END
    247 GO
    248 
    249 
    250 -- ============================================================
    251 -- Description: Create indices to speed up execution of queries
    252 -- ============================================================
    253 
    254 -- speed up joins between Job and Task
    255 CREATE NONCLUSTERED INDEX [TaskJobIdIndex]
    256 ON [dbo].[Task] ([JobId])
    257 INCLUDE ([TaskId],[TaskState],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[CoresNeeded],[MemoryNeeded],[IsParentTask],[FinishWhenChildJobsFinished],[Command],[IsPrivileged])
    258 GO
    259 
    260 -- this is an index to speed up the GetWaitingTasks() method
    261 CREATE NONCLUSTERED INDEX [TaskGetWaitingTasksIndex]
    262 ON [dbo].[Task] ([TaskState],[IsParentTask],[FinishWhenChildJobsFinished],[CoresNeeded],[MemoryNeeded])
    263 INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId],[IsPrivileged])
    264 GO
Note: See TracChangeset for help on using the changeset viewer.