Re: DBI & DBD::Pg processor load - Mailing list pgsql-general

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@turnstep.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@postgresql.org so that your
> message can get through to the mailing list cleanly

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