Hi-

I have a few specific questions about wraparound that I'm not finding
answers for in the Docs or list archives. We're a few versions behind
(sorry!) at 7.4.7. Our database is large (100 GB), with most of the rows
concentrated in 5 tables, the largest containing rows.

1) Do we need to do a *full* vacuum or just a "lazy" vacuum to take care of
the xid wraparound issues? The documentation at
http://www.postgresql.org/docs/7.4/i...PAROUNDimplies
that a regular vacuum is all that is needed but is not explicit
about it. We vacuum nightly, but due to availability requirements almost
never do a full vacuum.

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.

3) How can we tell if our attempts to take care of wraparound have worked? I
found a note that this select should tell me if we're in trouble:

SELECT datname, age(datfrozenxid) FROM pg_database;

But after a vaccum of both our prod and the template1 database, I get this
result:

datname | age
-----------+------------
prod | 1074324475
template1 | 1073742599
template0 | 363178963
(3 rows)

From the docs, I learned that after a vacuum, I should see this number at
one billion and get alarmed as I near 2 billion. This results is after about
3 hours of normal activity, and I'm almost one-tenth of the way to 2
billion. This scares me a bit, as it implies that after about 36 hours, I
would hit the wall if I don't run vacuum again.

Or... is it the case that by one billion, the docs really mean 2^30
(1073741824) ?

If the select above is not right, what should I be using to track how close
we are to wraparound problems?

4) The documentation implies that I need to vacuum *every* database, even if
I'm not using them all, so I vacuumed template1, but can't connect to
template0. Do I really need to vacuum databases where no transactions are
occurring? If so, how to I deal with template0?

Thanks.
-Nick
--
------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com
http://www.doxpop.com
765.965.7363
765.962.9788 (Fax)
Doxpop - Public Records at Your Fingertips.

On 8/8/07, Nick Fankhauser <nickf@doxpop.com> wrote:

> the largest containing rows.

Oops- I meant to say "...the largest containing 56 million rows".

One other question- when I'm vacuuming, I always get the warning:

WARNING: some databases have not been vacuumed in <big number> transactions
HINT: Better vacuum them within <big number> transactions, or you may
have a wraparound failure.

I get these even after all DBs except template0 have been freshly vacuumed. Why?

-Nick

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Nick Fankhauser" <nickf@doxpop.com> writes:
> 1) Do we need to do a *full* vacuum or just a "lazy" vacuum to take care of
> the xid wraparound issues?

Lazy is sufficient --- as long as it's database-wide including the
system catalogs (which means a superuser has to do it).

> Or... is it the case that by one billion, the docs really mean 2^30
> (1073741824) ?

2^30. You appear to have executed a bit under 600000 transactions since
vacuuming, so if that's three hours then you've got about 5000 hours
until wraparound.

> 4) The documentation implies that I need to vacuum *every* database, even if
> I'm not using them all, so I vacuumed template1, but can't connect to
> template0. Do I really need to vacuum databases where no transactions are
> occurring? If so, how to I deal with template0?

You don't need to touch frozen databases. This was discussed yesterday ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFGuiu5dO30qud8SkgRAsvdAKCu9N5IcOlqTqi9E7o+uG 14ajNTgwCeKWlE
zxTfKg01R8Rngb9ySyKcCjE=
=s+4N
-----END PGP SIGNATURE-----

"Nick Fankhauser" <nickf@doxpop.com> writes:
> One other question- when I'm vacuuming, I always get the warning:

> WARNING: some databases have not been vacuumed in <big number> transactions
> HINT: Better vacuum them within <big number> transactions, or you may
> have a wraparound failure.

> I get these even after all DBs except template0 have been freshly vacuumed. Why?

Do your vacuums change the pg_database.datfrozenxid values for the
databases? The only reason I can think of for them not to do so
is if you're not doing them as superuser ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 8/8/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Nick Fankhauser" <nickf@doxpop.com> writes:
> > One other question- when I'm vacuuming, I always get the warning:
>
> > WARNING: some databases have not been vacuumed in <big number> transactions
> > HINT: Better vacuum them within <big number> transactions, or you may
> > have a wraparound failure.
>
> > I get these even after all DBs except template0 have been freshly vacuumed. Why?
>
> Do your vacuums change the pg_database.datfrozenxid values for the
> databases? The only reason I can think of for them not to do so
> is if you're not doing them as superuser ...
>
> regards, tom lane
>

They do change the values- I noted this by selecting age(datfrozenxid)
in template1 both before and after vacuuming and noting the change.
Also, the maintenance process that does the regular vacuum on our
"prod" database is run as user postgres.

Our regular vacuum process only runs on our single "prod" database-
not template1. Is template1 a "frozen" database, or just template0? If
template1 is not frozen, that may explain the warning. Would I get the
warning even if the DB that has not been recently vacuumed has never
had any transactions on it?

-Nick


--
------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com
http://www.doxpop.com
765.965.7363
765.962.9788 (Fax)
Doxpop - Public Records at Your Fingertips.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Nick Fankhauser" <nickf@doxpop.com> writes:
> On 8/8/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Do your vacuums change the pg_database.datfrozenxid values for the
>> databases? The only reason I can think of for them not to do so
>> is if you're not doing them as superuser ...

> They do change the values- I noted this by selecting age(datfrozenxid)
> in template1 both before and after vacuuming and noting the change.

Uh, that proves very little, because age() is a moving target. Did the
raw datfrozenxid column values change?

> Our regular vacuum process only runs on our single "prod" database-
> not template1. Is template1 a "frozen" database, or just template0?

template1 is not frozen in a standard installation. Basically you gotta
vacuum everything that has datallowconn = true.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thanks, both Tom and Jim for the information. That's exactly what I
needed to know.

Jim-

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?


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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFGu3pydO30qud8SkgRAm4GAJ4xrC3CYi3VmprUAVe7Tf GaicMRvgCeJhX0
YSD/bdL/swnYUvWaUBGZbI4=
=55JY
-----END PGP SIGNATURE-----