pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Decibel! <decibel@decibel.org>
To: Nick Fankhauser <nickf@doxpop.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Specific questions about wraparound and vacuum
Date: Thu, 9 Aug 2007 15:34:58 -0500
Message-ID: <20070809203458.GX20424@nasby.net> (raw)
In-Reply-To: <bb2fe60f0708081855j754052c4od5f1f8e6dec08402@mail.gmail.com>
References: <bb2fe60f0708080907x1d8aa6dct5aa3cb1e1ba15897@mail.gmail.com>
	<20070808204649.GQ20424@nasby.net>
	<bb2fe60f0708081855j754052c4od5f1f8e6dec08402@mail.gmail.com>

On Wed, Aug 08, 2007 at 09:55:54PM -0400, Nick Fankhauser wrote:
> We did in fact just increase the fsm values significantly based on the
> feedback we were getting from the vacuum messages. We do nightly
> non-full vacuums. Am I to understand that if we increase our fsm
> allocation to a sufficient size, we should not be losing any space?

Correct, though of course you could build up substantial bloat during
the day.

Back when I was running a 7.4 database, I made use of pg_autovacuum from
contrib to help keep things in hand, but you might not be able to handle
vacuums firing off at random times without vacuum cost delay. Vacuuming
key tables more frequently via cron might be a better strategy, but if
you do want to run pg_autovac I've got a script that will help.

BTW, http://decibel.org/~decibel/pervasive/fsm.html is something I wrote
while at Pervasive that explains how the FSM works.

> The "modern version" upgrade is on our wish list, but as it's a
> production system incorporating many technologies, we've had
> priorities elsewhere for a while, and 7.4 has been so darn stable and
> productive that the only motivation to move forward is so I don't have
> to feel ashamed to admit how far back we are. On the bright side, it's
> an indication of how good postgresql is that a growing business has
> had no issues with a quite old version.
> 
> Regards,
>          -Nick
> 
> 
> On 8/8/07, Decibel! <decibel@decibel.org> wrote:
> > On Wed, Aug 08, 2007 at 12:07:14PM -0400, Nick Fankhauser wrote:
> > > 2) If a regular (non-full) vacuum will not reset the XID. Will a
> > > dump/restore take care of wraparound? We have done this in the past for
> > > space reclamation because we seem to be able to dump/restore more quickly
> > > than we can do a full vacuum.
> >
> > If you're doing that you need to re-evaluate your vacuuming strategy and
> > possibly your free space map settings. You should normally never need to
> > use pg_dump(all) or vacuum full to reclaim space.
> >
> > If you've got the ability to take enough downtime to dump and restore,
> > you should really use that opportunity to upgrade to a modern version,
> > too.
> > --
> > Decibel!, aka Jim Nasby                        decibel@decibel.org
> > EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> >
> >
> 
> 
> -- 
> ------------------------------------------------------------------
> Nick Fankhauser
> nickf@doxpop.com
> http://www.doxpop.com
> 765.965.7363
> 765.962.9788 (Fax)
> Doxpop - Public Records at Your Fingertips.
> 

-- 
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

view thread (11+ messages)

Message-ID: <20070809203458.GX20424@nasby.net>
Permalink:  ../20070809203458.GX20424@nasby.net/
Also on:    postgresql.org/message-id/20070809203458.GX20424@nasby.net

 · 

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-admin@postgresql.org
  Cc: decibel@decibel.org, nickf@doxpop.com
  Subject: Re: Specific questions about wraparound and vacuum
  In-Reply-To: <20070809203458.GX20424@nasby.net>

* 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