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

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