Re: DBD::Pg large processor load. - Mailing list pgsql-interfaces

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@remotelinux.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