Debian DokuWiki

This is an old revision of the document!

---

#!/usr/bin/perl -w
##Jason Thomas
use strict;
use lib "/home/jthomas/lib/perl5/site_perl/5.8.8/";
use Mysql;
use Statistics::Descriptive;
 
 
my $host = "timeinc9-ld01.websys.aol.com";
my $database = "keytest";
my $user = "admin";
my $pw = "admin";
my $beginDate = "2009-07-01";
my $endDate = "2009-07-06";
my @slotId = qw|698549|;
 
my $connect = Mysql -> connect ($host, $database, $user, $pw);
 
	my @textId = findTextId($beginDate, $endDate);
	my @dataSet = getDataSet(@textId);
 
 
 
#print @results2."\n";
 
#print $stat->percentile(85)."\n";
#print $stat->quantile(3);
 
 
sub findTextId{
	my ($beginDate, $endDate) = @_; 
	my @textId;
my $findTextIdQuery = "SELECT 
	a.slot_id,
	concat(c.trans_element_conn,
	b.trans_element_text) full_element_name,
	a.text_id,
	a.coverage,
	a.response_time,
	a.conn_id
	FROM 
		(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, 
	avg(measurement_element+time_to_first_byte+base_page_or_content_download+client_time) response_time,
       	max(element_conn_id) conn_id
	FROM base_element
	WHERE measurement_element>0
	AND date_time >= \'$beginDate\' and date_time <=\'$endDate\'
	AND slot_id = \'$slotId[0]\'
	GROUP BY element_text_id) a,
	global_trans_element_text b,
	global_trans_element_conn c
	WHERE a.text_id = b.trans_element_text_id
	AND a.conn_id = c.trans_element_conn_id
	AND a.coverage > 95
	ORDER BY a.response_time desc 
	LIMIT 12;";
 
 
	my $execute = $connect->query($findTextIdQuery); 
##	print $execute."test";
	while (my @results = $execute->fetchrow()){
##               print $results[2]."\n";
		push(@textId, $results[2]);
	}
 
	return @textId;
 
}
 
sub getDataSet{
 
	my (@textId) = @_;
	my @dataSet;
	for ( my $i = 0; $i < scalar @textId; $i++){
 
	my $selectElements = "SELECT a.responsetime 
	FROM (select avg(base_page_or_content_download+measurement_element+time_to_first_byte+client_time) responsetime
	FROM base_element
	WHERE date_time>=\'$beginDate\'
	AND date_time<\'$endDate\' AND element_text_id=\'$textId[$i]\'
	AND slot_id = \'$slotId[0]\'
	GROUP BY trans_id) a;";
 
	my $execute = $connect->query($selectElements);
	while (my @results = $execute->fetchrow()){
##		print $results[0]."\n";
		push(@dataSet, $results[0]);
	}
 
	my $responseTime85 = calc85th(@dataSet);
	writeDB($slotId[0], $textId[$i], "85", $responseTime85);
	my $responseTime95 = calc95th(@dataSet);
	writeDB($slotId[0], $textId[$i], "95", $responseTime95);
	}
 
	return @dataSet;
}
 
sub calc85th{
 
	my (@dataSet) = @_;
	my $stat = Statistics::Descriptive::Full->new();
	$stat->add_data (@dataSet);
	##print $stat->percentile(85)."\n";
	return $stat->percentile(85);
 
}
 
 
sub calc95th{
 
	my (@dataSet) = @_;
	my $stat = Statistics::Descriptive::Full->new();
	$stat->add_data (@dataSet);
	##print $stat->percentile(95)."\n";
	return $stat->percentile(95)."\n";
 
}
 
sub writeDB{
 
	my $connectNew = Mysql -> connect ($host, $database, $user, $pw);
	my ($slotId, $textId, $percentile, $responseTime) = @_; 
	my $tableName = "percentile_tmp"; 
 
	my $writeQuery = "INSERT INTO $tableName (slot_id, element_text_id, percentile, response_time)
		VALUES (\'$slotId\', \'$textId\', \'$percentile\', \'$responseTime\');";
	my $execute = $connectNew->query($writeQuery);
 
 
}