UKvGspmM

1
2/** 
3The project is basically we need to execute 1,000,000 runs on a super cluster of 10,000 nodes. 
4
5
6The entire set of runs is grouped together in an AES_ID, which has a name.
7
8
9Effectively we have a set of Instances and a set of ParamConfigurations and we create a job for the cross product of these two sets, in the JOB table. The JOB_ID is the primary identifier for this. STATUS controls whether the job is unassigned (could be taken by a worker), DISPATCHED (assigned to a worker), or COMPLETED (finished by a worker).
10
11Our workers on the super cluster when they start up, they know the AES_ID and contact the database and get a new HOST_ID for themselves. They then query the jobs table for unassigned jobs, and take ownership of them. When they are done they put the results in the job_results table (irrelevant). When there are no more jobs to do  they update the hosts table and set STATUS = 'DONE'.
12
13What I need is that when the final worker does this, the final worker knows about it to do some clean up work.
14
15I'm aware that the Instance table and the ParamConfigurations table aren't normalized but that's irrelevant. 
16*/
17
18AES_ID is just an identifier for the
19CREATE TABLE IF NOT EXISTS `algorithm_execution_set` (
20  `AES_ID` int(11) NOT NULL AUTO_INCREMENT,
21  `NAME` varchar(1024) NOT NULL,
22  `CREATE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
23  `STATUS` enum('SETUP','DISPATCH','COMPLETE') NOT NULL DEFAULT 'SETUP',
24  PRIMARY KEY (`AES_ID`)
25) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
26
27
28
29CREATE TABLE IF NOT EXISTS `hosts` (
30  `HOST_ID` int(11) NOT NULL AUTO_INCREMENT,
31  `AES_ID` int(11) NOT NULL,
32  `STATUS` enum('RUNNING','DONE') NOT NULL DEFAULT 'RUNNING',
33  `CLUSTER_JOB_ID` varchar(64) NOT NULL,
34  `HOSTNAME` varchar(64) NOT NULL,
35  `START_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
36  `END_TIME` date NOT NULL,
37  PRIMARY KEY (`HOST_ID`),
38  KEY `AES_ID` (`AES_ID`)
39) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
40
41
42
43CREATE TABLE IF NOT EXISTS `job` (
44  `JOB_ID` int(11) NOT NULL,
45  `AES_ID` int(11) NOT NULL,
46  `INSTANCE_ID` int(11) NOT NULL,
47  `CONFIG_ID` int(11) NOT NULL,
48  `SEED` bigint(20) NOT NULL,
49  `CAPTIME` double NOT NULL,
50  `CENSORED` tinyint(1) NOT NULL,
51  `HOST_ID` int(11) DEFAULT NULL,
52  `STATUS` enum('NEW','SCHEDULED','COMPLETED') NOT NULL DEFAULT 'NEW',
53  `LAST_MODIFIED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
54  PRIMARY KEY (`JOB_ID`),
55  KEY `instanceID` (`INSTANCE_ID`),
56  KEY `paramID` (`CONFIG_ID`),
57  KEY `ownerID` (`HOST_ID`),
58  KEY `status` (`STATUS`)
59) ENGINE=InnoDB DEFAULT CHARSET=latin1;
60
61
62
63-- --------------------------------------------------------
64
65--
66-- Table structure for table `instances`
67--
68
69CREATE TABLE IF NOT EXISTS `instances` (
70  `INSTANCE_ID` int(11) NOT NULL AUTO_INCREMENT,
71  `AES_ID` int(11) NOT NULL,
72  `INSTANCE_FILENAME` varchar(1024) NOT NULL,
73  PRIMARY KEY (`INSTANCE_ID`),
74  KEY `AES_ID` (`AES_ID`)
75) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
76
77--
78-- Dumping data for table `instances`
79--
80
81
82
83
84--
85-- Dumping data for table `job`
86--
87
88
89-- --------------------------------------------------------
90
91--
92-- Table structure for table `job_results`
93--
94
95CREATE TABLE IF NOT EXISTS `job_results` (
96  `JOB_ID` int(11) NOT NULL,
97  `RUN_RESULT` enum('TIMEOUT','SAT','UNSAT','CRASHED') NOT NULL,
98  `RESULT_RUNLENGTH` int(11) NOT NULL,
99  `RESULT_QUALITY` double NOT NULL,
100  `RESULT_SEED` int(11) NOT NULL,
101  `RESULT_LINE` varchar(255) NOT NULL,
102  `RUNTIME` double NOT NULL,
103  PRIMARY KEY (`JOB_ID`)
104) ENGINE=InnoDB DEFAULT CHARSET=latin1;
105
106--
107-- Dumping data for table `job_results`
108--
109
110
111-- --------------------------------------------------------
112
113--
114-- Table structure for table `paramconfigurations`
115--
116
117CREATE TABLE IF NOT EXISTS `paramconfigurations` (
118  `PARAM_ID` int(11) NOT NULL AUTO_INCREMENT,
119  `AES_ID` int(11) NOT NULL,
120  `PARAM_ARRAY_STR` varchar(1024) NOT NULL,
121  `PARAM_STRING` text NOT NULL,
122  PRIMARY KEY (`PARAM_ID`),
123  KEY `AES_ID` (`AES_ID`)
124) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
125
126--
127-- Dumping data for table `paramconfigurations`
128--
129
130
131--
132-- Constraints for dumped tables
133--
134
135--
136-- Constraints for table `instances`
137--
138ALTER TABLE `instances`
139  ADD CONSTRAINT `instances_ibfk_1` FOREIGN KEY (`AES_ID`) REFERENCES `algorithm_execution_set` (`AES_ID`);
140
141--
142-- Constraints for table `job`
143--
144ALTER TABLE `job`
145  ADD CONSTRAINT `job_ibfk_1` FOREIGN KEY (`INSTANCE_ID`) REFERENCES `instances` (`INSTANCE_ID`),
146  ADD CONSTRAINT `job_ibfk_2` FOREIGN KEY (`CONFIG_ID`) REFERENCES `job` (`CONFIG_ID`);
147
148--
149-- Constraints for table `job_results`
150--
151ALTER TABLE `job_results`
152  ADD CONSTRAINT `job_results_ibfk_1` FOREIGN KEY (`JOB_ID`) REFERENCES `job` (`JOB_ID`) ON DELETE CASCADE;
153
154--
155-- Constraints for table `paramconfigurations`
156--
157ALTER TABLE `paramconfigurations`
158  ADD CONSTRAINT `paramconfigurations_ibfk_1` FOREIGN KEY (`AES_ID`) REFERENCES `algorithm_execution_set` (`AES_ID`);