Changeset 29515 for branches/eam_branches/ipp-20100823/tools/czartool/CzarDb.pm

Timestamp:

Oct 21, 2010, 2:45:13 PM (16 years ago)

Author:

eugene

Message:

merge changes from trunk

File:

• branches/eam_branches/ipp-20100823/tools/czartool/CzarDb.pm (modified) (30 diffs)

branches/eam_branches/ipp-20100823/tools/czartool/CzarDb.pm

@@ -11 +11 @@
 our @ISA = qw(czartool::MySQLDb);    # inherits from MySQLDb
 
-# Override constructor
+###########################################################################
+#
+# Constructor (overrides superclass)
+#
+###########################################################################
 sub new {
     my ($class) = @_;
 
-    # Call the constructor of the parent class, Person.
-    my $self = $class->SUPER::new($_[1], $_[2], $_[3], $_[4],  $_[5], $_[6]);
+    # Call the constructor of the parent class
+    my $self = $class->SUPER::new($_[1], $_[2], $_[3], $_[4], $_[5], $_[6]);
 
     bless $self, $class;
@@ -39 +43 @@
 SQL
 
-
-    if (!$query->execute) {
-
-        return 0;
-    }
+    if (!$query->execute) {return 0;}
 
     ${$labels} = $query->fetchall_arrayref();
@@ -238 +238 @@
 ###########################################################################
 #
-# Gets time series data and stores it to temp file
-#
-###########################################################################
-sub createTimeSeriesData {
-    my ($self, $label, $stage, $fromTime, $toTime, $minX, $maxX, $minY, $maxY, $timeDiff, $dataFile, $isLog) = @_;
-
-    ${$dataFile} = "/tmp/czarplot_gnuplot_".$label."_".$stage."_t.dat";
-
-    open (GNUDAT, ">${$dataFile}") or print "* Problem opening gnuplot data file for plot for '$label' '$stage'\n";
+# Gets time min/max/diff for a given period
+#
+###########################################################################
+sub getTimeMinMaxDiff {
+    my ($self, $label, $stage, $fromTime, $toTime, $minX, $maxX, $timeDiff) = @_;
 
     my $query = $self->{_db}->prepare(<<SQL);
     SELECT 
-        MIN(processed), GREATEST(MAX(pending), MAX(faults), max(processed)-min(processed)) AS maxY, 
-        LEAST(MIN(pending), MIN(faults), max(processed)-min(processed)) AS minY,
         DATE_FORMAT(MAX(timestamp),'$self->{_dateFormat}'), 
         DATE_FORMAT(MIN(timestamp),'$self->{_dateFormat}'),
@@ -262 +256 @@
     if (!$query->execute) {return 0;}
 
-    my $minProcessed = undef;
-    my ($_maxY, $_minY, $_timeDiff);
-    ($minProcessed, $_maxY, $_minY, ${$maxX}, ${$minX}, $_timeDiff) = $query->fetchrow_array();
-
-    if ($_maxY > ${$maxY}) {${$maxY} = $_maxY;}
-    if ($_minY < ${$minY}) {${$minY} = $_minY;}
+    my ($_timeDiff);
+    (${$maxX}, ${$minX}, $_timeDiff) = $query->fetchrow_array();
+
     if ($_timeDiff > ${$timeDiff}) {${$timeDiff} = $_timeDiff;}
 
+return 1;
+}
+
+
+###########################################################################
+#
+# Gets time series data and stores it to temp file
+#
+###########################################################################
+sub createTimeSeriesData {
+    my ($self, $label, $stage, $fromTime, $toTime, $minX, $maxX, $timeDiff, $dataFile, $isLog, $showCleanup, $firstDeriv) = @_;
+
+    my $query = $self->{_db}->prepare(<<SQL);
+    SELECT 
+        MIN(processed)
+        FROM $stage 
+        WHERE label LIKE '$label'
+        AND timestamp >= '$fromTime' AND timestamp <= '$toTime'; 
+SQL
+
+    if (!$query->execute) {return 0;}
+    my $minProcessed = scalar  $query->fetchrow_array(); # TODO remove
     if (!defined $minProcessed) {return 0;}
+
+    $self->getTimeMinMaxDiff($label, $stage, $fromTime, $toTime, $minX, $maxX, $timeDiff);
 
     $query = $self->{_db}->prepare(<<SQL);
     SELECT 
-        DATE_FORMAT(timestamp, '$self->{_dateFormat}'), pending, faults, processed-$minProcessed 
+        timestamp, pending, faults, processed 
         FROM $stage 
         WHERE label LIKE '$label' 
@@ -283 +298 @@
     $query->execute;
 
-    # loop round results
+    ${$dataFile} = "/tmp/czarplot_gnuplot_".$label."_".$stage."_t.dat";
+    open (GNUDAT, ">${$dataFile}") or print "* Problem opening gnuplot data file for plot for '$label' '$stage'\n";
+
+    my $lastProcessed = -1;
+    my $lastPending = -1;
+    my $lastFaults = -1;
+    my $lastTimestamp = undef;
+    my $processed = 0;
+    my $runningProcessed = 0;
+    my $pending = 0;
+    my $faults = 0;
+    my $timestamp = undef;
     while (my @row = $query->fetchrow_array()) {
-        my ($timestamp, $pending, $faults, $processed) = @row;
-
-        if ($isLog) {
-
-            $processed = log($processed + 1);
-            $pending = log($pending + 1);
-            $faults = log($faults + 1);
-        }
+        my ($thisTimestamp, $thisPending, $thisFaults, $thisProcessed) = @row;
+
+        if ($showCleanup ) {
+        
+            $runningProcessed = $thisProcessed - $minProcessed;
+        }
+        elsif($lastProcessed != -1 && $thisProcessed > $lastProcessed) {
+            
+            $runningProcessed = $runningProcessed + ($thisProcessed - $lastProcessed);
+        }
+
+
+        if ($firstDeriv) {
+
+            if (defined $lastTimestamp) {
+
+                my $timeDiff = $self->diffTimesInSecs($thisTimestamp, $lastTimestamp);
+                
+                if ($thisProcessed > $lastProcessed){
+                $processed = abs($thisProcessed - $lastProcessed)/$timeDiff; 
+#print "$processed = ($thisProcessed - $lastProcessed)/$timeDiff\n";
+                }
+                else {$processed = 0;}
+                #$pending = abs($thisPending - $lastPending)/$timeDiff;    
+                #$faults = abs($thisFaults - $lastFaults)/$timeDiff;    
+            }
+            else {
+                $processed = 0;
+                $pending = 0;
+                $faults = 0; 
+            }
+        }
+        elsif ($isLog) {
+
+            $processed = ($runningProcessed < 0) ? 0 : $runningProcessed;
+            $pending =  ($thisPending < 0) ? 0 : $thisPending;
+            $faults =  ($thisFaults < 0) ? 0 : $thisFaults;
+
+            $processed = log($runningProcessed + 1)/log(10);
+            $pending = log($thisPending + 1)/log(10);
+            $faults = log($faults + 1)/log(10);
+        }
+        else {
+        
+            $processed = $runningProcessed;
+            $pending = $thisPending;
+            $faults = $thisFaults;
+        }
+
+        $timestamp = $self->getFormattedDate($thisTimestamp);
 
         print GNUDAT "$timestamp $pending $faults $processed\n";
+
+        $lastProcessed = $thisProcessed;
+        $lastPending = $thisPending;
+        $lastFaults = $thisFaults;
+        $lastTimestamp = $thisTimestamp;
     }
 
@@ -357 +430 @@
 sub countProcessedPendingAndFaults {
     my ($self, $label, $stage, $fromTime, $toTime, $processed, $pending, $faults) = @_;
-
 
     my $query = $self->{_db}->prepare(<<SQL);
@@ -371 +443 @@
     (${$pending}, ${$faults}) = $query->fetchrow_array();    
 
-    $query = $self->{_db}->prepare(<<SQL);
-    SELECT 
-        MAX(processed) - MIN(processed) 
-        FROM $stage 
-        WHERE label LIKE '$label' 
-        AND timestamp >= '$fromTime' AND timestamp <= '$toTime'; 
-SQL
-    $query->execute;
-    (${$processed}) = $query->fetchrow_array();
+    ${$processed} = $self->countProcessed($label, $stage, $fromTime, $toTime);
+
+    return 1;
 }
 
@@ -502 +568 @@
         print GNUDAT "$timestamp $available\n";
     }
+
     close(GNUDAT);
 
     return $dataFile;
 }
-###########################################################################
-#
-# Determines how much has been processed in the provided interval of time 
-# (format: 1 HOUR, 1 MINUTE 1 DAY etc)
-#
-###########################################################################
-sub countProcessed { # TODO use time not interval
-    my ($self, $label, $stage, $interval) = @_;
+
+###########################################################################
+#
+# TODO implement isLog
+#
+###########################################################################
+sub createProcessingRateData {
+    my ($self, $stage, $label, $startDay, $endDay, $interval, $dataFile, $isLog) = @_;
+
+    my $startTime = $startDay;
+    my $endTime;
+    my $quit = 0;
+    my $processed;
+    my $pending;
+    my $faults;
+    my $timestamp;
+    ${$dataFile} = "/tmp/czarplot_gnuplot_".$label."_".$stage."_r.dat";
+    open (GNUDAT, ">${$dataFile}") or print "* Problem opening gnuplot data file for plot for '$label' '$stage'\n";
+    my $cleanupCarry = 0;
+    while(1) {
+
+        if (!$self->isBefore($startTime, $endDay)) {last;}
+        $endTime = $self->addInterval($startTime, $interval);
+        $self->countProcessedPendingAndFaults($label, $stage, $startTime, $endTime, \$processed, \$pending, \$faults);
+        $timestamp = $self->getFormattedDate($endTime);
+        print GNUDAT "$timestamp $processed 0 0\n";
+
+        $startTime = $endTime;
+    }
+
+    close(GNUDAT) or print "* Problem closing gnuplot data file for rate plot for '$label' '$stage'\n"
+}
+
+###########################################################################
+#
+# When did this stage finish? 
+#
+###########################################################################
+sub getFinishTime {
+    my ($self, $label, $stage, $begin, $end) = @_;
 
     my $query = $self->{_db}->prepare(<<SQL);
     SELECT 
-        MAX(processed) - MIN(processed) 
-        FROM $stage 
+        timestamp, pending, faults
+        FROM $stage
         WHERE label LIKE '$label' 
-        AND timestamp > (now() - INTERVAL $interval); 
-SQL
-        $query->execute;
-
-    return scalar $query->fetchrow_array();
-}
-
+        AND timestamp >= '$begin' 
+        AND timestamp <= '$end' 
+SQL
+    $query->execute;
+
+    my $array = $query->fetchall_arrayref();
+
+    my $row;
+    my $thisPending = -1;
+    my $lastPending = -1;
+    my $thisFaults = -1;
+    my $lastFaults = -1;
+    my $timestamp;
+    my $started = undef;
+    my $finished = undef;
+    my $thisLeft = 0;
+    my $lastLeft = 0;
+    my $finishTimeout = "01:00:00";
+    my $numRows = @{$array};
+    print "$numRows rows\n";
+    my $n = 0;
+    foreach $row ( @{$array} ){
+
+        ($timestamp, $thisPending, $thisFaults) = @{$row};
+
+#        print "$timestamp, $thisPending, $thisFaults\n";
+
+        $thisLeft = $thisPending - $thisFaults;
+        $lastLeft = $lastPending - $lastFaults;
+
+
+        if ($n == 0 && $thisLeft > 0) {
+
+            print "Starting this time period with stuff pending ($thisLeft)\n";
+            $started = $timestamp;
+        }
+
+        if (!defined $started && $lastLeft == 0 && $thisLeft > 0) {
+
+            $started = $timestamp;
+            print "STARTED at $started\n";
+        }
+
+        if ($started && !defined $finished && $thisLeft == 0) {
+
+            $finished = $timestamp;
+            print "setting FINISHED to $finished\n";
+        }
+
+        if (defined $finished) {
+
+            if ($thisLeft != 0) {
+                
+                $finished = undef;
+            
+                print "NEW pending  at $timestamp\n";
+            
+            }
+            elsif ($thisLeft == 0) {
+                
+                my $howLongFinished = $self->diffTimes($timestamp, $finished); 
+
+print "$howLongFinished = $timestamp, $finished\n";
+
+                if ($self->isIntervalGreaterThan($howLongFinished, $finishTimeout)) {
+                    
+                    print "0 pending for $howLongFinished. We're done \n";
+                    last;
+                }
+
+            }
+
+        }
+
+
+        $lastPending = $thisPending;
+        $lastFaults = $thisFaults;
+        $n++;
+        if ($n == $numRows) {
+
+            if ($thisLeft == 0) {
+
+                print "exceeded interval before reaching finished timeout\n";
+            }
+            else {
+                print "Reached end of interval and not finished yet. $thisLeft left\n";
+            }
+        }
+    }
+
+    if ($finished) {
+
+        my $timeTaken = $self->diffTimes($finished, $started);
+        print "FINISHED at $finished with $thisFaults remaining faults, time taken = $timeTaken\n";
+    }
+
+}
+
+###########################################################################
+#
+# Figures out if a stage has plateaued 
+#
+###########################################################################
+sub hasPlateaued {
+    my ($self, $label, $stage, $end, $interval) = @_;
+
+    my $query = $self->{_db}->prepare(<<SQL);
+    SELECT 
+        timestamp, pending, faults
+        FROM $stage
+        WHERE label LIKE '$label'
+        AND timestamp >= '$end - INTERVAL $interval'
+        AND timestamp <= '$end'
+        ORDER BY timestamp DESC;
+SQL
+    $query->execute;
+
+    my $array = $query->fetchall_arrayref();
+
+    my $thisPending = -1;
+    my $lastPending = -1;
+    my $thisFaults = -1;
+    my $lastFaults = -1;
+    my $timestamp;
+    my $thisLeft = 0;
+    my $lastLeft = 0;
+    my $row;
+    my $numRows = @{$array};
+    my $n = 0;
+    foreach $row ( @{$array} ){
+
+        ($timestamp, $thisPending, $thisFaults) = @{$row};
+
+        $thisLeft = $thisPending - $thisFaults;
+        $lastLeft = $lastPending - $lastFaults;
+
+        print "$timestamp $thisPending\n";
+
+        if ($n>0) {
+
+            #if () {}
+
+        }
+
+        $lastPending = $thisPending;
+        $lastFaults = $thisFaults;
+        $n++;
+    }
+}
+
+###########################################################################
+#
+# Gets count of processed stuff in a given time period 
+#
+###########################################################################
+sub countProcessed {
+    my ($self, $label, $stage, $begin, $end) = @_;
+
+    my $query = $self->{_db}->prepare(<<SQL);
+    SELECT 
+        processed 
+        FROM $stage
+        WHERE label LIKE '$label' 
+        AND timestamp >= '$begin' 
+        AND timestamp < '$end' 
+SQL
+        $query->execute;
+
+    my $processedArray = $query->fetchall_arrayref();
+
+    my $processed;
+    my $thisCount = -1;
+    my $lastCount = -1;
+    my $count = 0;
+    foreach $processed ( @{$processedArray} ){
+
+        ($thisCount) = @{$processed};
+
+        if ($thisCount > $lastCount && $lastCount != -1) {$count = $count + ($thisCount -  $lastCount);}
+        $lastCount = $thisCount;
+    }
+
+    return $count;
+}
 
 ###########################################################################
@@ -541 +817 @@
 
         if (!$self->isBefore($thisDay, $endDay)) {
-        
+
             $quit = 1;
         }
@@ -553 +829 @@
 ###########################################################################
 #
-# Deletes all but one row per interval from all stage tables for all labels between the provided day
+# Deletes all but one row per interval from all tables for the provided date range
+# TODO this is very clumsy, I just have time to thinmk of something more elegant
 #
 ###########################################################################
@@ -566 +843 @@
     my $totalDeleted = undef;
     my $quit = 0;
+    my $query = undef;
     while(!$quit) {
 
         $toTime = $self->addInterval($fromTime, $interval);
         if (!$self->isBefore($toTime, $endDay)) {
-        
+
             $toTime = $endDay;
             $quit = 1;
         }
-   
+
         my $stage = undef;
         $totalDeleted = 0;
@@ -586 +864 @@
                 my ($label) = @{$row};
 
-                my $query = $self->{_db}->prepare(<<SQL);
+                $query = $self->{_db}->prepare(<<SQL);
                 SELECT COUNT(*) 
                     FROM $stage 
@@ -609 +887 @@
                 $totalDeleted += $toDelete;
             }
-        }
+
+        }
+
+        # servers table
+        my $servers = undef;
+        if ($self->getServers(\$servers)) {
+
+            my $server = undef;
+            my $row = undef;
+            foreach $row ( @{$servers} ) {
+                my ($server) = @{$row};
+
+                $query = $self->{_db}->prepare(<<SQL);
+                SELECT COUNT(*) 
+                    FROM servers 
+                    WHERE timestamp > '$fromTime'
+                    AND timestamp <= '$toTime'
+                    AND server = '$server' 
+SQL
+                    $query->execute;
+
+                my $toDelete = scalar $query->fetchrow_array() - 1;
+                if ($toDelete > 0) {
+
+                    $query = $self->{_db}->prepare(<<SQL);
+                    DELETE FROM servers 
+                        WHERE timestamp > '$fromTime' 
+                        AND timestamp <= '$toTime' 
+                        AND server = '$server' ORDER BY timestamp DESC LIMIT $toDelete
+SQL
+                        $query->execute;
+
+                    $totalDeleted += $toDelete;
+                }
+            }
+        }
+
+        # now deal with cluster_space table
+        $query = $self->{_db}->prepare(<<SQL);
+        SELECT COUNT(*) 
+            FROM cluster_space 
+            WHERE timestamp > '$fromTime'
+            AND timestamp <= '$toTime'
+SQL
+            $query->execute;
+
+        my $toDelete = scalar $query->fetchrow_array() - 1;
+        if ($toDelete > 0) {
+
+            $query = $self->{_db}->prepare(<<SQL);
+            DELETE FROM cluster_space 
+                WHERE timestamp > '$fromTime' 
+                AND timestamp <= '$toTime' 
+                ORDER BY timestamp DESC LIMIT $toDelete
+SQL
+                $query->execute;
+
+            $totalDeleted += $toDelete;
+        }
+
         print "   * Deleted $totalDeleted between $fromTime and  $toTime\n";
         $fromTime = $toTime;
     }
+}
+
+###########################################################################
+#
+# Optimizes all tables that need to be optimized 
+#
+###########################################################################
+sub optimize {
+    my ($self) = @_;
+
+    my $stage = undef;
+    foreach $stage (@stages) {
+
+        $self->optimizeTable($stage);
+    }
+
+    $self->optimizeTable("cluster_space");
+    $self->optimizeTable("servers");
+}
+
+###########################################################################
+#
+# Returns if a particular server has been down for a certain interval 
+#
+###########################################################################
+sub isServerDown {
+    my ($self, $server, $interval) = @_;
+
+    my $query = $self->{_db}->prepare(<<SQL);
+    SELECT COUNT(*) 
+        FROM servers
+        WHERE server = "$server" 
+        AND timestamp > now() - INTERVAL $interval
+SQL
+
+        $query->execute;
+    my $numOfReadings = scalar $query->fetchrow_array();
+
+    if ($numOfReadings == 0) {return 0;}
+
+    $query = $self->{_db}->prepare(<<SQL);
+    SELECT COUNT(*) 
+        FROM servers
+        WHERE server = "$server" 
+        AND timestamp > now() - INTERVAL $interval
+        AND !running
+SQL
+
+        $query->execute;
+    my $numNotRunning = scalar $query->fetchrow_array();
+
+    if ($numOfReadings == $numNotRunning) {return 1;}
+
+    return 0;
+}
+
+###########################################################################
+#
+# Returns an array of servers 
+#
+###########################################################################
+sub getServers {
+    my ($self, $servers) = @_;
+
+    my $query = $self->{_db}->prepare(<<SQL);
+    SELECT DISTINCT server
+        FROM servers
+SQL
+
+        if (!$query->execute) {
+
+            return 0;
+        }
+
+    ${$servers} = $query->fetchall_arrayref();
+
+    return 1;
 }
 
@@ -682 +1096 @@
     INSERT INTO revision (revision) VALUES ($revision);
 SQL
-    $query->execute;
+        $query->execute;
 }
 
@@ -701 +1115 @@
 SQL
 
-    $query->execute;
+        $query->execute;
     my @row = $query->fetchrow_array();
 
@@ -729 +1143 @@
     foreach $stage (@stages) {
 
-    my $query = $self->{_db}->prepare(<<SQL);
+        my $query = $self->{_db}->prepare(<<SQL);
         CREATE TABLE $stage (
                 timestamp TIMESTAMP DEFAULT NOW(),
@@ -758 +1172 @@
     foreach $stage (@stages) {
 
-    my $query = $self->{_db}->prepare(<<SQL);
+        my $query = $self->{_db}->prepare(<<SQL);
         ALTER TABLE $stage 
             ADD COLUMN reverting TINYINT NOT NULL DEFAULT 0;
@@ -783 +1197 @@
     foreach $stage (@stages) {
 
-    my $query = $self->{_db}->prepare(<<SQL);
+        my $query = $self->{_db}->prepare(<<SQL);
         ALTER TABLE $stage 
             ADD COLUMN processed BIGINT NOT NULL DEFAULT 0;
 SQL
 
-      $query->execute;
+            $query->execute;
     }
 
@@ -808 +1222 @@
 SQL
 
-      $query->execute;
+        $query->execute;
 
     $self->setRevision(4);
@@ -830 +1244 @@
 SQL
 
-      $query->execute;
+        $query->execute;
 
     $self->setRevision(5);
@@ -854 +1268 @@
 SQL
 
-      $query->execute;
-    }
-
-        my $query = $self->{_db}->prepare(<<SQL);
-        CREATE INDEX serverIndex ON servers (timestamp, server);
-SQL
-
-      $query->execute;
+            $query->execute;
+    }
+
+    my $query = $self->{_db}->prepare(<<SQL);
+    CREATE INDEX serverIndex ON servers (timestamp, server);
+SQL
+
+        $query->execute;
 
 
@@ -882 +1296 @@
 SQL
 
-    $query->execute;
+        $query->execute;
 
     $self->setRevision(7);
@@ -906 +1320 @@
 SQL
 
-    $query->execute;
+            $query->execute;
     }
 
@@ -916 +1330 @@
 SQL
 
-    $query->execute;
+        $query->execute;
 
     # insert stages into revert table
@@ -926 +1340 @@
             ('$stage', 0);
 SQL
-       $query->execute;
+            $query->execute;
     }
 
@@ -951 +1365 @@
 SQL
 
-      $query->execute;
+        $query->execute;
     $query = $self->{_db}->prepare(<<SQL);
     CREATE TABLE hosts (
@@ -960 +1374 @@
 SQL
 
-      $query->execute;
-
-        $query = $self->{_db}->prepare(<<SQL);
-        CREATE INDEX clusterSpaceIndex ON cluster_space (timestamp);
-SQL
-
-      $query->execute;
+        $query->execute;
+
+    $query = $self->{_db}->prepare(<<SQL);
+    CREATE INDEX clusterSpaceIndex ON cluster_space (timestamp);
+SQL
+
+        $query->execute;
 
     $self->setRevision(9);
@@ -984 +1398 @@
     ALTER TABLE hosts 
         ADD COLUMN writable TINYINT NOT NULL DEFAULT 0,
-        ADD COLUMN readable TINYINT NOT NULL DEFAULT 0;
-SQL
-
-    $query->execute;
+            ADD COLUMN readable TINYINT NOT NULL DEFAULT 0;
+SQL
+
+        $query->execute;
 
     $self->setRevision(10);
@@ -1012 +1426 @@
 SQL
 
-      $query->execute;
-        $query = $self->{_db}->prepare(<<SQL);
-        CREATE INDEX burntoolIndex ON burntool (timestamp, label);
-SQL
-
-      $query->execute;
+        $query->execute;
+    $query = $self->{_db}->prepare(<<SQL);
+    CREATE INDEX burntoolIndex ON burntool (timestamp, label);
+SQL
+
+        $query->execute;
 
     $self->setRevision(11);
@@ -1039 +1453 @@
 SQL
 
-    $query->execute;
+        $query->execute;
 
     $self->setRevision(12);