[Zope-DB] PostgreSQL load problem

[Ian Bicking]

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