perl_db_script
Differences
This shows you the differences between two versions of the page.
|
perl_db_script [2009/08/25 04:42] jt |
perl_db_script [2020/08/10 02:35] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | <code perl> | ||
| - | #!/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 = "hostname"; | ||
| - | my $database = "keytest"; | ||
| - | my $user = "admin"; | ||
| - | my $pw = "admin"; | ||
| - | my $beginDate = "2009-07-01"; | ||
| - | my $endDate = "2009-07-31"; | ||
| - | my @slotId = ("731147","731148","799084","799086","799088","799090","799091","698549"); | ||
| - | |||
| - | my $connect = Mysql -> connect ($host, $database, $user, $pw); | ||
| - | |||
| - | findTextId($beginDate, $endDate); | ||
| - | |||
| - | |||
| - | sub findTextId{ | ||
| - | my ($beginDate, $endDate) = @_; | ||
| - | |||
| - | for ( my $i = 0; $i < scalar @slotId; $i++){ | ||
| - | |||
| - | 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[$i]\')*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[$i]\' | ||
| - | 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;"; | ||
| - | |||
| - | print $findTextIdQuery."\n"; | ||
| - | my $execute = $connect->query($findTextIdQuery); | ||
| - | ## print $execute."test"; | ||
| - | while (my @results = $execute->fetchrow()){ | ||
| - | print $results[2]."\n"; | ||
| - | push(@textId, $results[2]); | ||
| - | } | ||
| - | getDataSet($slotId[$i], @textId); | ||
| - | } | ||
| - | } | ||
| - | |||
| - | sub getDataSet{ | ||
| - | |||
| - | my ($slotIdSingle, @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 = \'$slotIdSingle\' | ||
| - | GROUP BY trans_id) a;"; | ||
| - | print $selectElements."\n"; | ||
| - | my $execute = $connect->query($selectElements); | ||
| - | while (my @results = $execute->fetchrow()){ | ||
| - | ## print $results[0]."\n"; | ||
| - | push(@dataSet, $results[0]); | ||
| - | } | ||
| - | |||
| - | my $responseTime85 = calc85th(@dataSet); | ||
| - | writeDB($slotIdSingle, $textId[$i], "85", $responseTime85); | ||
| - | my $responseTime95 = calc95th(@dataSet); | ||
| - | writeDB($slotIdSingle, $textId[$i], "95", $responseTime95); | ||
| - | } | ||
| - | |||
| - | } | ||
| - | |||
| - | 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); | ||
| - | |||
| - | |||
| - | } | ||
| - | |||
| - | |||
| - | </code> | ||
perl_db_script.txt ยท Last modified: 2020/08/10 02:35 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
