Re: Long running queries degrade performance

On Friday 16 April 2004 4:25 pm, Mike Nolan wrote:
> Given the intermittent nature of the problem and its relative brevity
> (5-10 seconds), I don't know whether top offers the granularity needed to
> locate the bottleneck.

Our long running processes run on the order of multiple minutes (sometimes for
over an hour) and it's expected because the sql can be quite complex over
somewhat large datasets.  But it's the bringing the server to it's knees,
that I'm trying to figure out how to address if we can.  In other words, let
those long running processes run, but somehow still get decent performance
for "quick" requests.

Yours reminds me of what used to happen in our apps back when I worked in java
and the garbage collector kicked in.  Suddenly everything would stop for
10-15s and then continue on.  Sort of makes you think the app froze for some
reason.

> It happens on my development system, and I'm the only one on it.  I know
> I've seen it on the production server, but I think it is a bit more
> common on the development server, though that may be a case of which system
> I spend the most time on.  (Also, the production server is 1300 miles away
> with a DSL connection, so I may just be seeing network delays some of
> the time there.)

Interesting.  Have you tried running a processor monitor and seeing if you are
getting a cpu or disk spike when you get the blips?  Postgres has been pretty
constant for us in it's average runtime for any particular query.  We do get
some fluctuation, but I've always attributed that to other things happening
in the background.  I sometimes run gkrellm off the server just to "see"
what's happening on a macro scale.  It's a great early indicator when we are
getting slammed one way or another (network, memory, processor, disk, etc).
Plus it shows a couple of seconds of history so you can see blips pretty
easily.

> My web app traps double-clicks in javascript and ignores all but the first
> one. That's because some of the users have mice that give double-clicks
> even when they only want one click.

Hmmm, never thought of doing that.  Might be interesting to do something like
that in a few key places where we have problems.

> --
> Mike Nolan

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