Re: DBD::Pg large processor load.

Thank you very much for the response. I'll follow your lead and CC to dbi-users
as well.

That sample code never did make it to google... Here it is:

[------TABLE SCHEMA-----]

CREATE TABLE queue (
id int4 DEFAULT nextval('"queue_id_seq"'::text) NOT NULL,
my_type varchar(50),
my_data varchar(50),
status int8 DEFAULT 0,
CONSTRAINT queue_id_key UNIQUE (id)
) WITH OIDS;

[---END TABLE SCHEMA----]

[----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----]

I'm really not "preparing" any of my statements... It seemed that in my
conversion to DBI, prepare() was a required formality. What is the alternative
to executing a pre-prepared SQL statement other than prepare; execute;?

I'm also not cleaning up after my non-row-returning SQL executes. Should I be
cleaning them up somehow?

Here are the things on my list to attempt:
1. Use $dbi->trace(9)
2. Convert $sth->fetchrow_arrayref() to $sth->fetchall_arrayref() and see what
happens.
3. Look into the DBI Profiler you mentioned...

Any other thoughts?

CG

--- Rudy Lippan <rlippan(at)remotelinux(dot)com> wrote:
> On Mon, 21 Apr 2003, Chris Gamache wrote:
>
> CCing dbi-users.
>
> > Procps reports that the 6 simultaneous postmasters (spawned by 6 instances
> of
> > the same perl script) each have 20% load with DBI over 6% load each for 6
> > postmasters with PgSQL::Cursor. (Running Dual Xeons at 2GHz apiece w/o
> > hyperthreading)
>
> What type of load were you seeing with PgSQL?
> >
> > I can't seem to reproduce the problem with test code (posted in
> pgsql-general
> > with a similar thread. Hasn't made it to google yet, otherwise I'd include
> a
> > link) I think it has to do with the large dataset it is dealing with.
> >
> Sample could would be nice ;)
>
> > As far as PgSQL::Cursor using cursors, I guess that might be the case. I
> was
> > hoping someone would suggest using a different DBI method or DBI setting,
> to
> > tone down DBI's (perceived) voracity for processor time!
>
> It does not seem to. In fact PgSQL does not seem to do much at all --
> which might explain why it is faster in some things; BTA, much of
> DBI & DBD::Pg are written in C which "should" make them faster :)
>
> >
> > PgSQL::Cursor was alpha software when I started using it. It was simple to
> > impliment, and did the trick. It was eclipsed by DBI and as a result was
> not
> > updated. PgSQL.pm didn't cause a problem until recently. Porting to DBI was
> > simple: different method calls yielding the same results. The only
> difference
> > that I can see is the extra load. I've never gone digging in the sources,
> so
> > I'm not sure how the two differ.
> >
> > I'll settle for extra processor load as a trade off for stability. I was
> hoping
> > there would be a well-known and quick fix. My next task is to turn on some
> > verbose debug logs to perhaps shed some light on what's going on, and let
> me
> > know why I can't reproduce this with my test code!
> >
> 1. You might want to try $sth->fetchall_arrayref(). This will suck
> everthing into one big arrayref; which is not as bad as it sounds because,
> if you are not using cursors, all of the data are returned by postgres and
> fetch() just gives you the next row. Of course this will cause a
> momentary increase in memory usage because you will have to duplicate all
> of the rows instead of one at a time. -- I would be interested in seing if
> this causing any increase in performance?
>
> 2. If you are doing many prepare()s/execute()s, You might want to try
> runing the code out of CVS. DBD::Pg has to preparse the statement that you
> give it changing '?' into :p1 and allocating memory and data structs for
> handling placeholders. The preparse code for prepare()/execute() in CVS
> is much leaner that what is in 1.22. Also try and keep prepare()s out of
> loops, this will save a walk or two of the statement.
>
> 3. if memory usage is a problem try using DECLARE CURSOR and FETCH; this
> will give you a fetchrow_arrayref() that takes much less memory
> (depending on the size of the dataset), will use more CPU time. [I know
> you are worried about CPU time]
>
> 4. One way to get some pretty large performance increases if you are
> executing() in a tight loop is to use postgres's server side prepare. The
> Code in CVS can do this for you automatically; however the way it is
> writtin, it breaks anything that that cannot be prepared like PREPARE
> 'DBD::Pg Statement 1' (varchar,varchar,varchar) AS SELECT * FROM t1 WHERE
> a=$1 and b = $2, and c=$3. Basically the (varchars) will cause some SQL
> statments to break (eg. WHERE foo BETWEEN $1 AND $2). Right now CVS head
> has this feature disabled because it breaks too many things, but if you
> want I can make it configurable?
>
>
> You also might want to look at the DBI profiler.
>
> If you are interested in seeing what DBI is doing try taking a look at the
> output of DBI->trace(9).
>
> -r
>

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com