Debian DokuWiki

User Tools

Site Tools

Trace: • freenas • bash_functions • postgresql_replication_monitor
perl_db_script

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
perl_db_script [2009/08/25 02:57] – created jtperl_db_script [2020/08/10 02:35] (current) – external edit 127.0.0.1
Line 1: Line 1:
 <code perl> <code perl>
 #!/usr/bin/perl -w #!/usr/bin/perl -w
-##Jason Thomas+#Jason Thomas
 use strict; use strict;
 use lib "/home/jthomas/lib/perl5/site_perl/5.8.8/"; use lib "/home/jthomas/lib/perl5/site_perl/5.8.8/";
Line 8: Line 8:
  
  
-my $host = "timeinc9-ld01.websys.aol.com";+my $host = "hostname";
 my $database = "keytest"; my $database = "keytest";
 my $user = "admin"; my $user = "admin";
 my $pw = "admin"; my $pw = "admin";
 my $beginDate = "2009-07-01"; my $beginDate = "2009-07-01";
-my $endDate = "2009-07-06"; +my $endDate = "2009-07-31"; 
-my @slotId = qw|698549|;+my @slotId = ("731147","731148","799084","799086","799088","799090","799091","698549");
  
 my $connect = Mysql -> connect ($host, $database, $user, $pw); my $connect = Mysql -> connect ($host, $database, $user, $pw);
  
- my @textId = findTextId($beginDate, $endDate); + findTextId($beginDate, $endDate);
- my @dataSet = getDataSet(@textId); +
- +
- +
- +
-#print @results2."\n"; +
- +
-#print $stat->percentile(85)."\n"; +
-#print $stat->quantile(3);+
  
  
 sub findTextId{ sub findTextId{
  my ($beginDate, $endDate) = @_;   my ($beginDate, $endDate) = @_; 
 +
 + for ( my $i = 0; $i < scalar @slotId; $i++){
 +
  my @textId;  my @textId;
-my $findTextIdQuery = "SELECT + 
 + my $findTextIdQuery = "SELECT 
  a.slot_id,  a.slot_id,
  concat(c.trans_element_conn,  concat(c.trans_element_conn,
Line 42: Line 38:
  FROM   FROM 
  (SELECT element_text_id text_id,slot_id,  (SELECT element_text_id text_id,slot_id,
- count(element_text_id)/(SELECT count(*) c FROM base_trans WHERE date_time>=\'$beginDate\' AND date_time<=\'$endDate\' AND slot_id=\'$slotId[0]\')*100 coverage, + count(element_text_id)/(SELECT count(*) c FROM base_trans WHERE date_time>=\'$beginDate\' AND date_time<=\'$endDate\' AND slot_id=\'$slotId[$i]\')*100 coverage, 
  avg(measurement_element+time_to_first_byte+base_page_or_content_download+client_time) response_time,  avg(measurement_element+time_to_first_byte+base_page_or_content_download+client_time) response_time,
         max(element_conn_id) conn_id         max(element_conn_id) conn_id
Line 48: Line 44:
  WHERE measurement_element>0  WHERE measurement_element>0
  AND date_time >= \'$beginDate\' and date_time <=\'$endDate\'  AND date_time >= \'$beginDate\' and date_time <=\'$endDate\'
- AND slot_id = \'$slotId[0]\'+ AND slot_id = \'$slotId[$i]\'
  GROUP BY element_text_id) a,  GROUP BY element_text_id) a,
  global_trans_element_text b,  global_trans_element_text b,
Line 58: Line 54:
  LIMIT 12;";  LIMIT 12;";
  
 + print $findTextIdQuery."\n";
  my $execute = $connect->query($findTextIdQuery);   my $execute = $connect->query($findTextIdQuery); 
 ## print $execute."test"; ## print $execute."test";
- while (my @results = $execute->fetchrow()){ + while (my @results = $execute->fetchrow()){ 
-##               print $results[2]."\n"; +                print $results[2]."\n"; 
- push(@textId, $results[2]);+ push(@textId, $results[2]); 
 + } 
 + getDataSet($slotId[$i], @textId);
  }  }
-  
- return @textId; 
- 
 } }
  
 sub getDataSet{ sub getDataSet{
  
- my (@textId) = @_;+ my ($slotIdSingle, @textId) = @_;
  my @dataSet;  my @dataSet;
  for ( my $i = 0; $i < scalar @textId; $i++){  for ( my $i = 0; $i < scalar @textId; $i++){
Line 81: Line 76:
  WHERE date_time>=\'$beginDate\'  WHERE date_time>=\'$beginDate\'
  AND date_time<\'$endDate\' AND element_text_id=\'$textId[$i]\'  AND date_time<\'$endDate\' AND element_text_id=\'$textId[$i]\'
- AND slot_id = \'$slotId[0]\'+ AND slot_id = \'$slotIdSingle\'
  GROUP BY trans_id) a;";  GROUP BY trans_id) a;";
 + print $selectElements."\n";
  my $execute = $connect->query($selectElements);  my $execute = $connect->query($selectElements);
  while (my @results = $execute->fetchrow()){  while (my @results = $execute->fetchrow()){
Line 91: Line 86:
   
  my $responseTime85 = calc85th(@dataSet);  my $responseTime85 = calc85th(@dataSet);
- writeDB($slotId[0], $textId[$i], "85", $responseTime85);+ writeDB($slotIdSingle, $textId[$i], "85", $responseTime85);
  my $responseTime95 = calc95th(@dataSet);  my $responseTime95 = calc95th(@dataSet);
- writeDB($slotId[0], $textId[$i], "95", $responseTime95);+ writeDB($slotIdSingle, $textId[$i], "95", $responseTime95);
  }  }
  
- return @dataSet; 
 } }
  
perl_db_script.1251169050.txt.gz · Last modified: 2020/08/10 02:30 (external edit)