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

Andrew Sullivan wrote:
> Sorry I haven't had a chance to reply to this sooner.

> 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 discussed it later in the thread, but we're adding about 400K rows per 
hour and deleting most of them after processing (note this is a 
commercial app, written and maintained by another department -- I can 
recommend changes, but this late into their release cycle they are very 
reluctant to change the app). This is 7 x 24 data collection from 
equipment, so there is no "slow" time to use as a maintenance window.

But since the server in question is a test machine, I was able to shut 
everything off long enough to do a full vacuum -- it took about 12 hours.

> 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.

I'm (reasonably) sure there is no swapping. Minimum free memory (from 
top) is about 800 MB, and "vmstat -S" shows no swap-in or swap-out.

I've been playing with a version of Jan's performance patch in the past 
few hours. Based on my simulations, it appears that a 1 ms delay every 
10 pages is just about right. The performance hit is negligible (based 
on overall test time, and cpu % used by the vacuum process). I still 
have a bit more analysis to do, but this is looking pretty good. More 
later...

Joe



view thread (26+ messages)  latest in thread

Message-ID: <4058A4C8.7090108@joeconway.com>
Permalink:  ../4058A4C8.7090108@joeconway.com/
Also on:    postgresql.org/message-id/4058A4C8.7090108@joeconway.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: mail@joeconway.com, ajs@crankycanuck.ca
  Subject: Re: rapid degradation after postmaster restart
  In-Reply-To: <4058A4C8.7090108@joeconway.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