Free cookie consent management tool by TermsFeed Policy Generator

source: branches/3106_AnalyticContinuedFractionsRegression/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Prepare Hive Database.sql

Last change on this file was 17574, checked in by jkarder, 5 years ago

#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 size: 6.4 KB
Line 
1/* HeuristicLab
2 * Copyright (C) 2002-2019 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 */
21USE [HeuristicLab.Hive-3.4]
22
23ALTER TABLE [dbo].[AssignedProjectResource]  DROP  CONSTRAINT [Project_AssignedProjectResource]
24ALTER TABLE [dbo].[AssignedProjectResource]  WITH CHECK ADD  CONSTRAINT [Project_AssignedProjectResource] FOREIGN KEY([ProjectId])
25REFERENCES [dbo].[Project] ([ProjectId])
26ON UPDATE CASCADE
27ON DELETE CASCADE
28GO
29ALTER TABLE [dbo].[AssignedProjectResource]  DROP  CONSTRAINT [Resource_AssignedProjectResource]
30ALTER TABLE [dbo].[AssignedProjectResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedProjectResource] FOREIGN KEY([ResourceId])
31REFERENCES [dbo].[Resource] ([ResourceId])
32ON UPDATE CASCADE
33ON DELETE CASCADE
34GO
35ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Job_AssignedJobResource]
36ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Job_AssignedJobResource] FOREIGN KEY([JobId])
37REFERENCES [dbo].[Job] ([JobId])
38ON UPDATE CASCADE
39ON DELETE CASCADE
40GO
41ALTER TABLE [dbo].[AssignedJobResource]  DROP  CONSTRAINT [Resource_AssignedJobResource]
42ALTER TABLE [dbo].[AssignedJobResource]  WITH CHECK ADD  CONSTRAINT [Resource_AssignedJobResource] FOREIGN KEY([ResourceId])
43REFERENCES [dbo].[Resource] ([ResourceId])
44ON UPDATE CASCADE
45ON DELETE CASCADE
46GO
47
48ALTER TABLE dbo.Task ALTER COLUMN TaskId ADD ROWGUIDCOL;
49ALTER TABLE dbo.Task WITH NOCHECK ADD CONSTRAINT [DF_Task_TaskId] DEFAULT (NEWSEQUENTIALID()) FOR TaskId;
50GO
51
52ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Task_StateLog]
53ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Task_StateLog] FOREIGN KEY([TaskId])
54REFERENCES [dbo].[Task] ([TaskId])
55ON UPDATE CASCADE
56ON DELETE CASCADE
57GO
58ALTER TABLE [dbo].[StateLog]  DROP  CONSTRAINT [Resource_StateLog]
59ALTER TABLE [dbo].[StateLog]  WITH CHECK ADD CONSTRAINT [Resource_StateLog] FOREIGN KEY([SlaveId])
60REFERENCES [dbo].[Resource] ([ResourceId])
61ON UPDATE CASCADE
62ON DELETE SET NULL
63GO
64
65ALTER TABLE dbo.Plugin ALTER COLUMN PluginId ADD ROWGUIDCOL;
66ALTER TABLE dbo.Plugin WITH NOCHECK ADD CONSTRAINT [DF_Plugin_PluginId] DEFAULT (NEWSEQUENTIALID()) FOR PluginId;
67
68ALTER TABLE dbo.PluginData WITH NOCHECK ADD CONSTRAINT [DF_PluginData_PluginDataId] DEFAULT (NEWSEQUENTIALID()) FOR PluginDataId;
69
70ALTER TABLE [dbo].[PluginData]  DROP  CONSTRAINT [Plugin_PluginData]
71ALTER TABLE [dbo].[PluginData]  WITH CHECK ADD  CONSTRAINT [Plugin_PluginData] FOREIGN KEY([PluginId])
72REFERENCES [dbo].[Plugin] ([PluginId])
73ON UPDATE CASCADE
74ON DELETE CASCADE
75GO
76
77ALTER TABLE dbo.RequiredPlugins ALTER COLUMN RequiredPluginId ADD ROWGUIDCOL;
78ALTER TABLE dbo.RequiredPlugins WITH NOCHECK ADD CONSTRAINT [DF_RequiredPlugins_RequiredPluginId] DEFAULT (NEWSEQUENTIALID()) FOR RequiredPluginId;
79
80ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Task_RequiredPlugin]
81ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Task_RequiredPlugin] FOREIGN KEY([TaskId])
82REFERENCES [dbo].[Task] ([TaskId])
83ON UPDATE CASCADE
84ON DELETE CASCADE
85GO
86
87ALTER TABLE [dbo].[RequiredPlugins]  DROP  CONSTRAINT [Plugin_RequiredPlugin]
88ALTER TABLE [dbo].[RequiredPlugins]  WITH CHECK ADD  CONSTRAINT [Plugin_RequiredPlugin] FOREIGN KEY([PluginId])
89REFERENCES [dbo].[Plugin] ([PluginId])
90ON UPDATE CASCADE
91ON DELETE CASCADE
92GO
93
94ALTER TABLE dbo.Resource ALTER COLUMN ResourceId ADD ROWGUIDCOL;
95ALTER TABLE dbo.Resource WITH NOCHECK ADD CONSTRAINT [DF_Resource_ResourceId] DEFAULT (NEWSEQUENTIALID()) FOR ResourceId;
96
97ALTER TABLE dbo.Downtime ALTER COLUMN DowntimeId ADD ROWGUIDCOL;
98ALTER TABLE dbo.Downtime WITH NOCHECK ADD CONSTRAINT [DF_Downtime_DowntimeId] DEFAULT (NEWSEQUENTIALID()) FOR DowntimeId;
99
100ALTER TABLE dbo.Job ALTER COLUMN JobId ADD ROWGUIDCOL;
101ALTER TABLE dbo.Job WITH NOCHECK ADD CONSTRAINT [DF_Job_JobId] DEFAULT (NEWSEQUENTIALID()) FOR JobId;
102ALTER TABLE [dbo].[Job]  DROP  CONSTRAINT [Project_Job]
103ALTER TABLE [dbo].[Job]  WITH CHECK ADD  CONSTRAINT [Project_Job] FOREIGN KEY([ProjectId])
104REFERENCES [dbo].[Project] ([ProjectId])
105ON UPDATE CASCADE
106GO
107
108ALTER TABLE dbo.StateLog ALTER COLUMN StateLogId ADD ROWGUIDCOL;
109ALTER TABLE dbo.StateLog WITH NOCHECK ADD CONSTRAINT [DF_StateLog_StateLogId] DEFAULT (NEWSEQUENTIALID()) FOR StateLogId;
110
111ALTER TABLE [dbo].[JobPermission]  DROP  CONSTRAINT [Job_JobPermission]
112ALTER TABLE [dbo].[JobPermission]  WITH CHECK ADD CONSTRAINT [Job_JobPermission] FOREIGN KEY([JobId])
113REFERENCES [dbo].[Job] ([JobId])
114ON UPDATE CASCADE
115ON DELETE CASCADE
116GO
117
118ALTER TABLE dbo.Project ALTER COLUMN ProjectId ADD ROWGUIDCOL;
119ALTER TABLE dbo.Project WITH NOCHECK ADD CONSTRAINT [DF_Project_ProjectId] DEFAULT (NEWSEQUENTIALID()) FOR ProjectId;
120
121ALTER TABLE [dbo].[ProjectPermission]  DROP  CONSTRAINT [Project_ProjectPermission]
122ALTER TABLE [dbo].[ProjectPermission]  WITH CHECK ADD CONSTRAINT [Project_ProjectPermission] FOREIGN KEY([ProjectId])
123REFERENCES [dbo].[Project] ([ProjectId])
124ON UPDATE CASCADE
125ON DELETE CASCADE
126GO
127
128/* create indices */
129CREATE INDEX Index_RequiredPlugins_TaskId ON RequiredPlugins(TaskId);
130GO
131
132-- speed up joins between Job and Task
133CREATE NONCLUSTERED INDEX [TaskJobIdIndex]
134ON [dbo].[Task] ([JobId])
135INCLUDE ([TaskId],[TaskState],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[CoresNeeded],[MemoryNeeded],[IsParentTask],[FinishWhenChildJobsFinished],[Command])
136GO
137
138-- this is an index to speed up the GetWaitingTasks() method
139CREATE NONCLUSTERED INDEX [TaskGetWaitingTasksIndex]
140ON [dbo].[Task] ([TaskState],[IsParentTask],[FinishWhenChildJobsFinished],[CoresNeeded],[MemoryNeeded])
141INCLUDE ([TaskId],[ExecutionTimeMs],[LastHeartbeat],[ParentTaskId],[Priority],[Command],[JobId])
142GO
Note: See TracBrowser for help on using the repository browser.