tables.pgsql 24.6 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
/*
    This is PostgreSQL version of EnsEMBL Hive database schema file.

    It has been annotated with @-tags.
    The following command is used to create HTML documentation:
        perl $ENSEMBL_CVS_ROOT_DIR/ensembl/misc-scripts/sql2html.pl -i $ENSEMBL_CVS_ROOT_DIR/ensembl-hive/sql/tables.pgsql \
             -o $ENSEMBL_CVS_ROOT_DIR/ensembl-hive/docs/hive_schema.html -d Hive -sort_headers 0 -sort_tables 0

    Adding the following line into the header of the previous output will make it look prettier (valid in rel.71):
        <link rel="stylesheet" type="text/css" media="all" href="http://static.ensembl.org/minified/f75db6b3a877e4e04329aa1283dec34e.css" />
*/


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

/**
@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
*/

CREATE TABLE analysis_base (
Leo Gordon's avatar
Leo Gordon committed
42
    analysis_id             SERIAL PRIMARY KEY,
43
44
45
    logic_name              VARCHAR(40) NOT NULL,
    module                  VARCHAR(255),
    parameters              TEXT,
Leo Gordon's avatar
Leo Gordon committed
46
47
48
49
50
51
52
53
    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,
    meadow_type             VARCHAR(40)          DEFAULT NULL,
    analysis_capacity       INTEGER              DEFAULT NULL,

54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
    UNIQUE  (logic_name)
);


/**
@table  analysis_stats

@colour #C70C09

@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

@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 num_required_workers    extra number of Workers of this Analysis needed to execute all READY jobs

@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

@column last_update             when this entry was last updated
@column sync_lock               a binary lock flag to prevent simultaneous updates
*/

CREATE TYPE analysis_status AS ENUM ('BLOCKED', 'LOADING', 'SYNCHING', 'EMPTY', 'READY', 'WORKING', 'ALL_CLAIMED', 'DONE', 'FAILED');
CREATE TYPE analysis_behaviour AS ENUM ('STATIC', 'DYNAMIC');
CREATE TABLE analysis_stats (
Leo Gordon's avatar
Leo Gordon committed
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
    analysis_id             INTEGER     NOT NULL,
    batch_size              INTEGER     NOT NULL DEFAULT 1,
    hive_capacity           INTEGER              DEFAULT NULL,
    status                  analysis_status NOT NULL DEFAULT 'EMPTY',

    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,
    num_running_workers     INTEGER     NOT NULL DEFAULT 0,
    num_required_workers    INTEGER     NOT NULL DEFAULT 0,

    behaviour               analysis_behaviour 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,

    last_update             TIMESTAMP            DEFAULT NULL,
    sync_lock               SMALLINT    NOT NULL DEFAULT 0,

    PRIMARY KEY (analysis_id)
124
125
126
127
128
129
130
131
132
133
134
135
136
);


/**
@table  dataflow_rule

@colour #C70C09

@desc 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 the job, analysis, dataflow_rule,
        and worker tables so that the following join works on the same database 
Leo Gordon's avatar
Leo Gordon committed
137
138
139
            WHERE analysis.analysis_id = dataflow_rule.from_analysis_id 
            AND   analysis.analysis_id = job.analysis_id
            AND   analysis.analysis_id = worker.analysis_id
140
141
142
143
144
145
146
147
148
149
150
151
152
153
        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
*/

CREATE TABLE dataflow_rule (
Leo Gordon's avatar
Leo Gordon committed
154
155
156
157
158
159
160
    dataflow_rule_id        SERIAL PRIMARY KEY,
    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 '',
    input_id_template       TEXT                 DEFAULT NULL,

161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
    UNIQUE (from_analysis_id, branch_code, funnel_dataflow_rule_id, to_analysis_url, input_id_template)
);


/**
@table  analysis_ctrl_rule

@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
182
183
    condition_analysis_url  VARCHAR(255) NOT NULL DEFAULT '',
    ctrled_analysis_id      INTEGER     NOT NULL,
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205

    UNIQUE (condition_analysis_url, ctrled_analysis_id)
);


/**
@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
*/

CREATE TABLE resource_class (
Leo Gordon's avatar
Leo Gordon committed
206
207
    resource_class_id       SERIAL PRIMARY KEY,
    name                    VARCHAR(40) NOT NULL,
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225

    UNIQUE  (name)
);


/**
@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...
@column parameters          ... the following resource line should be given to it.
*/

CREATE TABLE resource_description (
Leo Gordon's avatar
Leo Gordon committed
226
227
228
    resource_class_id       INTEGER     NOT NULL,
    meadow_type             VARCHAR(40) NOT NULL,
    parameters              VARCHAR(255) NOT NULL DEFAULT '',
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255

    PRIMARY KEY(resource_class_id, meadow_type)
);


/**
@header Job-related
@colour #1D73DA
*/

/**
@table  job

@colour #1D73DA

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

@column job_id                  autoincrement id
@column prev_job_id             previous job which created this one (and passed input_id)
@column analysis_id             the analysis_id needed to accomplish this job.
@column input_id                input data passed into Analysis:RunnableDB to control the work
@column worker_id               link to worker table to define which worker claimed this job
@column status                  state the job is in
@column retry_count             number times job had to be reset when worker failed to run it
Leo Gordon's avatar
Leo Gordon committed
256
@column completed               when the job was completed
257
258
259
260
261
262
263
264
265
266
@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".
*/

-- union enum status for job and worker
CREATE TYPE jw_status AS ENUM ('UNKNOWN','SPECIALIZATION','COMPILATION','SEMAPHORED','READY','CLAIMED','PRE_CLEANUP','FETCH_INPUT','RUN','WRITE_OUTPUT','POST_CLEANUP','DONE','FAILED','PASSED_ON','DEAD');

CREATE TABLE job (
Leo Gordon's avatar
Leo Gordon committed
267
268
269
270
271
272
273
274
275
276
277
278
279
280
    job_id                  SERIAL PRIMARY KEY,
    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,
    worker_id               INTEGER              DEFAULT NULL,
    status                  jw_status   NOT NULL DEFAULT 'READY',
    retry_count             INTEGER     NOT NULL DEFAULT 0,
    completed               TIMESTAMP            DEFAULT NULL,
    runtime_msec            INTEGER              DEFAULT NULL,
    query_count             INTEGER              DEFAULT NULL,

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

281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
    UNIQUE (input_id, analysis_id)                      -- to avoid repeating tasks
);
CREATE INDEX ON job (analysis_id, status, retry_count); -- for claiming jobs
CREATE INDEX ON job (worker_id, status);                -- for fetching and releasing claimed jobs


/**
@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 worker_id          link to worker table to define which worker claimed this job
@column retry              copy of retry_count of job as it was run
@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
305
306
307
    job_id                  INTEGER     NOT NULL,
    retry                   INTEGER     NOT NULL,
    worker_id               INTEGER     NOT NULL,
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
    stdout_file             VARCHAR(255),
    stderr_file             VARCHAR(255),

    PRIMARY KEY (job_id, retry)
);
CREATE INDEX ON job_file (worker_id);


/**
@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 (
    sending_job_id          INTEGER,
Leo Gordon's avatar
Leo Gordon committed
332
    receiving_job_id        INTEGER     NOT NULL,
333
334
    struct_name             VARCHAR(255) NOT NULL,
    key_signature           VARCHAR(255) NOT NULL,
335
    value                   TEXT
336
);
Leo Gordon's avatar
Leo Gordon committed
337
338
CREATE INDEX ON accu (sending_job_id);
CREATE INDEX ON accu (receiving_job_id);
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355


/**
@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
356
357
    analysis_data_id        SERIAL PRIMARY KEY,
    data                    TEXT
358
359
360
361
);
CREATE INDEX ON analysis_data (data);


362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
/**
@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(80) NOT NULL PRIMARY KEY,
    meta_value              TEXT

);


381
382
383
384
385
386
387
388
389
/**
@table  meta

@colour #000000

@desc This table comes from the Ensembl core schema.
        It is created here with the 'IF NOT EXISTS' option to avoid a potential clash
         if we are dealing with core-hive hybrid that is created in the wrong order.
        At the moment meta table is used
Leo Gordon's avatar
Leo Gordon committed
390
            (1) for compatibility with the Core API ('schema_version'),
391
            (2) to keep pipeline-wide parameters.
Leo Gordon's avatar
Leo Gordon committed
392
393
394
395
396

@column meta_id         auto-incrementing primary key, not really used per se
@column species_id      always 1, kept for compatibility with the Core API
@column meta_key        the KEY of KEY-VALUE pairs
@column meta_value      the VALUE of KEY-VALUE pairs
397
398
399
*/

CREATE TABLE IF NOT EXISTS meta (
Leo Gordon's avatar
Leo Gordon committed
400
401
402
403
    meta_id                 SERIAL PRIMARY KEY,
    species_id              INTEGER              DEFAULT 1,
    meta_key                VARCHAR(40) NOT NULL,
    meta_value              TEXT        NOT NULL,
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424

    UNIQUE (species_id, meta_key, meta_value)
);
CREATE INDEX ON meta (species_id, meta_value);


/**
@header worker table
@colour #24DA06
*/

/**
@table  worker

@colour #24DA06

@desc Entries of this table correspond to Worker objects of the API.
        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.

Leo Gordon's avatar
Leo Gordon committed
425
426
427
428
429
430
431
432
433
434
435
436
437
438
@column worker_id           unique ID of the Worker
@column meadow_type         type of the Meadow it is running on
@column meadow_name         name of the Meadow it is running on (for 'LOCAL' type is the same as host)
@column host                execution host name
@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 analysis_id         Analysis the Worker is specified into
@column work_done           how many jobs the Worker has completed successfully
@column status              current status of the Worker
@column born                when the Worker process was started
@column last_check_in       when the Worker last checked into the database
@column died                if defined, when the Worker died (or its premature death was first detected by GC)
@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
439
440
441
442
*/

CREATE TYPE worker_cod AS ENUM ('NO_ROLE', 'NO_WORK', 'JOB_LIMIT', 'HIVE_OVERLOAD', 'LIFESPAN', 'CONTAMINATED', 'KILLED_BY_USER', 'MEMLIMIT', 'RUNLIMIT', 'SEE_MSG', 'UNKNOWN');
CREATE TABLE worker (
Leo Gordon's avatar
Leo Gordon committed
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
    worker_id               SERIAL PRIMARY KEY,
    meadow_type             VARCHAR(40) NOT NULL,
    meadow_name             VARCHAR(40) NOT NULL,
    host                    VARCHAR(40) NOT NULL,
    process_id              VARCHAR(40) NOT NULL,
    resource_class_id       INTEGER              DEFAULT NULL,

    analysis_id             INTEGER              DEFAULT NULL,
    work_done               INTEGER     NOT NULL DEFAULT 0,
    status                  jw_status   NOT NULL DEFAULT 'READY',
    born                    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_check_in           TIMESTAMP   NOT NULL,
    died                    TIMESTAMP            DEFAULT NULL,
    cause_of_death          worker_cod           DEFAULT NULL,
    log_dir                 VARCHAR(255)         DEFAULT NULL
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
);
CREATE INDEX ON worker (analysis_id, status);


/**
@header Logging and monitoring
@colour #F4D20C
*/

/**
@table  log_message

@colour #08DAD8

@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)
@column      worker_id  the 'current' worker
@column           time  when the message was thrown
@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
488
489
    log_message_id          SERIAL PRIMARY KEY,
    job_id                  INTEGER              DEFAULT NULL,
490
    worker_id               INTEGER              DEFAULT NULL,
Leo Gordon's avatar
Leo Gordon committed
491
492
493
494
495
496
    time                    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    retry                   INTEGER              DEFAULT NULL,
    status                  jw_status            DEFAULT 'UNKNOWN',
    msg                     TEXT,
    is_error                SMALLINT

497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
);
CREATE INDEX ON log_message (worker_id);
CREATE INDEX ON log_message (job_id);


/**
@table  analysis_stats_monitor

@colour #F4D20C

@desc   A regular timestamped snapshot of the analysis_stats table.

@column time                    when this snapshot was taken

@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 num_required_workers    extra number of Workers of this Analysis needed to execute all READY jobs

@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

@column last_update             when this entry was last updated
@column sync_lock               a binary lock flag to prevent simultaneous updates
*/

CREATE TABLE analysis_stats_monitor (
Leo Gordon's avatar
Leo Gordon committed
539
    time                    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
540

Leo Gordon's avatar
Leo Gordon committed
541
542
543
544
    analysis_id             INTEGER     NOT NULL,
    batch_size              INTEGER     NOT NULL DEFAULT 1,
    hive_capacity           INTEGER              DEFAULT NULL,
    status                  analysis_status NOT NULL DEFAULT 'EMPTY',
545

Leo Gordon's avatar
Leo Gordon committed
546
547
548
549
550
551
552
    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,
    num_running_workers     INTEGER     NOT NULL DEFAULT 0,
    num_required_workers    INTEGER     NOT NULL DEFAULT 0,
553

Leo Gordon's avatar
Leo Gordon committed
554
555
556
    behaviour               analysis_behaviour NOT NULL DEFAULT 'STATIC',
    input_capacity          INTEGER     NOT NULL DEFAULT 4,
    output_capacity         INTEGER     NOT NULL DEFAULT 4,
557

Leo Gordon's avatar
Leo Gordon committed
558
559
560
561
    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,
562

Leo Gordon's avatar
Leo Gordon committed
563
564
    last_update             TIMESTAMP            DEFAULT NULL,
    sync_lock               SMALLINT    NOT NULL DEFAULT 0
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584

);


/**
@table  monitor

@colour #F4D20C

@desc   A regular collated snapshot of the Worker table.

@column time            when this snapshot was taken
@column workers         number of running workers
@column throughput      average numb of completed Jobs per sec. of the hive (this number is calculated using running workers only)
@column per_worker      average numb of completed Jobs per sec. per Worker (this number is calculated using running workers only)
@column analysis        a comma-separated list of analyses running at the time of snapshot

*/

CREATE TABLE monitor (
Leo Gordon's avatar
Leo Gordon committed
585
586
587
588
589
    time                    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    workers                 INTEGER     NOT NULL DEFAULT 0,
    throughput              FLOAT                DEFAULT NULL,
    per_worker              FLOAT                DEFAULT NULL,
    analysis                VARCHAR(255)         DEFAULT NULL   -- not just one, but a list of logic_names
590
591
592
593

);