[Zope-DB] Re: PostgreSQL load problem

[Casey Duncan]

One helpful thing I often do (which may or may not help but...) is add a
strategic print statement (or zLOG.LOG) inside the DA to tell me when it
sends a SQL comman to the database (and what the sql is). It can
sometimes be surprising how many queries you are actually making from
Zope. 

It would also tell you about any transactional interactions between Zope
and the DB. ZODB conflict errors are already logged, so you can see them
interlaced with SQL sent over to the db server.

Anyway at least it gives you some live info.

-Casey

On Thu, 08 Apr 2004 14:33:20 -0500
Ian Bicking <ianb at colorstudy.com> wrote:

> I think I don't understand what's happening in my application, moreso 
> after recent discussions.
> 
> Anyway, we have an application that hits the database fairly hard even
> 
> with a small number of users, but nothing it shouldn't be able to 
> handle.  We've had a problem lately when there's several concurrent 
> users, and an update to the database occurs.  A Postgres (7.2.4)
> process will go to 90% CPU usage, and take about 10 minutes to resolve
> itself -- but eventually it goes back to normal, and even seems to
> execute the original query.  It's really not a very big database --
> the largest table is ~6000 rows -- so I feel it has to be some weird
> conflict. Maybe it's related to all the side effects of an insert,
> which causes the transaction to be held open for at least a few
> seconds while other things happen.  (I thought I'd try to put in a
> commit in half way throught the Zope request, so that the database
> transaction wouldn't be help up for the rest of my calculations, but I
> guess that's not a very good idea)
> 
> I figured it was something in my SQL, but lately (uh, yesterday) I 
> started to think it might have something to do with what Zope is doing
> 
> (somewhere, somehow), or some nasty interaction involving all the 
> various pieces of the application.  It's a nasty problem and I'm not 
> expecting anyone to debug it for me, but I'm hoping something in this 
> will sound familiar and they can give me an idea about what I should
> be looking for.  Because honestly, I have no idea where else to look
> for the causes.
> 
> (inevitably, turning up logging on PG seems to eliminate the problem, 
> either because it slows down the database significantly, or because
> the logging has other side effects)
> 
> Thanks,
>    Ian