Re: Long running queries degrade performance

Fairly sure, when it is happening, postgres usually is taking up the top slots
for cpu usage as reported by top.  Perhaps there is a better way to monitor
this?

The other thing for us is that others talk about disks being the bottleneck
whereas for us it is almost always the processor.  I expected the drives to
kill us early on (we have two uw scsi mirrored drives) but there is very
little disk activity.  The disks rarely do much during load for us (at this
point).  Most likely this is related more to data volume at this point.

As far as in your case, is there a lot of disk activity happening?  More
likely you have a situation where something else is happening which blocks
the current thread.  We ran into two situations recently which exhibited this
behavior.  One was adding and dropping tables in a transaction which blocks
any other transaction trying to do the same.  And two threads inserting
records with the same primary key value blocks the second till the first
finishes.  Both of these were triggered by users double clicking links in our
web app and were fixed by a better implementation.  Perhaps something like
that is causing what you are seeing.

-Chris

On Friday 16 April 2004 11:46 am, Mike Nolan wrote:
> > We have a web app with a postgres backend.  Most queries have subsecond
> > response times through the web even with high usage.  Every once in
> > awhile someone will run either an ad-hoc query or some other long running
> > db process.
>
> Are you sure it is postgres where the delay is occurring?  I ask this
> because I also have a web-based front end to postgres, and while most of
> the time the queries respond in about a second every now and then I see
> one that takes much longer, sometimes 10-15 seconds.
>
> I've seen this behavior on both my development system and on the
> production server.
>
> The same query a while later might respond quickly again.
>
> I'm not sure where to look for the delay, either, and it is intermittent
> enough that I'm not even sure what monitoring techniques to use.
> --
> Mike Nolan

--
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com