Message-ID: <20030422023037.29655.qmail@web13807.mail.yahoo.com>
Date: Mon, 21 Apr 2003 19:30:37 -0700 (PDT)
From: Chris Gamache <cgg007@yahoo.com>
Reply-To: cgg007@yahoo.com
Subject: Re: DBD::Pg large processor load.
To: Rudy Lippan <rlippan@remotelinux.com>
Cc: pgsql-interfaces@postgresql.org
In-Reply-To: <Pine.LNX.4.44.0304211440000.4221-100000@elfride.ineffable.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


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) 

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. 

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! 

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!

Thanks for the response. Let me know if I've sparked a clue or two...

--- Rudy Lippan <rlippan@remotelinux.com> wrote:
> On Fri, 18 Apr 2003, 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. However, it is no
> > longer supported, and was causing some problems. So! I switched to DBI. 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?
> > 
> 
> How much of a processor load are you talking about? Is it a 2%, 20%, 200%
> increase in processor usage?
> 
> 1. Does PgSQL::Cursor uses Cursors?
> 2. Do you have code that shows the problem?
> 
> 
> -r
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


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