MySQL - Perl Speed Tips?

[Brett_Tabke]

A while back, I made a comment about how slow I felt the DBI/MySQL module usage was - and got some feedback that it could be speed up a bit. How so? What are the tips to speeding up the other was lathargic connection?

[spikey]

I don't know if this is directly relevant but make sure you pool your database handles. Building and tearing down connections really slows things down if you can just keep one open.

I use a sub like this in my classes (sorry for the dropped whitespace):

sub get_dbh {
my $self= shift;
return $self->{'_dbh'} if (ref $self->{'_dbh'});
my $dbh = DBI->connect(@{ $self->{'dbn'} }) ¦¦ 
$self->error('Unable to connect to Database');
return $self->{'_dbh'} = $dbh;
}

[phranque]

have you tried using Benchmark.pm [perldoc.perl.org]?
it's a core perl module.

use Benchmark;
$t0 = new Benchmark;
# ... your code here ...
$t1 = new Benchmark;
$td = timediff($t1, $t0);
print "the code took:",timestr($td),"\n";

if you are doing a repetitive operation you can sum the timediffs to see how much total time you are spending there.
the number one rule in solving efficiency problems is to start with the largest targets.

[phranque]

if it ends up being a connection problem, you should check out Apache::DBI [search.cpan.org] or even mod_dbd [httpd.apache.org].

i assume you are using or have considered mod_perl [perl.apache.org].

[bennymack]

Reusing database connections are always a good idea. You'll also want to use/re-use prepared statement handlers.

Benchmarking is important but probably not as informative as profiling. My favorite profiling module is Devel::SallProf while SMALLPROF_CONFIG=zg. This will give you profiling information to the line. Then use this script to sort the output with the biggest timesinks first then possibly piped to "less" or "$EDITOR -":

#!/usr/bin/perl
use strict;
use warnings;
# * file name : line number : line count : time (ms) : ctime (ms) : line source
my( @unsorted ) = do {
 open( my( $SMALLPROF ), '<', 'smallprof.out' ) or die $!;
 local $/;
 <$SMALLPROF>;
};
my( @sorted ) =
 map { join( ':', @$_ ) }
 sort { $a->[3] <=> $b->[3] }
 map { [ split( /:/, $_ ) ] }
 @unsorted;
print @sorted, "\n";