tables.sql 18.6 KB
Newer Older
Leo Gordon's avatar
Leo Gordon committed
1

Leo Gordon's avatar
Leo Gordon committed
2 3 4 5 6 7 8 9 10 11 12 13 14
-- The first 3 tables are from the ensembl core schema: meta, analysis and analysis_description.
-- We create them with the 'IF NOT EXISTS' option in case they already exist in the DB.

################################################################################
#
# Table structure for table 'meta' (FROM THE CORE SCHEMA)
#

CREATE TABLE IF NOT EXISTS meta (

  meta_id                     INT NOT NULL AUTO_INCREMENT,
  species_id                  INT UNSIGNED DEFAULT 1,
  meta_key                    VARCHAR(40) NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
15
  meta_value                  TEXT NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
16 17

  PRIMARY   KEY (meta_id),
Leo Gordon's avatar
Leo Gordon committed
18 19
  UNIQUE    KEY species_key_value_idx (species_id, meta_key, meta_value(255)),
            KEY species_value_idx (species_id, meta_value(255))
Leo Gordon's avatar
Leo Gordon committed
20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

) COLLATE=latin1_swedish_ci ENGINE=MyISAM;


################################################################################
#
# Table structure for table 'analysis' (FROM THE CORE SCHEMA)
#
# semantics:
#
# analysis_id - internal id
# created
#   - date to distinguish newer and older versions off the same analysis. Not
#     well maintained so far.
# logic_name - string to identify the analysis. Used mainly inside pipeline.
# db, db_version, db_file
#  - db should be a database name, db version the version of that db
#    db_file the file system location of that database,
#    probably wiser to generate from just db and configurations
# program, program_version,program_file
#  - The binary used to create a feature. Similar semantic to above
# module, module_version
#  - Perl module names (RunnableDBS usually) executing this analysis.
# parameters - a paramter string which is processed by the perl module
# gff_source, gff_feature
#  - how to make a gff dump from features with this analysis


CREATE TABLE IF NOT EXISTS analysis (

50
  analysis_id                 int(10) unsigned NOT NULL AUTO_INCREMENT, # unique internal id
Leo Gordon's avatar
Leo Gordon committed
51 52 53 54
  created                     datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  logic_name                  VARCHAR(40) NOT NULL,
  db                          VARCHAR(120),
  db_version                  VARCHAR(40),
55 56
  db_file                     VARCHAR(255),
  program                     VARCHAR(255),
Leo Gordon's avatar
Leo Gordon committed
57
  program_version             VARCHAR(40),
58
  program_file                VARCHAR(255),
Leo Gordon's avatar
Leo Gordon committed
59
  parameters                  TEXT,
60
  module                      VARCHAR(255),
Leo Gordon's avatar
Leo Gordon committed
61 62 63 64 65 66 67 68
  module_version              VARCHAR(40),
  gff_source                  VARCHAR(40),
  gff_feature                 VARCHAR(40),

  PRIMARY KEY (analysis_id),
  KEY logic_name_idx (logic_name),
  UNIQUE (logic_name)

Leo Gordon's avatar
Leo Gordon committed
69
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Leo Gordon's avatar
Leo Gordon committed
70 71 72 73 74 75 76 77 78


################################################################################
#
# Table structure for table 'analysis_description' (FROM THE CORE SCHEMA)
#

CREATE TABLE IF NOT EXISTS analysis_description (

Leo Gordon's avatar
Leo Gordon committed
79
  analysis_id                 int(10) unsigned NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
80 81 82 83 84 85 86
  description                  TEXT,
  display_label                VARCHAR(255),
  displayable                  BOOLEAN NOT NULL DEFAULT 1,
  web_data                     TEXT,

  UNIQUE KEY analysis_idx (analysis_id)

Leo Gordon's avatar
Leo Gordon committed
87
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Leo Gordon's avatar
Leo Gordon committed
88 89 90 91 92 93



#################### now, to the 'proper' Hive tables: ##############################


Leo Gordon's avatar
Leo Gordon committed
94
-- ----------------------------------------------------------------------------------
Jessica Severin's avatar
Jessica Severin committed
95
--
96
-- Table structure for table 'worker'
Jessica Severin's avatar
Jessica Severin committed
97 98 99 100 101 102 103 104 105 106
--
-- overview:
--   Table which tracks the workers of a hive as they exist out in the world.
--   Workers are created by inserting into this table so that there is only every
--   one instance of a worker object in the world.  As workers live and do work,
--   they update this table, and when they die they update.
--
-- semantics:
--

107
CREATE TABLE worker (
108
  worker_id        int(10) unsigned NOT NULL AUTO_INCREMENT,
Leo Gordon's avatar
Leo Gordon committed
109
  analysis_id      int(10) unsigned NOT NULL,
110
  meadow_type      enum('LSF', 'LOCAL') NOT NULL,
111
  meadow_name      varchar(40) DEFAULT NULL,
112 113
  host	           varchar(40) DEFAULT NULL,
  process_id       varchar(40) DEFAULT NULL,
Jessica Severin's avatar
Jessica Severin committed
114
  work_done        int(11) DEFAULT '0' NOT NULL,
115
  status           enum('READY','COMPILATION','GET_INPUT','RUN','WRITE_OUTPUT','DEAD') DEFAULT 'READY' NOT NULL,
116
  born	           timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
Jessica Severin's avatar
Jessica Severin committed
117 118
  last_check_in    datetime NOT NULL,
  died             datetime DEFAULT NULL,
119
  cause_of_death   enum('', 'NO_WORK', 'JOB_LIMIT', 'HIVE_OVERLOAD', 'LIFESPAN', 'CONTAMINATED', 'KILLED_BY_USER', 'MEMLIMIT', 'RUNLIMIT', 'FATALITY') DEFAULT '' NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
120

Leo Gordon's avatar
Leo Gordon committed
121
  PRIMARY KEY (worker_id),
122
  INDEX analysis_status (analysis_id, status)
Leo Gordon's avatar
Leo Gordon committed
123 124

) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Jessica Severin's avatar
Jessica Severin committed
125 126


Leo Gordon's avatar
Leo Gordon committed
127
-- ---------------------------------------------------------------------------------
Jessica Severin's avatar
Jessica Severin committed
128 129 130 131 132 133 134
--
-- Table structure for table 'dataflow_rule'
--
-- overview:
--   Extension of simple_rule design except that goal(to) is now in extended URL format e.g.
--   mysql://ensadmin:<pass>@ecs2:3361/compara_hive_test?analysis.logic_name='blast_NCBI34'
--   (full network address of an analysis).  The only requirement is that there are rows in 
135
--   the job, analysis, dataflow_rule, and worker tables so that the following join
Jessica Severin's avatar
Jessica Severin committed
136 137
--   works on the same database 
--   WHERE analysis.analysis_id = dataflow_rule.from_analysis_id 
138 139
--   AND   analysis.analysis_id = job.analysis_id
--   AND   analysis.analysis_id = worker.analysis_id
Jessica Severin's avatar
Jessica Severin committed
140
--
141
--   These are the rules used to create entries in the job table where the
Jessica Severin's avatar
Jessica Severin committed
142 143 144 145 146 147
--   input_id (control data) is passed from one analysis to the next to define work.
--  
--   The analysis table will be extended so that it can specify different read and write
--   databases, with the default being the database the analysis is on
--
-- semantics:
148
--   dataflow_rule_id     - internal ID
Jessica Severin's avatar
Jessica Severin committed
149
--   from_analysis_id     - foreign key to analysis table analysis_id
150
--   branch_code          - branch_code of the fan
151
--   funnel_dataflow_fule_id - dataflow_rule_id of the semaphored funnel (is NULL by default, which means dataflow is not semaphored)
Jessica Severin's avatar
Jessica Severin committed
152
--   to_analysis_url      - foreign key to net distributed analysis logic_name reference
153
--   input_id_template    - a template for generating a new input_id (not necessarily a hashref) in this dataflow; if undefined is kept original
Jessica Severin's avatar
Jessica Severin committed
154 155

CREATE TABLE dataflow_rule (
156
  dataflow_rule_id    int(10) unsigned NOT NULL AUTO_INCREMENT,
Jessica Severin's avatar
Jessica Severin committed
157 158
  from_analysis_id    int(10) unsigned NOT NULL,
  branch_code         int(10) default 1 NOT NULL,
159
  funnel_dataflow_rule_id  int(10) unsigned default NULL,
160
  to_analysis_url     varchar(255) default '' NOT NULL,
161
  input_id_template   TEXT DEFAULT NULL,
Jessica Severin's avatar
Jessica Severin committed
162

163
  PRIMARY KEY (dataflow_rule_id),
164
  UNIQUE KEY (from_analysis_id, branch_code, funnel_dataflow_rule_id, to_analysis_url, input_id_template(512))
Leo Gordon's avatar
Leo Gordon committed
165 166

) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Jessica Severin's avatar
Jessica Severin committed
167 168


Leo Gordon's avatar
Leo Gordon committed
169
-- ---------------------------------------------------------------------------------
Jessica Severin's avatar
Jessica Severin committed
170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187
--
-- Table structure for table 'analysis_ctrl_rule'
--
-- overview:
--   These rules define a higher level of control.  These rules are used to turn
--   whole anlysis nodes on/off (READY/BLOCKED).
--   If any of the condition_analyses are not 'DONE' the ctrled_analysis is set BLOCKED
--   When all conditions become 'DONE' then ctrled_analysis is set to READY
--   The workers switch the analysis.status to 'WORKING' and 'DONE'.
--   But any moment if a condition goes false, the analysis is reset to BLOCKED.
--
--   This process of watching conditions and flipping the ctrled_analysis state
--   will be accomplished by another automous agent (CtrlWatcher.pm)
--
-- semantics:
--   condition_analysis_url  - foreign key to net distributed analysis reference
--   ctrled_analysis_id      - foreign key to analysis table analysis_id

188
CREATE TABLE analysis_ctrl_rule (
Jessica Severin's avatar
Jessica Severin committed
189 190 191
  condition_analysis_url     varchar(255) default '' NOT NULL,
  ctrled_analysis_id         int(10) unsigned NOT NULL,

192
  UNIQUE (condition_analysis_url, ctrled_analysis_id)
Leo Gordon's avatar
Leo Gordon committed
193 194

) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Jessica Severin's avatar
Jessica Severin committed
195 196


Leo Gordon's avatar
Leo Gordon committed
197
-- ---------------------------------------------------------------------------------
Jessica Severin's avatar
Jessica Severin committed
198
--
199
-- Table structure for table 'job'
Jessica Severin's avatar
Jessica Severin committed
200 201
--
-- overview:
202
--   The job is the heart of this system.  It is the kiosk or blackboard
Jessica Severin's avatar
Jessica Severin committed
203
--   where workers find things to do and then post work for other works to do.
204 205
--   These jobs are created prior to work being done, are claimed by workers,
--   are updated as the work is done, with a final update on completion.
Jessica Severin's avatar
Jessica Severin committed
206 207
--
-- semantics:
208 209
--   job_id                  - autoincrement id
--   prev_job_id             - previous job which created this one (and passed input_id)
210
--   analysis_id             - the analysis_id needed to accomplish this job.
211
--   input_id                - input data passed into Analysis:RunnableDB to control the work
212
--   worker_id               - link to worker table to define which worker claimed this job
213 214
--   status                  - state the job is in
--   retry_count             - number times job had to be reset when worker failed to run it
215
--   completed               - datetime when job was completed
216 217 218
--
--   semaphore_count         - if this count is >0, the job is conditionally blocked (until this count drops to 0 or below).
--                              Default=0 means "nothing is blocking me by default".
219
--   semaphored_job_id       - the job_id of job S that is waiting for this job to decrease S's semaphore_count.
220
--                              Default=NULL means "I'm not blocking anything by default".
Jessica Severin's avatar
Jessica Severin committed
221

222
CREATE TABLE job (
223
  job_id                    int(10) NOT NULL AUTO_INCREMENT,
224
  prev_job_id               int(10) DEFAULT NULL,  # the job that created this one using a dataflow rule
Leo Gordon's avatar
Leo Gordon committed
225
  analysis_id               int(10) unsigned NOT NULL,
226 227
  input_id                  char(255) NOT NULL,
  worker_id                 int(10) unsigned DEFAULT NULL,
Leo Gordon's avatar
Leo Gordon committed
228
  status                    enum('READY','BLOCKED','CLAIMED','COMPILATION','GET_INPUT','RUN','WRITE_OUTPUT','DONE','FAILED','PASSED_ON') DEFAULT 'READY' NOT NULL,
229 230 231 232
  retry_count               int(10) default 0 NOT NULL,
  completed                 datetime DEFAULT NULL,
  runtime_msec              int(10) default NULL, 
  query_count               int(10) default NULL, 
Jessica Severin's avatar
Jessica Severin committed
233

234 235 236
  semaphore_count           int(10) NOT NULL default 0,
  semaphored_job_id         int(10) DEFAULT NULL,

237
  PRIMARY KEY                  (job_id),
238
  UNIQUE KEY input_id_analysis (input_id, analysis_id),
Leo Gordon's avatar
typo  
Leo Gordon committed
239
  INDEX analysis_status_sema_retry (analysis_id, status, semaphore_count, retry_count),
Leo Gordon's avatar
Leo Gordon committed
240
  INDEX worker_id              (worker_id)
Leo Gordon's avatar
Leo Gordon committed
241 242

) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Jessica Severin's avatar
Jessica Severin committed
243 244


245 246
-- ---------------------------------------------------------------------------------
--
247
-- Table structure for table 'job_message'
248 249
--
-- overview:
250 251
--      In case a job throws a message (via die/throw), this message is registered in this table.
--      It may or may not indicate that the job was unsuccessful via is_error flag.
252 253
--
-- semantics:
254 255
--       job_message_id     - an autoincremented primary id of the message
--               job_id     - the id of the job that threw the message
256
--            worker_id     - the worker in charge of the job at the moment
257 258
--                 time     - when the message was thrown
--                retry     - retry_count of the job when the message was thrown
259 260 261
--               status     - of the job when the message was thrown
--                  msg     - string that contains the message
--             is_error     - binary flag
262

263
CREATE TABLE job_message (
264
  job_message_id            int(10) NOT NULL AUTO_INCREMENT,
265
  job_id                    int(10) NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
266
  worker_id                 int(10) unsigned NOT NULL,
267 268
  time                      timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  retry                     int(10) DEFAULT 0 NOT NULL,
269
  status                    enum('UNKNOWN', 'COMPILATION', 'GET_INPUT', 'RUN', 'WRITE_OUTPUT', 'PASSED_ON') DEFAULT 'UNKNOWN',
270 271
  msg                       text,
  is_error                  boolean,
272

273
  PRIMARY KEY               (job_message_id),
274
  INDEX worker_id           (worker_id),
275
  INDEX job_id              (job_id)
Leo Gordon's avatar
Leo Gordon committed
276 277

) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
278 279


Leo Gordon's avatar
Leo Gordon committed
280
-- ---------------------------------------------------------------------------------
Jessica Severin's avatar
Jessica Severin committed
281
--
282
-- Table structure for table 'job_file'
Jessica Severin's avatar
Jessica Severin committed
283 284
--
-- overview:
285
--   Table which holds paths to files created by jobs
Jessica Severin's avatar
Jessica Severin committed
286 287
--   e.g. STDOUT STDERR, temp directory
--   or output data files created by the RunnableDB
288
--   There can only be one entry of a certain type for a given job
Jessica Severin's avatar
Jessica Severin committed
289 290
--
-- semantics:
291 292
--   job_id             - foreign key
--   worker_id          - link to worker table to define which worker claimed this job
293
--   retry              - copy of retry_count of job as it was run
294 295
--   stdout_file        - path to the job's STDOUT log
--   stderr_file        - path to the job's STDERR log
Jessica Severin's avatar
Jessica Severin committed
296

297
CREATE TABLE job_file (
298 299 300 301 302
  job_id                int(10) NOT NULL,
  retry                 int(10) NOT NULL,
  worker_id             int(10) unsigned NOT NULL,
  stdout_file           varchar(255),
  stderr_file           varchar(255),
Leo Gordon's avatar
Leo Gordon committed
303

304
  UNIQUE KEY job_retry  (job_id, retry),
Leo Gordon's avatar
Leo Gordon committed
305
  INDEX worker_id           (worker_id)
Leo Gordon's avatar
Leo Gordon committed
306 307

) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Jessica Severin's avatar
Jessica Severin committed
308 309


Leo Gordon's avatar
Leo Gordon committed
310
-- ---------------------------------------------------------------------------------
311 312 313 314 315 316 317 318 319 320 321 322 323
--
-- Table structure for table 'analysis_data'
--
-- overview:
--   Table which holds LONGTEXT data for use by the analysis system.
--   This data is general purpose and it's up to each analysis to
--   determine how to use it
--
-- semantics:
--   analysis_data_id   - primary id
--   data               - text blob which holds the data

CREATE TABLE analysis_data (
324
  analysis_data_id  int(10) NOT NULL AUTO_INCREMENT,
325 326 327
  data              longtext,

  PRIMARY KEY (analysis_data_id),
328
  KEY data (data(100))
329
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
330 331


Leo Gordon's avatar
Leo Gordon committed
332 333 334 335 336 337
CREATE TABLE resource_description (
    rc_id                 int(10) unsigned DEFAULT 0 NOT NULL,
    meadow_type           enum('LSF', 'LOCAL') DEFAULT 'LSF' NOT NULL,
    parameters            varchar(255) DEFAULT '' NOT NULL,
    description           varchar(255),
    PRIMARY KEY(rc_id, meadow_type)
Leo Gordon's avatar
Leo Gordon committed
338 339

) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Leo Gordon's avatar
Leo Gordon committed
340 341


Leo Gordon's avatar
Leo Gordon committed
342
-- ---------------------------------------------------------------------------------
Jessica Severin's avatar
Jessica Severin committed
343 344 345 346 347 348 349 350 351 352
--
-- Table structure for table 'analysis_stats'
--
-- overview:
--   Parallel table to analysis which provides high level statistics on the
--   state of an analysis and it's jobs.  Used to provide a fast overview, and to
--   provide final approval of 'DONE' which is used by the blocking rules to determine
--   when to unblock other analyses.  Also provides
--
-- semantics:
353
--   analysis_id          - foreign key to analysis table
354
--   status               - overview status of the jobs (cached state)
355
--   failed_job_tolerance - % of tolerated failed jobs
Leo Gordon's avatar
Leo Gordon committed
356
--   rc_id                - resource class id (analyses are grouped into disjoint classes)
Jessica Severin's avatar
Jessica Severin committed
357 358

CREATE TABLE analysis_stats (
Leo Gordon's avatar
Leo Gordon committed
359
  analysis_id           int(10) unsigned NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
360
  status                enum('BLOCKED', 'LOADING', 'SYNCHING', 'READY', 'WORKING', 'ALL_CLAIMED', 'DONE', 'FAILED')
361
                          DEFAULT 'READY' NOT NULL,
362
  batch_size            int(10) default 1 NOT NULL,
363 364 365 366
  avg_msec_per_job      int(10) default 0 NOT NULL,
  avg_input_msec_per_job      int(10) default 0 NOT NULL,
  avg_run_msec_per_job      int(10) default 0 NOT NULL,
  avg_output_msec_per_job      int(10) default 0 NOT NULL,
367
  hive_capacity         int(10) default 1 NOT NULL,
368 369 370
  behaviour             enum('STATIC', 'DYNAMIC') DEFAULT 'STATIC' NOT NULL,
  input_capacity        int(10) default 4 NOT NULL,
  output_capacity       int(10) default 4 NOT NULL,
371 372 373
  total_job_count       int(10) NOT NULL,
  unclaimed_job_count   int(10) NOT NULL,
  done_job_count        int(10) NOT NULL,
374
  max_retry_count       int(10) default 3 NOT NULL,
375
  failed_job_count      int(10) NOT NULL,
376
  failed_job_tolerance  int(10) default 0 NOT NULL,
377
  num_running_workers   int(10) default 0 NOT NULL,
Jessica Severin's avatar
Jessica Severin committed
378
  num_required_workers  int(10) NOT NULL,
Jessica Severin's avatar
Jessica Severin committed
379
  last_update           datetime NOT NULL,
380
  sync_lock             int(10) default 0 NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
381
  rc_id                 int(10) unsigned default 0 NOT NULL,
382
  can_be_empty          TINYINT UNSIGNED DEFAULT 0 NOT NULL,
383
  priority              TINYINT DEFAULT 0 NOT NULL,
Jessica Severin's avatar
Jessica Severin committed
384
  
385
  UNIQUE KEY   (analysis_id)
Leo Gordon's avatar
Leo Gordon committed
386 387

) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Jessica Severin's avatar
Jessica Severin committed
388

Leo Gordon's avatar
Leo Gordon committed
389

390 391
CREATE TABLE analysis_stats_monitor (
  time                  datetime NOT NULL default '0000-00-00 00:00:00',
Leo Gordon's avatar
Leo Gordon committed
392
  analysis_id           int(10) unsigned NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
393
  status                enum('BLOCKED', 'LOADING', 'SYNCHING', 'READY', 'WORKING', 'ALL_CLAIMED', 'DONE', 'FAILED')
394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412
                          DEFAULT 'READY' NOT NULL,
  batch_size            int(10) default 1 NOT NULL,
  avg_msec_per_job      int(10) default 0 NOT NULL,
  avg_input_msec_per_job      int(10) default 0 NOT NULL,
  avg_run_msec_per_job      int(10) default 0 NOT NULL,
  avg_output_msec_per_job      int(10) default 0 NOT NULL,
  hive_capacity         int(10) default 1 NOT NULL,
  behaviour             enum('STATIC', 'DYNAMIC') DEFAULT 'STATIC' NOT NULL,
  input_capacity        int(10) default 4 NOT NULL,
  output_capacity       int(10) default 4 NOT NULL,
  total_job_count       int(10) NOT NULL,
  unclaimed_job_count   int(10) NOT NULL,
  done_job_count        int(10) NOT NULL,
  max_retry_count       int(10) default 3 NOT NULL,
  failed_job_count      int(10) NOT NULL,
  failed_job_tolerance  int(10) default 0 NOT NULL,
  num_running_workers   int(10) default 0 NOT NULL,
  num_required_workers  int(10) NOT NULL,
  last_update           datetime NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
413
  sync_lock             int(10) default 0 NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
414
  rc_id                 int(10) unsigned default 0 NOT NULL,
415 416
  can_be_empty          TINYINT UNSIGNED DEFAULT 0 NOT NULL,
  priority              TINYINT DEFAULT 0 NOT NULL
Leo Gordon's avatar
Leo Gordon committed
417

Leo Gordon's avatar
Leo Gordon committed
418
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
419

Leo Gordon's avatar
Leo Gordon committed
420
-- ---------------------------------------------------------------------------------
421 422 423 424 425 426 427
--
-- Table structure for table 'monitor'
--
-- overview:
--   This table stores information about hive performance.
--
-- semantics:
428
--   time           - datetime
429 430 431 432 433
--   workers        - number of running workers
--   throughput     - average numb of completed jobs per sec. of the hive
--                    (this number is calculated using running workers only)
--   per_worker     - average numb of completed jobs per sec. per worker
--                    (this number is calculated using running workers only)
434
--   analysis       - analysis(es) running at that time
435 436 437 438 439 440

CREATE TABLE monitor (
  time                  datetime NOT NULL default '0000-00-00 00:00:00',
  workers               int(10) NOT NULL default '0',
  throughput            float default NULL,
  per_worker            float default NULL,
441
  analysis              varchar(255) default NULL  # not just one, but a list of logic_names
442

Leo Gordon's avatar
Leo Gordon committed
443
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
444 445


446
# Auto add schema version to database (should be overridden by Compara's table.sql)
Matthieu Muffato's avatar
Matthieu Muffato committed
447
INSERT IGNORE INTO meta (species_id, meta_key, meta_value) VALUES (NULL, 'schema_version', '67');
448