IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 30457 for trunk/tools/psstatus


Ignore:
Timestamp:
Feb 2, 2011, 11:52:19 AM (15 years ago)
Author:
bills
Message:

Improvements to the psstatus script. Remove dead code. Added more information

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/tools/psstatus

    r28940 r30457  
    1919my $dbuser;
    2020my $dbpassword;
    21 my $use_mysql = 1;
    2221my $verbose;
     22my $finishing;
    2323
    2424GetOptions(
    2525    'running|r',     \$running,
    2626    'finished|f',    \$finished,
     27    'finishing',     \$finishing,
    2728    'req_faulted',   \$req_faulted,
    2829    'job_faulted',   \$job_faulted,
    … …  
    3233) or pod2usage (2);
    3334
    34 pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;
     35pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV ;
     36
     37$running = 1 if (!$finished and !$finishing and !$running);
    3538
    3639my $no_args = ! (defined $running or defined $finished or defined $req_faulted or defined $job_faulted );
    … …  
    4245my $dbh = getDBHandle();
    4346
    44 my $jobLabel = $running ? "Running jobs" : "Num Jobs";
    45 my $select = "SELECT req_id, name, label, reqType, pstampRequest.state, pstampRequest.fault, count(job_id) AS '$jobLabel', timestamp";
    46 
    47 $select .= ", outdir" if ($verbose);
    48 my $from = " FROM pstampRequest LEFT JOIN pstampJob USING(req_id)";
    49 my $where = " WHERE (pstampRequest.state NOT like '%cleaned')";
    50 
    51 my $sql = $select . $from . $where;
    52 
    53 $limit = 20 if ($no_args);
    54 # if ($no_args and $limit) {
    55 if (0 and $limit) {
    56     # use mysql to get the last_req_id
    57     # this works but it prints out the @last_req_id value which is distracting. Use dbi for the query
    58     my $where = "";
    59     if ($running) {
    60         $where = " WHERE pstampRequest.state = 'run' OR pstampRequest.state = 'new'";
    61     } elsif ($finished) {
    62         $where = " WHERE pstampRequest.state = 'stop'";
    63     }
    64     my $stmt = $dbh->prepare("SELECT req_id FROM pstampRequest $where ORDER BY req_id DESC LIMIT 1");
    65     $stmt->execute();
    66     my $row = $stmt->fetchrow_hashref();
    67     die "failed to get last req_id\n"  if !$row;
    68     my $last_req_id = $row->{req_id};
    69     die "failed to get last req_id\n"  if !$last_req_id;
    70 
    71     my $first_req_id_to_select = $last_req_id - $limit + 1;
    72     $sql .= " AND (req_id >= $first_req_id_to_select)";
    73 }
    74 if ($running) {
    75     $sql .= " AND (pstampRequest.state = 'new' OR pstampRequest.state = 'run') AND (pstampJob.state = 'run' OR pstampJob.state IS NULL)";
    76 } elsif ($finished) {
    77     $sql .= " AND (pstampRequest.state = 'stop') AND date_add(timestamp, interval 1 day) >=utc_timestamp()"
    78 }
    79 if ($req_faulted) {
    80     $sql .= " AND (pstampRequest.fault > 0)";
    81 }
    82 if ($job_faulted) {
    83     $sql .= " AND (pstampJob.fault > 0 AND pstampJob.fault < 10)";
    84 }
    85 $sql .= " GROUP BY req_id";
    86 
     47my $running_query = "
     48SELECT
     49    unfinishedReq.req_id,
     50    name,
     51    label,
     52    reqType
     53    state,
     54    fault,
     55    IFNULL(runningJobs, 0) AS 'Pending Jobs',
     56    IFNULL(faultedJobs, 0) AS 'Faulted Jobs',
     57    IFNULL(completedJobs,0) AS 'Completed Jobs',
     58    timestamp AS 'last state change (UTC)'
     59FROM (
     60    SELECT
     61    req_id,
     62    name,
     63    label,
     64    reqType,
     65    pstampRequest.state,
     66    pstampRequest.fault,
     67    timestamp
     68FROM pstampRequest
     69    WHERE pstampRequest.state = 'run' or pstampRequest.state ='new'
     70) as unfinishedReq
     71LEFT JOIN (
     72    SELECT req_id,
     73        count(job_id) AS runningJobs
     74    FROM pstampRequest JOIN pstampJob USING(req_id)
     75    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new')
     76        AND pstampJob.state = 'run'
     77        AND pstampJob.fault = 0
     78    GROUP BY req_id
     79) as runningJobs
     80ON unfinishedReq.req_id = runningJobs.req_id
     81LEFT JOIN (
     82    SELECT req_id,
     83        count(job_id) AS faultedJobs
     84    FROM pstampRequest JOIN pstampJob USING(req_id)
     85    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new')
     86        AND pstampJob.state = 'run'
     87        AND pstampJob.fault > 0
     88    GROUP BY req_id
     89) as faultedJobs
     90ON unfinishedReq.req_id = faultedJobs.req_id
     91LEFT JOIN (
     92    SELECT req_id,
     93        count(job_id) AS completedJobs
     94    FROM pstampRequest JOIN pstampJob USING(req_id)
     95    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new')
     96        AND pstampJob.state = 'stop'
     97    GROUP BY req_id
     98) as finishedJobs
     99ON unfinishedReq.req_id = finishedJobs.req_id
     100
     101ORDER by unfinishedReq.req_id
     102";
     103
     104my $finishing_query = "
     105SELECT
     106    req_id,
     107    name,
     108    label,
     109    reqType,
     110    pstampRequest.state,
     111    pstampRequest.fault,
     112    count(job_id) as 'Finished jobs',
     113    timestamp as 'last state change'
     114FROM pstampRequest
     115    JOIN pstampJob USING(req_id)
     116WHERE pstampRequest.state ='run'
     117    AND (
     118        SELECT count(job_id) FROM pstampJob
     119            WHERE pstampJob.req_id = pstampRequest.req_id
     120                AND pstampJob.state != 'stop'
     121    ) = 0
     122GROUP BY req_id
     123ORDER by req_id
     124";
     125
     126my $finished_query = "
     127SELECT
     128    finishedReq.req_id,
     129    name,
     130    label,
     131    reqType,
     132    IFNULL(success, 0) AS 'Successful Jobs',
     133    IFNULL(faulted,0) AS 'Faulted Jobs',
     134    timestamp AS 'Completion Time (UTC)'
     135FROM (
     136    SELECT
     137    req_id,
     138    name,
     139    label,
     140    reqType,
     141--    pstampRequest.state,
     142--    pstampRequest.fault,
     143    timestamp
     144FROM pstampRequest
     145    WHERE pstampRequest.state = 'stop'
     146        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
     147) as finishedReq
     148LEFT JOIN (
     149    SELECT req_id,
     150        count(job_id) AS success
     151    FROM pstampRequest JOIN pstampJob USING(req_id)
     152    WHERE pstampRequest.state = 'stop'
     153        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
     154        AND pstampJob.fault = 0
     155    GROUP BY req_id
     156) as successfulJobs
     157ON finishedReq.req_id = successfulJobs.req_id
     158LEFT JOIN (
     159    SELECT req_id,
     160        count(job_id) AS faulted
     161    FROM pstampRequest JOIN pstampJob USING(req_id)
     162    WHERE pstampRequest.state = 'stop'
     163        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
     164        AND pstampJob.fault > 0
     165    GROUP BY req_id
     166) as faultedJobs
     167ON finishedReq.req_id = faultedJobs.req_id
     168ORDER by timestamp desc
     169";
     170
     171
     172my $sql;
    87173if ($finished) {
    88     $sql .= " ORDER BY timestamp DESC";
    89 }
     174    $sql = $finished_query;
     175} elsif ($running) {
     176    $sql = $running_query;
     177} elsif ($finishing) {
     178    $sql = $finishing_query;
     179} else {
     180    die "can't happen";
     181}
     182
    90183if ($limit and !$no_args) {
    91184    $sql .= " LIMIT $limit";
    92185}
    93186
    94 if ($use_mysql) {
     187{
    95188#    print STDERR "$sql\n";
    96189    my @args = ( "--table", "--user=$dbuser",  "--host=$dbserver", "--password=$dbpassword", $dbname);
    … …  
    98191    push @args, '--verbose' if $verbose;
    99192
    100     open my $mysql, "|-", 'mysql', @args or die "failed to run mysql\n";
    101     print $mysql "$sql;\n";
    102     close $mysql;
    103 
    104 } else {
    105         # I thought that I wanted custom formatting but in the end I figured I'd just use mysql
    106 
    107         my $stmt = $dbh->prepare($sql);
    108         $stmt->execute();
    109 
    110         my $head = "| req_id | name                                                            | label           | reqType   | state | fault  |  numJobs   | outdir";
    111         my $head2= "-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------";
    112         #           |   5499 | cclin_20100522T011813                                           | WEB.UP          | pstamp    | stop  |      0 |          1 |/data/ippdb02.0/pstamp/work/20100521/5517"
    113 
    114         my $printhead = 1;
    115         my $count = 0;
    116         while (  my $row = $stmt->fetchrow_hashref() ) {
    117             $count++;
    118             if ($printhead) {
    119                 print "$head\n";
    120                 print "$head2\n";
    121                 $printhead = 0;
    122             }
    123             printf "|%7d | %-64s| %-16s| %-10s| %-6s| %6d | %10d | %-s |\n",
    124                 $row->{req_id}, be_safe($row->{name}), $row->{label}, be_safe($row->{reqType}), $row->{state}, $row->{fault},
    125                 $row->{numJobs}, $row->{outdir};
    126         }
    127 
    128         if (!$count) {
    129             print "no requests found\n";
    130         } else {
    131             print "$head2\n";
    132         }
     193    open my $mysql_pipe, "|-", 'mysql', @args or die "failed to run mysql\n";
     194    print $mysql_pipe "$sql;\n";
     195    close $mysql_pipe;
    133196
    134197}
    135198
    136199exit 0;
    137 
    138 sub be_safe {
    139     my $str = shift;
    140 
    141     return $str ? $str : 'null';
    142 }
    143200
    144201
Note: See TracChangeset for help on using the changeset viewer.