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:[EMAIL PROTECTED] http://apachetoday.com http://jazzvalley.com http://singlesheaven.com http://perl.apache.org http://perlmonth.com/
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 <[EMAIL PROTECTED]> =cut
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]