[prev in list] [next in list] [prev in thread] [next in thread] 

List:       apache-modperl
Subject:    Antwort: RFC: DBI::Prof
From:       Michael.Jacob () gad ! de
Date:       2000-11-30 13:37:59
[Download RAW message or body]

Hi,

I'm not quite sure, but I think the following would produce wrong results,
wouldn't it?

$sth1 = $dbh->prepare(...);
$sth2 = $dbh->prepare(...);
$sth1->execute();
$sth3 = $dbh->prepare(...);
$sth2->execute();
$sth3->execute();

Michael Jacob


Datum:         28.11.2000 21:12
An:            mod_perl list <modperl@apache.org>


Betreff:       RFC: DBI::Prof
Nachrichtentext:


I have a huge project with lots of tables, and the performance wasn't that
well. So I've started to review the tables definitions and have found that
some indices were missing. I was sick from doing the tracing of all
possible SQL calls manually, so I wrote this simple profiler. Take a look
and tell me if you think it worths releasing on CPAN...

hmm, why mod_perl list... because it works under mod_perl :) In fact I
didn't test it under non mod_perl but it should work as well :)

Anyway, enjoy :)


_____________________________________________________________________
Stas Bekman              JAm_pH     --   Just Another mod_perl Hacker
http://stason.org/       mod_perl Guide  http://perl.apache.org/guide
mailto:stas@stason.org   http://apachetoday.com http://jazzvalley.com
http://singlesheaven.com http://perl.apache.org http://perlmonth.com/



["Prof.pm" (application/octet-stream)]

package DBI::Prof;

use Apache::Constants qw(DECLINED OK);
use Time::HiRes ();

my %results = ();
my $statement ='';

$DBI::Prof::THRESHOLD = 0.01;

my $sub_execute = \&DBI::st::execute;
eval q{
    sub DBI::st::execute{
        my $start_time = [ Time::HiRes::gettimeofday ];
        my $res = &$sub_execute;
        my $end_time = [ Time::HiRes::gettimeofday ];
        my $elapsed = Time::HiRes::tv_interval($start_time,$end_time);
        $results{$statement} = $elapsed;
        $statement = '';
        return $res;
    }
};

my $sub_prepare = \&DBI::db::prepare;
eval q{
       sub DBI::db::prepare{
           $statement = $_[1];
           &$sub_prepare;
       }
   };

sub report{
    my $r = shift;
    print STDERR "Queries with execute() time > $DBI::Prof::THRESHOLD secs\n";
    my $total_time    = 0;
    my $total_queries = 0;
    for (sort {$results{$b} <=> $results{$a}} keys %results) {
        $total_time += $results{$_};
        $total_queries++;
        next if $results{$_} < $DBI::Prof::THRESHOLD;
        print STDERR "$results{$_} $_;\n"
    }
    print STDERR "Total elapsed execute() time: $total_time\n";
    print STDERR "Total number of queries: $total_queries\n\n";
    # reset the values
    %results = ();
    $statement = '';
    return OK;
}

1;
__END__

=head1 NAME

DBI::Prof -- Benchmark the $sth->execute() calls to find slow queries and adjust the table indices.

=head1 SYNOPSYS

Under normal Perl code:

  use DBI ();
  use DBI::mysql (); # or another driver
  use DBI::Prof ();
  ...your code that queries some DB...
  DBI::Prof::report();

Under mod_perl:

  PerlModule DBI
  PerlModule DBI::mysql #  or another driver
  PerlModule DBI::Prof
  PerlLogHandler DBI::Prof::report

This module must be loaded after C<DBD::mysql> (or another driver) is
loaded. Not DBI, but the driver, since C<DBI::Prof> overrides the
execute() and prepare() calls.

=head1 DESCRIPTION

This module allows you to measure the execute() time of all the DBI
queries and thus adjust your code/tables/indices to work faster. It
also reports the total number of queries that were executed and the
total execute() time.

You can modify the C<$DBI::Prof::THRESHOLD> variable to print only
SQLs which were taken more than C<$DBI::Prof::THRESHOLD> seconds to
execute. Notice that the measured time is not the exact time that it
took for sql engine to execute the statement, but a little bit higher.

The output goes at the end of each request to the error_log and only
queries that it took longer than C<$DBI::Prof::THRESHOLD> secs will be
listed, sorted from the longest to the slowest.

I repeat, this is the measurement of the execute() and not
execute()+fetch() which is obviosly longer. So it's mostly useful for
finding queries which don't use indices and therefore very slow.

=head1 AUTHOR

Stas Bekman <stas@stason.org>

=cut



---------------------------------------------------------------------
To unsubscribe, e-mail: modperl-unsubscribe@apache.org
For additional commands, e-mail: modperl-help@apache.org





---------------------------------------------------------------------
To unsubscribe, e-mail: modperl-unsubscribe@apache.org
For additional commands, e-mail: modperl-help@apache.org

[prev in list] [next in list] [prev in thread] [next in thread]