pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Kratz <chris.kratz@vistashare.com>
To: pgsql-performance@postgresql.org
Cc: Mike Nolan <nolan@gw.tssi.com>
Subject: Re: Long running queries degrade performance
Date: Fri, 16 Apr 2004 16:51:29 -0400
Message-ID: <200404161651.29332.chris.kratz@vistashare.com> (raw)
In-Reply-To: <200404162025.i3GKPDe4013710@gw.tssi.com>
References: <200404162025.i3GKPDe4013710@gw.tssi.com>

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



view thread (8+ messages)  latest in thread

Message-ID: <200404161651.29332.chris.kratz@vistashare.com>
Permalink:  ../200404161651.29332.chris.kratz@vistashare.com/
Also on:    postgresql.org/message-id/200404161651.29332.chris.kratz@vistashare.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: chris.kratz@vistashare.com, nolan@gw.tssi.com
  Subject: Re: Long running queries degrade performance
  In-Reply-To: <200404161651.29332.chris.kratz@vistashare.com>
  References: <200404162025.i3GKPDe4013710@gw.tssi.com> <200404161651.29332.chris.kratz@vistashare.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox