Re: DBI & DBD::Pg processor load

Hi,

I don't know the internals of DBD::Pg, but I know some internals about the
underlying c-interface.

It looks like executing the select feches all 100,000 records at once. You
should use a cursor for that.

$dbh->do('BEGIN WORK'); # you need to start a transaction for a cursor
$dbh->do("DECLARE cursor1 CURSOR $mysql_get");
my $sth_fetch = $dbh->prepare('FETCH 100 FROM cursor1');
while(my $res = $dbh->selectall_arrayref($sth_fetch))
{
foreach (@$res)
{
my @row = @$_;
...do something with your data
}
}
$dbh->do('COMMIT'); # or ROLLBACK

By the way: You don't need to lock your table to update. Update does it for
you. And there is no need to start a transaction for a single
update-statement. And passing perl-variables as bind-values is better,
because DBI/DBD escapes your values automatically.

$dbh->do('UPDATE queue
set status=?
WHERE id = (SELECT min(id)
FROM queue
WHERE status=0', {}, $procid);

Tommi

Am Montag, 21. April 2003 17:12 schrieb Chris Gamache:
> Its interesting: the perl process doesn't soak up a huge amount of
> processor time, but the postmaster connection created by DBI does. My queue
> table is indexed, and my queue table is large (100,000 records). It is
> fully vacuumed and analyized every night... I haven't turned on debugging
> yet, but that's my next thing to try. I also might try archiving the table
> at 10000 rows, but PgSQL::Cursor didn't have a problem with the 100,000 row
> queue...
>
> [----BEGIN TEST CODE----]
>
> #!/usr/local/bin/perl
>
> #
> # Set process id...
> #
>
> my $procid = $$;
>
> use DBI;
>
> my $rs;
> my $dbh = DBI->connect("dbi:Pg:dbname=mydb","myuser","mypass");
>
> my $finished = false;
>
> while ($finished ne true) {
>
> #
> # Begin Transaction...
> #
>
> #
> # Claim a record for ourselves
> #
> my $mysql_update = <<EOS;
> BEGIN;
> LOCK TABLE queue IN EXCLUSIVE MODE;
> UPDATE queue
> set status=$procid
> WHERE id = (SELECT min(id)
> FROM queue
> WHERE status=0
> );
> COMMIT;
> EOS
> my $rs_update = $dbh->prepare($mysql_update);
> $rs_update->execute;
>
> #
> # Get the row from the batch_trans_queue
> #
> my $mysql_get = <<EOS;
> SELECT id, my_type, my_data
> FROM queue
> WHERE status=$procid;
> EOS
> my $rs_get = $dbh->prepare($mysql_get);
> $rs_get->execute;
>
> #
> # We got a record...
> #
>
> while ($row_get = $rs_get->fetchrow_arrayref) {
> #
> # Get queue values
> #
> my @row = @$row_get if $row_get;
> my $id = @row[0];
> my $my_type = @row[1];
> my $my_data = @row[2];
>
> print "VALUES: $my_type $my_data\n";
>
> #
> # Set record completed
> #
> $mysql_update = "update queue set status=1 where id=$id;";
> $rs_update = $dbh->prepare($mysql_update);
> $rs_update->execute;
> }
> $rs_get->finish;
> sleep(1);
> }
>
> #
> # disconnect from server
> #
> $dbh->disconnect;
>
> [----END TEST CODE----]
>
> CG
>
> --- greg(at)turnstep(dot)com wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > > Are there any DBI experts out there with some advice to cut down
> > > on processor usage?
> >
> > Very hard to say without knowing exactly what is going on. Can you
> > break it down into a simple case that you can post here? You might
> > also want to bump up the trace level "$dbh->trace(2)" and see if
> > that helps give an insight as to what is going on.
> >
> >>Linux 2.4.20 & PostgreSQL 7.2.3 & DBD::Pg 1.22.
> >>
> >>I was using PgSQL and PgSQL::Cursor with decent results. It is no
> >>longer supported, and was causing some strange problems. So, I switched
> >> to DBI
> >>with no problems to speak of. However, I immediately noticed a jump in
> >>processor usage. I primarily use
> >>$db->prepare($sql), $rs->execute, and $rs->fetchrow_arrayref.
> >>
> >>Are there any DBI experts out there with some advice to cut down on
> >>processor
> >>usage?
> >>
> >>CG
>
> __________________________________________________
> Do you Yahoo!?
> The New Yahoo! Search - Faster. Easier. Bingo
> http://search.yahoo.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Dr. Eckhardt + Partner GmbH
http://www.epgmbh.de