Thread: DBI & DBD::Pg processor load
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
-----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. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200304210953 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE+o/fZvJuQZxSWSsgRAsk4AJ923kvOGAC36Y/vFLl8qF0yx+Nm6QCeI3PP wwmX1x/fb7S38wBzmpfAqvo= =cuuV -----END PGP SIGNATURE-----
At 01:49 AM 4/21/2003 -0700, Chris Gamache wrote: >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. Not expert, but is it possible to try a plain $rs->fetchrow instead of fetchrow_arrayref? Link.
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
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