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 13:56:20 -0400
Message-ID: <200404161356.20210.chris.kratz@vistashare.com> (raw)
In-Reply-To: <200404161546.i3GFk3p3010490@gw.tssi.com>
References: <200404161546.i3GFk3p3010490@gw.tssi.com>

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




view thread (8+ messages)  latest in thread

Message-ID: <200404161356.20210.chris.kratz@vistashare.com>
Permalink:  ../200404161356.20210.chris.kratz@vistashare.com/
Also on:    postgresql.org/message-id/200404161356.20210.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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: chris.kratz@vistashare.com, nolan@gw.tssi.com
  Subject: Re: Long running queries degrade performance
  In-Reply-To: <200404161356.20210.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