pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: rapid degradation after postmaster restart
Date: Wed, 17 Mar 2004 13:11:15 -0500
Message-ID: <20040317181115.GA7805@phlogiston.dyndns.org> (raw)
In-Reply-To: <4052661D.5060505@joeconway.com>
References: <4052661D.5060505@joeconway.com>

Sorry I haven't had a chance to reply to this sooner.

On Fri, Mar 12, 2004 at 05:38:37PM -0800, Joe Conway wrote:
> The problem is this: the application runs an insert, that fires off a 
> trigger, that cascades into a fairly complex series of functions, that 
> do a bunch of calculations, inserts, updates, and deletes. Immediately 
> after a postmaster restart, the first insert or two take about 1.5 
> minutes (undoubtedly this could be improved, but it isn't the main 
> issue). However by the second or third insert, the time increases to 7 - 
> 9 minutes. Restarting the postmaster causes the cycle to repeat, i.e. 
> the first one or two inserts are back to the 1.5 minute range.

The vacuum delay stuff that you're working on may help, but I can't
really believe it's your salvation if this is happening after only a
few minutes.  No matter how much you're doing inside those functions,
you surely can't be causing so many dead tuples that a vacuum is
necessary that soon.  Did you try not vacuuming for a little while to
see if it helps?

I didn't see it anywhere in this thread, but are you quite sure that
you're not swapping?  Note that vmstat on multiprocessor Solaris
machines is not notoriously useful.  You may want to have a look at
what the example stuff in the SE Toolkit tells you, or what you get
from sar.  I believe you have to use a special kernel setting on
Solaris to mark shared memory as being ineligible for swap.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
		--Dennis Ritchie



view thread (26+ messages)  latest in thread

Message-ID: <20040317181115.GA7805@phlogiston.dyndns.org>
Permalink:  ../20040317181115.GA7805@phlogiston.dyndns.org/
Also on:    postgresql.org/message-id/20040317181115.GA7805@phlogiston.dyndns.org

 · 

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: ajs@crankycanuck.ca
  Subject: Re: rapid degradation after postmaster restart
  In-Reply-To: <20040317181115.GA7805@phlogiston.dyndns.org>

* 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