tables.mysql 28.5 KB
Newer Older
1 2
/*
    This is MySQL version of EnsEMBL Hive database schema file.
Leo Gordon's avatar
Leo Gordon committed
3

4 5
    It has been annotated with @-tags.
    The following command is used to create HTML documentation:
Leo Gordon's avatar
Leo Gordon committed
6 7 8
        perl $ENSEMBL_CVS_ROOT_DIR/ensembl-production/scripts/sql2html.pl \
            -i $ENSEMBL_CVS_ROOT_DIR/ensembl-hive/sql/tables.mysql -d Hive -intro $ENSEMBL_CVS_ROOT_DIR/ensembl-hive/docs/hive_schema.inc \
            -sort_headers 0 -sort_tables 0 -o $ENSEMBL_CVS_ROOT_DIR/ensembl-hive/docs/hive_schema.html
9

Leo Gordon's avatar
Leo Gordon committed
10

11 12
    Adding the following line into the header of the previous output will make it look prettier:
        <link rel="stylesheet" type="text/css" media="all" href="ehive_doc.css" />
13 14 15 16


LICENSE

17
    Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
18 19 20 21 22 23 24 25 26 27

    Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License.
    You may obtain a copy of the License at

         http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software distributed under the License
    is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and limitations under the License.

28 29 30 31
CONTACT

    Please subscribe to the Hive mailing list:  http://listserver.ebi.ac.uk/mailman/listinfo/ehive-users  to discuss Hive-related questions or to be notified of our updates

32 33 34 35 36 37 38 39
*/


/**
@header Pipeline structure
@colour #C70C09
*/

Leo Gordon's avatar
Leo Gordon committed
40 41 42 43 44 45 46 47 48 49 50 51 52 53
/**
@table  hive_meta

@colour #000000

@desc This table keeps several important hive-specific pipeline-wide key-value pairs
        such as hive_sql_schema_version, hive_use_triggers and hive_pipeline_name.

@column meta_key        the KEY of KEY-VALUE pairs (primary key)
@column meta_value      the VALUE of KEY-VALUE pairs
*/

CREATE TABLE hive_meta (
    meta_key                VARCHAR(255) NOT NULL PRIMARY KEY,
54
    meta_value              MEDIUMTEXT
Leo Gordon's avatar
Leo Gordon committed
55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

) COLLATE=latin1_swedish_ci ENGINE=InnoDB;


/**
@table  pipeline_wide_parameters

@colour #000000

@desc This table contains a simple hash between pipeline_wide_parameter names and their values.
      The same data used to live in 'meta' table until both the schema and the API were finally separated from Ensembl Core.

@column param_name      the KEY of KEY-VALUE pairs (primary key)
@column param_value     the VALUE of KEY-VALUE pairs
*/

CREATE TABLE pipeline_wide_parameters (
    param_name              VARCHAR(255) NOT NULL PRIMARY KEY,
73
    param_value             MEDIUMTEXT,
Leo Gordon's avatar
Leo Gordon committed
74 75 76 77 78

    KEY value_idx (param_name(255))
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;


79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
/**
@table  analysis_base

@colour #C70C09

@desc   Each Analysis is a node of the pipeline diagram.
        It acts both as a "class" to which Jobs belong (and inherit from it certain properties)
        and as a "container" for them (Jobs of an Analysis can be blocking all Jobs of another Analysis).

@column analysis_id             a unique ID that is also a foreign key to most of the other tables
@column logic_name              the name of the Analysis object
@column module                  the Perl module name that runs this Analysis
@column parameters              a stingified hash of parameters common to all jobs of the Analysis
@column resource_class_id       link to the resource_class table
@column failed_job_tolerance    % of tolerated failed Jobs
@column max_retry_count         how many times a job of this Analysis will be retried (unless there is no point)
@column can_be_empty            if TRUE, this Analysis will not be blocking if/while it doesn't have any jobs
@column priority                an Analysis with higher priority will be more likely chosen on Worker's specialization
@column meadow_type             if defined, forces this Analysis to be run only on the given Meadow
@column analysis_capacity       if defined, limits the number of Workers of this particular Analysis that are allowed to run in parallel
*/
Leo Gordon's avatar
Leo Gordon committed
100

101
CREATE TABLE analysis_base (
Leo Gordon's avatar
Leo Gordon committed
102
    analysis_id             INTEGER     NOT NULL PRIMARY KEY AUTO_INCREMENT,
103
    logic_name              VARCHAR(255) NOT NULL,
104
    module                  VARCHAR(255) NOT NULL,
105
    parameters              MEDIUMTEXT,
Leo Gordon's avatar
Leo Gordon committed
106 107 108 109 110
    resource_class_id       INTEGER     NOT NULL,
    failed_job_tolerance    INTEGER     NOT NULL DEFAULT 0,
    max_retry_count         INTEGER     NOT NULL DEFAULT 3,
    can_be_empty            SMALLINT    NOT NULL DEFAULT 0,
    priority                SMALLINT    NOT NULL DEFAULT 0,
111
    meadow_type             VARCHAR(255)         DEFAULT NULL,
Leo Gordon's avatar
Leo Gordon committed
112
    analysis_capacity       INTEGER              DEFAULT NULL,
Leo Gordon's avatar
Leo Gordon committed
113

Leo Gordon's avatar
Leo Gordon committed
114
    UNIQUE  KEY logic_name_idx (logic_name)
Leo Gordon's avatar
Leo Gordon committed
115
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Leo Gordon's avatar
Leo Gordon committed
116 117


118 119
/**
@table  analysis_stats
Jessica Severin's avatar
Jessica Severin committed
120

121
@colour #C70C09
122

123 124 125 126
@desc   Parallel table to analysis_base 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
127

128 129 130 131
@column analysis_id             foreign-keyed to the corresponding analysis_base entry
@column batch_size              how many jobs are claimed in one claiming operation before Worker starts executing them
@column hive_capacity           a reciprocal limiter on the number of Workers running at the same time (dependent on Workers of other Analyses)
@column status                  cached state of the Analysis
Leo Gordon's avatar
Leo Gordon committed
132

133 134 135 136 137
@column total_job_count         total number of Jobs of this Analysis
@column semaphored_job_count    number of Jobs of this Analysis that are in SEMAPHORED state
@column ready_job_count         number of Jobs of this Analysis that are in READY state
@column done_job_count          number of Jobs of this Analysis that are in DONE state
@column failed_job_count        number of Jobs of this Analysis that are in FAILED state
Jessica Severin's avatar
Jessica Severin committed
138

139
@column num_running_workers     number of running Workers of this Analysis
Jessica Severin's avatar
Jessica Severin committed
140

141 142 143
@column behaviour               whether hive_capacity is set or is dynamically calculated based on timers
@column input_capacity          used to compute hive_capacity in DYNAMIC mode
@column output_capacity         used to compute hive_capacity in DYNAMIC mode
Jessica Severin's avatar
Jessica Severin committed
144

145 146 147 148
@column avg_msec_per_job        weighted average used to compute DYNAMIC hive_capacity
@column avg_input_msec_per_job  weighted average used to compute DYNAMIC hive_capacity
@column avg_run_msec_per_job    weighted average used to compute DYNAMIC hive_capacity
@column avg_output_msec_per_job weighted average used to compute DYNAMIC hive_capacity
Jessica Severin's avatar
Jessica Severin committed
149

150
@column when_updated            when this entry was last updated
151 152
@column sync_lock               a binary lock flag to prevent simultaneous updates
*/
Leo Gordon's avatar
Leo Gordon committed
153

154
CREATE TABLE analysis_stats (
Leo Gordon's avatar
Leo Gordon committed
155 156 157 158 159 160 161 162 163 164
    analysis_id             INTEGER     NOT NULL,
    batch_size              INTEGER     NOT NULL DEFAULT 1,
    hive_capacity           INTEGER              DEFAULT NULL,
    status                  ENUM('BLOCKED', 'LOADING', 'SYNCHING', 'EMPTY', 'READY', 'WORKING', 'ALL_CLAIMED', 'DONE', 'FAILED') DEFAULT 'EMPTY' NOT NULL,

    total_job_count         INTEGER     NOT NULL DEFAULT 0,
    semaphored_job_count    INTEGER     NOT NULL DEFAULT 0,
    ready_job_count         INTEGER     NOT NULL DEFAULT 0,
    done_job_count          INTEGER     NOT NULL DEFAULT 0,
    failed_job_count        INTEGER     NOT NULL DEFAULT 0,
165

Leo Gordon's avatar
Leo Gordon committed
166 167 168 169 170 171 172 173 174 175 176
    num_running_workers     INTEGER     NOT NULL DEFAULT 0,

    behaviour               ENUM('STATIC', 'DYNAMIC') NOT NULL DEFAULT 'STATIC',
    input_capacity          INTEGER     NOT NULL DEFAULT 4,
    output_capacity         INTEGER     NOT NULL DEFAULT 4,

    avg_msec_per_job        INTEGER              DEFAULT NULL,
    avg_input_msec_per_job  INTEGER              DEFAULT NULL,
    avg_run_msec_per_job    INTEGER              DEFAULT NULL,
    avg_output_msec_per_job INTEGER              DEFAULT NULL,

177
    when_updated            TIMESTAMP                    NULL,  -- mysql's special for "TIMESTAMP DEFAULT NULL"
Leo Gordon's avatar
Leo Gordon committed
178 179 180
    sync_lock               SMALLINT    NOT NULL DEFAULT 0,

    PRIMARY KEY (analysis_id)
Leo Gordon's avatar
Leo Gordon committed
181
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Jessica Severin's avatar
Jessica Severin committed
182 183


184 185 186 187 188 189
/**
@table  dataflow_rule

@colour #C70C09

@desc Extension of simple_rule design except that goal(to) is now in extended URL format e.g.
Leo Gordon's avatar
Leo Gordon committed
190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207
        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 the job, analysis, dataflow_rule,
        and worker tables so that the following join works on the same database 
            WHERE analysis.analysis_id = dataflow_rule.from_analysis_id 
            AND   analysis.analysis_id = job.analysis_id
            AND   analysis.analysis_id = worker.analysis_id
        These are the rules used to create entries in the job table where the
        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

@column dataflow_rule_id        internal ID
@column from_analysis_id        foreign key to analysis table analysis_id
@column branch_code             branch_code of the fan
@column funnel_dataflow_rule_id dataflow_rule_id of the semaphored funnel (is NULL by default, which means dataflow is not semaphored)
@column to_analysis_url         foreign key to net distributed analysis logic_name reference
@column input_id_template       a template for generating a new input_id (not necessarily a hashref) in this dataflow; if undefined is kept original
208
*/
Jessica Severin's avatar
Jessica Severin committed
209

210
CREATE TABLE dataflow_rule (
Leo Gordon's avatar
Leo Gordon committed
211 212 213 214 215
    dataflow_rule_id        INTEGER     NOT NULL PRIMARY KEY AUTO_INCREMENT,
    from_analysis_id        INTEGER     NOT NULL,
    branch_code             INTEGER     NOT NULL DEFAULT 1,
    funnel_dataflow_rule_id INTEGER              DEFAULT NULL,
    to_analysis_url         VARCHAR(255) NOT NULL DEFAULT '',
216
    input_id_template       MEDIUMTEXT            DEFAULT NULL,
Leo Gordon's avatar
Leo Gordon committed
217

Leo Gordon's avatar
Leo Gordon committed
218
    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
219
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Jessica Severin's avatar
Jessica Severin committed
220 221


222 223
/**
@table  analysis_ctrl_rule
224

225 226 227 228 229 230 231 232 233 234 235 236 237 238
@colour #C70C09

@desc   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 to 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.

@column condition_analysis_url foreign key to net distributed analysis reference
@column ctrled_analysis_id     foreign key to analysis table analysis_id
*/

CREATE TABLE analysis_ctrl_rule (
Leo Gordon's avatar
Leo Gordon committed
239 240
    condition_analysis_url  VARCHAR(255) NOT NULL DEFAULT '',
    ctrled_analysis_id      INTEGER     NOT NULL,
241 242

    UNIQUE  KEY (condition_analysis_url, ctrled_analysis_id)
Leo Gordon's avatar
Leo Gordon committed
243
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
244 245


246 247 248 249 250 251 252 253 254 255 256 257 258 259 260
/**
@header Resources
@colour #FF7504
*/

/**
@table  resource_class

@colour #FF7504

@desc   Maps between resource_class numeric IDs and unique names.

@column resource_class_id   unique ID of the ResourceClass
@column name                unique name of the ResourceClass
*/
261

262
CREATE TABLE resource_class (
263 264
    resource_class_id       INTEGER      NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name                    VARCHAR(255) NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
265

266
    UNIQUE  KEY(name)
Leo Gordon's avatar
Leo Gordon committed
267
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Leo Gordon's avatar
Leo Gordon committed
268 269


270 271 272 273 274 275 276 277 278
/**
@table  resource_description

@colour #FF7504

@desc   Maps (ResourceClass, MeadowType) pair to Meadow-specific resource lines.

@column resource_class_id   foreign-keyed to the ResourceClass entry
@column meadow_type         if the Worker is about to be executed on the given Meadow...
279 280
@column submission_cmd_args ... these are the resource arguments (queue, memory,...) to give to the submission command
@column worker_cmd_args     ... and these are the arguments that are given to the worker command being submitted
281 282
*/

283
CREATE TABLE resource_description (
284 285
    resource_class_id       INTEGER      NOT NULL,
    meadow_type             VARCHAR(255) NOT NULL,
286 287
    submission_cmd_args     VARCHAR(255) NOT NULL DEFAULT '',
    worker_cmd_args         VARCHAR(255) NOT NULL DEFAULT '',
288 289 290 291 292

    PRIMARY KEY(resource_class_id, meadow_type)
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;


293 294 295 296
/**
@header Job-related
@colour #1D73DA
*/
Jessica Severin's avatar
Jessica Severin committed
297

298 299
/**
@table  job
300

301
@colour #1D73DA
302

303 304 305 306
@desc The job is the heart of this system.  It is the kiosk or blackboard
    where workers find things to do and then post work for other works to do.
    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.
307

308
@column job_id                  autoincrement id
309
@column prev_job_id             previous job which created this one
310 311
@column analysis_id             the analysis_id needed to accomplish this job.
@column input_id                input data passed into Analysis:RunnableDB to control the work
312 313
@column param_id_stack          a CSV of job_ids whose input_ids contribute to the stack of local variables for the job
@column accu_id_stack           a CSV of job_ids whose accu's contribute to the stack of local variables for the job
314
@column role_id                 links to the Role that claimed this job (NULL means it has never been claimed)
315 316
@column status                  state the job is in
@column retry_count             number times job had to be reset when worker failed to run it
317
@column when_completed          when the job was completed
318 319 320 321 322
@column runtime_msec            how long did it take to execute the job (or until the moment it failed)
@column query_count             how many SQL queries were run during this job
@column 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".
@column semaphored_job_id       the job_id of job S that is waiting for this job to decrease S's semaphore_count. Default=NULL means "I'm not blocking anything by default".
*/
323

324
CREATE TABLE job (
Leo Gordon's avatar
Leo Gordon committed
325 326 327 328
    job_id                  INTEGER     NOT NULL PRIMARY KEY AUTO_INCREMENT,
    prev_job_id             INTEGER              DEFAULT NULL,  -- the job that created this one using a dataflow rule
    analysis_id             INTEGER     NOT NULL,
    input_id                CHAR(255)   NOT NULL,
329 330
    param_id_stack          CHAR(64)    NOT NULL DEFAULT '',
    accu_id_stack           CHAR(64)    NOT NULL DEFAULT '',
331
    role_id                 INTEGER              DEFAULT NULL,
Leo Gordon's avatar
Leo Gordon committed
332 333
    status                  ENUM('SEMAPHORED','READY','CLAIMED','COMPILATION','PRE_CLEANUP','FETCH_INPUT','RUN','WRITE_OUTPUT','POST_CLEANUP','DONE','FAILED','PASSED_ON') DEFAULT 'READY' NOT NULL,
    retry_count             INTEGER     NOT NULL DEFAULT 0,
334
    when_completed          TIMESTAMP                    NULL,  -- mysql's special for "TIMESTAMP DEFAULT NULL"
Leo Gordon's avatar
Leo Gordon committed
335 336 337 338 339 340
    runtime_msec            INTEGER              DEFAULT NULL,
    query_count             INTEGER              DEFAULT NULL,

    semaphore_count         INTEGER     NOT NULL DEFAULT 0,
    semaphored_job_id       INTEGER              DEFAULT NULL,

341 342
    UNIQUE  KEY input_id_stacks_analysis    (input_id, param_id_stack, accu_id_stack, analysis_id), -- to avoid repeating tasks
            KEY analysis_status_retry       (analysis_id, status, retry_count),                     -- for claiming jobs
343
            KEY role_status                 (role_id, status)                                       -- for fetching and releasing claimed jobs
344
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
345

346 347 348 349 350 351 352 353 354 355 356 357 358

/**
@table  job_file

@colour #1D73DA

@desc   For testing/debugging purposes both STDOUT and STDERR streams of each Job
        can be redirected into a separate log file.
        This table holds filesystem paths to one or both of those files.
        There is max one entry per job_id and retry.

@column job_id             foreign key
@column retry              copy of retry_count of job as it was run
359
@column role_id            links to the Role that claimed this job
360 361 362 363 364
@column stdout_file        path to the job's STDOUT log
@column stderr_file        path to the job's STDERR log
*/

CREATE TABLE job_file (
Leo Gordon's avatar
Leo Gordon committed
365 366
    job_id                  INTEGER     NOT NULL,
    retry                   INTEGER     NOT NULL,
367
    role_id                 INTEGER     NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
368 369
    stdout_file             VARCHAR(255),
    stderr_file             VARCHAR(255),
370 371

    PRIMARY KEY job_retry   (job_id, retry),
372
            KEY role        (role_id)
Leo Gordon's avatar
Leo Gordon committed
373
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
Jessica Severin's avatar
Jessica Severin committed
374

Leo Gordon's avatar
Leo Gordon committed
375

376 377 378 379 380 381 382 383 384 385 386 387 388 389 390
/**
@table  accu

@colour #1D73DA

@desc   Accumulator for funneled dataflow.

@column sending_job_id     semaphoring job in the "box"
@column receiving_job_id   semaphored job outside the "box"
@column struct_name        name of the structured parameter
@column key_signature      locates the part of the structured parameter
@column value              value of the part
*/

CREATE TABLE accu (
Leo Gordon's avatar
Leo Gordon committed
391 392 393 394
    sending_job_id          INTEGER,
    receiving_job_id        INTEGER     NOT NULL,
    struct_name             VARCHAR(255) NOT NULL,
    key_signature           VARCHAR(255) NOT NULL,
395
    value                   MEDIUMTEXT,
Leo Gordon's avatar
Leo Gordon committed
396 397 398

    KEY accu_sending_idx (sending_job_id),
    KEY accu_receiving_idx (receiving_job_id)
399 400 401
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;


402 403 404 405 406 407 408 409 410 411 412 413 414 415 416
/**
@table  analysis_data

@colour #1D73DA

@desc   A generic blob-storage hash.
        Currently the only legitimate use of this table is "overflow" of job.input_ids:
        when they grow longer than 254 characters the real data is stored in analysis_data instead,
        and the input_id contains the corresponding analysis_data_id.

@column analysis_data_id    primary id
@column data                text blob which holds the data
*/

CREATE TABLE analysis_data (
Leo Gordon's avatar
Leo Gordon committed
417
    analysis_data_id        INTEGER     NOT NULL PRIMARY KEY AUTO_INCREMENT,
418
    data                    MEDIUMTEXT,
419 420 421 422 423 424

            KEY (data(100))
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;


/**
425
@header execution tables
426 427 428 429 430 431 432 433 434
@colour #24DA06
*/

/**
@table  worker

@colour #24DA06

@desc Entries of this table correspond to Worker objects of the API.
Leo Gordon's avatar
Leo Gordon committed
435 436 437 438 439 440
        Workers are created by inserting into this table
        so that there is only one instance of a Worker object in the database.
        As Workers live and do work, they update this table, and when they die they update again.

@column worker_id           unique ID of the Worker
@column meadow_type         type of the Meadow it is running on
441 442 443
@column meadow_name         name of the Meadow it is running on (for meadow_type=='LOCAL' it is the same as meadow_host)
@column meadow_host         execution host name
@column meadow_user         scheduling/execution user name (within the Meadow)
Leo Gordon's avatar
Leo Gordon committed
444 445 446 447
@column process_id          identifies the Worker process on the Meadow (for 'LOCAL' is the OS PID)
@column resource_class_id   links to Worker's resource class
@column work_done           how many jobs the Worker has completed successfully
@column status              current status of the Worker
448 449 450 451
@column when_born           when the Worker process was started
@column when_checked_in     when the Worker last checked into the database
@column when_seen           when the Worker was last seen by the Meadow
@column when_died           if defined, when the Worker died (or its premature death was first detected by GC)
Leo Gordon's avatar
Leo Gordon committed
452 453
@column cause_of_death      if defined, why did the Worker exit (or why it was killed)
@column log_dir             if defined, a filesystem directory where this Worker's output is logged
454 455 456
*/

CREATE TABLE worker (
Leo Gordon's avatar
Leo Gordon committed
457
    worker_id               INTEGER     NOT NULL PRIMARY KEY AUTO_INCREMENT,
458 459
    meadow_type             VARCHAR(255) NOT NULL,
    meadow_name             VARCHAR(255) NOT NULL,
460 461
    meadow_host             VARCHAR(255) NOT NULL,
    meadow_user             VARCHAR(255)         DEFAULT NULL,
462
    process_id              VARCHAR(255) NOT NULL,
Leo Gordon's avatar
Leo Gordon committed
463 464 465 466
    resource_class_id       INTEGER              DEFAULT NULL,

    work_done               INTEGER     NOT NULL DEFAULT 0,
    status                  ENUM('SPECIALIZATION','COMPILATION','READY','PRE_CLEANUP','FETCH_INPUT','RUN','WRITE_OUTPUT','POST_CLEANUP','DEAD') DEFAULT 'READY' NOT NULL,
467 468 469 470
    when_born               TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    when_checked_in         TIMESTAMP                    NULL,
    when_seen               TIMESTAMP                    NULL,
    when_died               TIMESTAMP                    NULL,  -- mysql's special for "TIMESTAMP DEFAULT NULL"
471
    cause_of_death          ENUM('NO_ROLE', 'NO_WORK', 'JOB_LIMIT', 'HIVE_OVERLOAD', 'LIFESPAN', 'CONTAMINATED', 'RELOCATED', 'KILLED_BY_USER', 'MEMLIMIT', 'RUNLIMIT', 'SEE_MSG', 'UNKNOWN') DEFAULT NULL,
Leo Gordon's avatar
Leo Gordon committed
472
    log_dir                 VARCHAR(255)         DEFAULT NULL,
473

474
    KEY meadow_process (meadow_type, meadow_name, process_id)   
475 476 477
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;


478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509
/**
@table  role

@colour #24DA06

@desc Entries of this table correspond to Role objects of the API.
        When a Worker specializes, it acquires a Role,
        which is a temporary link between the Worker and a resource-compatible Analysis.

@column role_id             unique ID of the Role
@column worker_id           the specialized Worker
@column analysis_id         the Analysis into which the Worker specialized
@column when_started        when this Role started
@column when_finished       when this Role finished. NULL may either indicate it is still running or was killed by an external force.
@column attempted_jobs      counter of the number of attempts
@column done_jobs           counter of the number of successful attempts
*/

CREATE TABLE role (
    role_id                 INTEGER     NOT NULL PRIMARY KEY AUTO_INCREMENT,
    worker_id               INTEGER     NOT NULL,
    analysis_id             INTEGER     NOT NULL,
    when_started            TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    when_finished           TIMESTAMP                    NULL,  -- mysql's special for "TIMESTAMP DEFAULT NULL"
    attempted_jobs          INTEGER     NOT NULL DEFAULT 0,
    done_jobs               INTEGER     NOT NULL DEFAULT 0,

    KEY worker   (worker_id),
    KEY analysis (analysis_id)
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;


510 511 512 513 514
/**
@header Logging and monitoring
@colour #F4D20C
*/

515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536
/**
@table  worker_resource_usage

@colour #F4D20C

@desc   A table with post-mortem resource usage statistics of a Worker.

@column          worker_id  links to the worker table
@column        exit_status  meadow-dependent, in case of LSF it's usually 'done' (normal) or 'exit' (abnormal)
@column           mem_megs  how much memory the Worker process used
@column          swap_megs  how much swap the Worker process used
@column        pending_sec  time spent by the process in the queue before it became a Worker
@column            cpu_sec  cpu time used by the Worker process
@column       lifespan_sec  walltime used by the Worker process
@column   exception_status  meadow-specific flags, in case of LSF it can be 'underrun', 'overrun' or 'idle'
*/

CREATE TABLE worker_resource_usage (
    worker_id               INTEGER         NOT NULL,
    exit_status             VARCHAR(255)    DEFAULT NULL,
    mem_megs                FLOAT           DEFAULT NULL,
    swap_megs               FLOAT           DEFAULT NULL,
537
    pending_sec             FLOAT           DEFAULT NULL,
538
    cpu_sec                 FLOAT           DEFAULT NULL,
539
    lifespan_sec            FLOAT           DEFAULT NULL,
540 541 542 543 544 545
    exception_status        VARCHAR(255)    DEFAULT NULL,

    PRIMARY KEY (worker_id)
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;


546 547 548
/**
@table  log_message

549
@colour #F4D20C
550 551 552 553 554 555 556 557

@desc   When a Job or a job-less Worker (job_id=NULL) throws a "die" message
        for any reason, the message is recorded in this table.
        It may or may not indicate that the job was unsuccessful via is_error flag.
        Also $self->warning("...") messages are recorded with is_error=0.

@column log_message_id  an autoincremented primary id of the message
@column         job_id  the id of the job that threw the message (or NULL if it was outside of a message)
558
@column        role_id  the 'current' role
559
@column      worker_id  the 'current' worker
560
@column    when_logged  when the message was thrown
561 562 563 564 565 566 567
@column          retry  retry_count of the job when the message was thrown (or NULL if no job)
@column         status  of the job or worker when the message was thrown
@column            msg  string that contains the message
@column       is_error  binary flag
*/

CREATE TABLE log_message (
Leo Gordon's avatar
Leo Gordon committed
568 569
    log_message_id          INTEGER     NOT NULL PRIMARY KEY AUTO_INCREMENT,
    job_id                  INTEGER              DEFAULT NULL,
570
    role_id                 INTEGER              DEFAULT NULL,
571
    worker_id               INTEGER              DEFAULT NULL,
572
    when_logged             TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
Leo Gordon's avatar
Leo Gordon committed
573
    retry                   INTEGER              DEFAULT NULL,
574
    status                  ENUM('UNKNOWN','SPECIALIZATION','COMPILATION','CLAIMED','READY','PRE_CLEANUP','FETCH_INPUT','RUN','WRITE_OUTPUT','POST_CLEANUP','PASSED_ON') DEFAULT 'UNKNOWN',
575
    msg                     MEDIUMTEXT,
Leo Gordon's avatar
Leo Gordon committed
576 577
    is_error                SMALLINT,

578 579 580 581 582 583 584 585 586 587 588 589
            KEY worker_id     (worker_id),
            KEY job_id        (job_id)
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;


/**
@table  analysis_stats_monitor

@colour #F4D20C

@desc   A regular timestamped snapshot of the analysis_stats table.

590
@column when_logged             when this snapshot was taken
591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613

@column analysis_id             foreign-keyed to the corresponding analysis_base entry
@column batch_size              how many jobs are claimed in one claiming operation before Worker starts executing them
@column hive_capacity           a reciprocal limiter on the number of Workers running at the same time (dependent on Workers of other Analyses)
@column status                  cached state of the Analysis

@column total_job_count         total number of Jobs of this Analysis
@column semaphored_job_count    number of Jobs of this Analysis that are in SEMAPHORED state
@column ready_job_count         number of Jobs of this Analysis that are in READY state
@column done_job_count          number of Jobs of this Analysis that are in DONE state
@column failed_job_count        number of Jobs of this Analysis that are in FAILED state

@column num_running_workers     number of running Workers of this Analysis

@column behaviour               whether hive_capacity is set or is dynamically calculated based on timers
@column input_capacity          used to compute hive_capacity in DYNAMIC mode
@column output_capacity         used to compute hive_capacity in DYNAMIC mode

@column avg_msec_per_job        weighted average used to compute DYNAMIC hive_capacity
@column avg_input_msec_per_job  weighted average used to compute DYNAMIC hive_capacity
@column avg_run_msec_per_job    weighted average used to compute DYNAMIC hive_capacity
@column avg_output_msec_per_job weighted average used to compute DYNAMIC hive_capacity

614
@column when_updated            when this entry was last updated
615 616 617
@column sync_lock               a binary lock flag to prevent simultaneous updates
*/

618
CREATE TABLE analysis_stats_monitor (
619
    when_logged             TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
620

Leo Gordon's avatar
Leo Gordon committed
621 622 623 624
    analysis_id             INTEGER     NOT NULL,
    batch_size              INTEGER     NOT NULL DEFAULT 1,
    hive_capacity           INTEGER              DEFAULT NULL,
    status                  ENUM('BLOCKED', 'LOADING', 'SYNCHING', 'EMPTY', 'READY', 'WORKING', 'ALL_CLAIMED', 'DONE', 'FAILED') DEFAULT 'EMPTY' NOT NULL,
625

Leo Gordon's avatar
Leo Gordon committed
626 627 628 629 630
    total_job_count         INTEGER     NOT NULL DEFAULT 0,
    semaphored_job_count    INTEGER     NOT NULL DEFAULT 0,
    ready_job_count         INTEGER     NOT NULL DEFAULT 0,
    done_job_count          INTEGER     NOT NULL DEFAULT 0,
    failed_job_count        INTEGER     NOT NULL DEFAULT 0,
631

Leo Gordon's avatar
Leo Gordon committed
632
    num_running_workers     INTEGER     NOT NULL DEFAULT 0,
633

Leo Gordon's avatar
Leo Gordon committed
634 635 636
    behaviour               ENUM('STATIC', 'DYNAMIC') NOT NULL DEFAULT 'STATIC',
    input_capacity          INTEGER     NOT NULL DEFAULT 4,
    output_capacity         INTEGER     NOT NULL DEFAULT 4,
637

Leo Gordon's avatar
Leo Gordon committed
638 639 640 641
    avg_msec_per_job        INTEGER              DEFAULT NULL,
    avg_input_msec_per_job  INTEGER              DEFAULT NULL,
    avg_run_msec_per_job    INTEGER              DEFAULT NULL,
    avg_output_msec_per_job INTEGER              DEFAULT NULL,
642

643
    when_updated            TIMESTAMP                    NULL,  -- mysql's special for "TIMESTAMP DEFAULT NULL"
Leo Gordon's avatar
Leo Gordon committed
644
    sync_lock               SMALLINT    NOT NULL DEFAULT 0
Leo Gordon's avatar
Leo Gordon committed
645

Leo Gordon's avatar
Leo Gordon committed
646
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;
647