procedures.sqlite 6.31 KB
Newer Older
1 2 3 4 5 6 7 8 9
/*

DESCRIPTION

    Some stored functions, views and procedures used in eHive


LICENSE

10
    Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
nwillhoft's avatar
nwillhoft committed
11
    Copyright [2016-2021] EMBL-European Bioinformatics Institute
12 13 14 15 16 17 18 19 20 21

    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.

22 23 24 25
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

26
*/
27 28


29
-- show hive progress for analyses (turned into a view to give extra flexibility) ----------------
30 31 32 33 34
--
-- Thanks to Greg Jordan for the idea and the original version
--
-- Usage:
--       select * from progress;                                         # the whole table (may take ages to generate, depending on the size of your pipeline)
Matthieu Muffato's avatar
typo  
Matthieu Muffato committed
35
--       select * from progress where analysis_name_and_id like 'family_blast%';   # only show family_blast-related analyses
36 37
--       select * from progress where retry_count>1;                     # only show jobs that have been tried more than once

38
DROP VIEW IF EXISTS progress;
39
CREATE VIEW IF NOT EXISTS progress AS
40
    SELECT a.logic_name || '(' || a.analysis_id || ')' analysis_name_and_id,
41
        MIN(rc.name) resource_class,
42 43 44
        j.status,
        j.retry_count,
        CASE WHEN j.status IS NULL THEN 0 ELSE count(*) END cnt,
45 46 47 48
        MIN(job_id) example_job_id
    FROM        analysis_base a
    LEFT JOIN   job j USING (analysis_id)
    LEFT JOIN   resource_class rc ON (a.resource_class_id=rc.resource_class_id)
49 50 51 52 53 54 55 56 57 58 59
    GROUP BY a.analysis_id, j.status, j.retry_count
    ORDER BY a.analysis_id, j.status;


-- a convenient view that also incorporates (otherwise redundant) analysis_id and logic_name ------------
--
-- Usage:
--       select * from msg;
--       select * from msg where analysis_id=18;
--       select * from msg where logic_name like 'family_blast%';

60
DROP VIEW IF EXISTS msg;
61 62
CREATE VIEW IF NOT EXISTS msg AS
    SELECT a.analysis_id, a.logic_name, m.*
63
    FROM log_message m
64 65
    LEFT JOIN role USING (role_id)
    LEFT JOIN analysis_base a USING (analysis_id);
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
-- show the jobs with related semaphores -------
--
-- Usage:
--       select * from semaphore_job;

DROP VIEW IF EXISTS semaphore_job;
CREATE VIEW IF NOT EXISTS semaphore_job AS
    SELECT
        job.job_id AS job_id,
        job.prev_job_id AS prev_job_id,
        job.analysis_id AS analysis_id,
        job.input_id AS input_id,
        job.param_id_stack AS param_id_stack,
        job.accu_id_stack AS accu_id_stack,
        job.role_id AS role_id,
        job.status AS status,
        job.retry_count AS retry_count,
        job.when_completed AS when_completed,
        job.runtime_msec AS runtime_msec,
        job.query_count AS query_count,
        semaphore.local_jobs_counter AS local_jobs_counter,
        semaphore.remote_jobs_counter AS remote_jobs_counter,
        semaphore.dependent_semaphore_url AS dependent_semaphore_url
    FROM job JOIN semaphore
    ON semaphore.dependent_job_id = job.job_id;


95 96 97 98 99 100
-- show statistics of Workers' real resource usage by analysis -------------------------------------------
--
-- Usage:
--       select * from resource_usage_stats;
--       select * from resource_usage_stats where logic_name like 'family_blast%';

101
DROP VIEW IF EXISTS resource_usage_stats;
102 103 104 105
CREATE VIEW IF NOT EXISTS resource_usage_stats AS
    SELECT a.logic_name || '(' || a.analysis_id || ')' analysis,
           w.meadow_type,
           rc.name || '(' || rc.resource_class_id || ')' resource_class,
106
           u.exit_status,
107
           count(*) AS num_workers,
108
           min(mem_megs) AS min_mem_megs, round(avg(mem_megs),2) AS avg_mem_megs, max(mem_megs) AS max_mem_megs,
109
           round(min(cpu_sec/lifespan_sec),2) AS min_cpu_usage, round(avg(cpu_sec/lifespan_sec),2) AS avg_cpu_usage, round(max(cpu_sec/lifespan_sec),2) AS max_cpu_usage
110 111
    FROM resource_class rc
    JOIN analysis_base a USING(resource_class_id)
112 113
    LEFT JOIN role r USING(analysis_id)
    LEFT JOIN worker w USING(worker_id)
114
    LEFT JOIN worker_resource_usage u USING (worker_id)
115 116
    GROUP BY a.analysis_id, w.meadow_type, rc.resource_class_id, u.exit_status
    ORDER BY a.analysis_id, w.meadow_type, rc.resource_class_id, u.exit_status;
117

118 119 120 121 122 123 124

-- show the roles that are currently live (grouped by meadow_users, resource_classes and analyses) -------
--
-- Usage:
--       select * from live_roles;
--       select * from live_roles where resource_class_id=12;

125
DROP VIEW IF EXISTS live_roles;
126
CREATE VIEW IF NOT EXISTS live_roles AS
127
    SELECT w.meadow_user, w.meadow_type, w.resource_class_id, rc.name resource_class_name, r.analysis_id, a.logic_name, count(*) AS num_workers
128 129 130
    FROM worker w
    JOIN role r USING(worker_id)
    LEFT JOIN resource_class rc ON w.resource_class_id=rc.resource_class_id
Jan Vogel's avatar
Jan Vogel committed
131
    LEFT JOIN analysis_base a ON a.analysis_id = r.analysis_id
132 133 134 135
    WHERE r.when_finished IS NULL
    GROUP BY w.meadow_user, w.meadow_type, w.resource_class_id, rc.name, r.analysis_id, a.logic_name;


136 137 138 139 140 141 142 143 144 145
-- show activity of beekeepers in this hive
--
-- Usage:
--       select * from beekeeper_activity;
--
--       -- Find beekeepers that may have disappeared
--       select * from beekeeper_activity
--       where cause_of_death is null
--       and is_overdue = 1;

146
DROP VIEW IF EXISTS beekeeper_activity;
147
CREATE VIEW IF NOT EXISTS beekeeper_activity AS
148
    SELECT b.beekeeper_id, b.meadow_user, b.meadow_host, b.sleep_minutes, b.loop_limit, b.is_blocked,
149 150 151 152 153 154 155 156 157
           b.cause_of_death, COUNT(*) AS loops_executed,
           MAX(lm.when_logged) AS last_heartbeat,
           STRFTIME('%s', 'now') - STRFTIME('%s', MAX(lm.when_logged)) AS time_since_last_heartbeat,
           ((sleep_minutes * 60) -
                (STRFTIME('%s', 'now') - STRFTIME('%s', MAX(lm.when_logged)))) < 0 AS is_overdue
    FROM beekeeper b
    LEFT JOIN log_message lm
    ON b.beekeeper_id = lm.beekeeper_id
    GROUP BY b.beekeeper_id;