Re^3: DBI and DBD::Pg: Assessing Performance

>{PERL DBI, DBD}-->{Postgresql}{underlying server OS and >hardware}
If you can emulate your 80-120 transactions at this layer, you may found if it is postgres or not at least. I mean test script on this machine will show you something.

On my old notepc and PostgreSQL 9.0.3, I run my test script and it shows 120 transaction exceeds one second. This is the result of defualt postgres configuration.

count=120
With AutoCommit OFF=0.339
With AutoComit ON time=1.266
[download]

And test code below.

use strict;
use warnings;
use DBI;
use Time::HiRes;

#with AutoCommit = off
sub test_insert1{
my ($dbh,$cnt)=@_;
my($sth,$v,$bgn,$end);
    $v="a" x 1000;
    $sth=$dbh->prepare("insert into test1 (item1) values(?);") or die 
+DBI->errstr;

    $bgn=Time::HiRes::time();
    for (1 .. $cnt){
        $sth->execute($v);
    }
    $sth->finish;
    $dbh->commit;
    $end=Time::HiRes::time();
    return($bgn,$end);
}
#with AutoCommit = on
sub test_insert2{
my ($dbh,$cnt)=@_;
my($sth,$v,$bgn,$end);
    $v="a" x 1000;
    $sth=$dbh->prepare("insert into test1 (item1) values(?);") or die 
+DBI->errstr;

    $bgn=Time::HiRes::time();
    for (1 .. $cnt){
        $sth->execute($v);
    }
    $sth->finish;
    $end=Time::HiRes::time();
    return($bgn,$end);
}sub print_count{
    my($dbh)=@_;
    my $sth;
    my $sql="select count(*) as cnt from test1";
    $sth=$dbh->prepare($sql);
    $sth->execute;
    while(my $r = $sth->fetchrow_hashref){
        print "$r->{cnt}\n";
    }
    $sth->finish;
}

    my($user,$password,$dsn,$dbh,$sth);
    my($bgn,$end);
    my $cnt=120;
    print "count=$cnt\n";
    $user="tetsu";
    $password="";
    $dsn="dbi:Pg:dbname=test1;host=localhost";

    #with AutoCommit OFF
    $dbh=DBI->connect($dsn, $user,$password,{AutoCommit=>0})or die DBI
+->errstr;;

    ($bgn,$end)=test_insert1($dbh,1000);
    printf("With AutoCommit OFF=%0.3f\n",($end - $bgn));
    #print_count($dbh);

    $dbh->disconnect;

    #with AutoCommit ON
    $dbh=DBI->connect($dsn, $user,$password,{AutoCommit=>1})or die DBI
+->errstr;;

    ($bgn,$end)=test_insert2($dbh,1000);
    printf("With AutoComit ON time=%0.3f\n",($end - $bgn));
    #print_count($dbh);

    $dbh->disconnect;
[download]

If you tune postgres.conf and optimize your PostgreSQL server, the result will change. For instance, if trun fsync option to "off", the result differs.

With AutoCommit OFF=0.224
With AutoComit ON time=0.259
[download]

*THIS OPTION SHOULD BE HANDLED CAREFULLY*. It is very veyr villain to trun off fsync option to off carelessly like me. It is not for accounting thing but it may show you where the problem is. Postgress has many ways for tuning it proper. I hope it helps you to find the problem.

Comment on Re^3: DBI and DBD::Pg: Assessing Performance Select or Download Code