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

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