Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 938579FB7D1 for ; Thu, 9 Aug 2007 17:35:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 40338-02 for ; Thu, 9 Aug 2007 17:35:17 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 683A69FB1EF for ; Thu, 9 Aug 2007 17:35:17 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 014C8564B7; Thu, 9 Aug 2007 15:35:00 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Thu, 9 Aug 2007 15:34:58 -0500 Date: Thu, 9 Aug 2007 15:34:58 -0500 From: Decibel! To: Nick Fankhauser Cc: pgsql-admin@postgresql.org Subject: Re: Specific questions about wraparound and vacuum Message-ID: <20070809203458.GX20424@nasby.net> References: <20070808204649.GQ20424@nasby.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="Ut0KX4oafLh8aNyr" Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:070809:nickf@doxpop.com::vsk/WhObosrsMLk6:06DQK X-Hashcash: 1:20:070809:pgsql-admin@postgresql.org::zoedQub4SfCEUWR9:00000000000 00000000000000000000000041aA X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/111 X-Sequence-Number: 26442 --Ut0KX4oafLh8aNyr Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable 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. >=20 > Regards, > -Nick >=20 >=20 > On 8/8/07, Decibel! 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 f= or > > > space reclamation because we seem to be able to dump/restore more qui= ckly > > > 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) > > > > >=20 >=20 > --=20 > ------------------------------------------------------------------ > Nick Fankhauser > nickf@doxpop.com > http://www.doxpop.com > 765.965.7363 > 765.962.9788 (Fax) > Doxpop - Public Records at Your Fingertips. >=20 --=20 Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) --Ut0KX4oafLh8aNyr Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.3 (FreeBSD) iD8DBQFGu3pydO30qud8SkgRAm4GAJ4xrC3CYi3VmprUAVe7TfGaicMRvgCeJhX0 YSD/bdL/swnYUvWaUBGZbI4= =55JY -----END PGP SIGNATURE----- --Ut0KX4oafLh8aNyr--