Обсуждение: Specific questions about wraparound and vacuum

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/interactive/maintenance.html#VACUUM-FOR-WRAPAROUND implies 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

"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

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

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.

"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

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.

Hi,

thanks alot for your answers.

Yesterday I have updated the kernel. After a reboot, the ECC-Kernel error still appears. Then I've cleaned
the RAM-Slots with nose-paper :), rebooted the machine, and the error was away! Unbelievable, the error
was before on both the machines.

Yesterday night, i've switched my Slony-replication and the IP address. Right now, thanks to god, I'm on a
safe side. :)

Now i will clean the old production machine :) and putting it back to the replication. :)

Over again, thanks very much, I like PostgreSQL and the Community, it's a very very good project :)!

Regards,

Martin

-----Ursprüngliche Nachricht-----
Von: Tom Lane <tgl@sss.pgh.pa.us>
Gesendet: 08.08.07 18:36:09
An: "Nick Fankhauser" <nickf@doxpop.com>
CC: pgsql-admin@postgresql.org
Betreff: Re: [ADMIN] Specific questions about wraparound and vacuum


"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



_______________________________________________________________________
Jetzt neu! Schützen Sie Ihren PC mit McAfee und WEB.DE. 3 Monate
kostenlos testen. http://www.pc-sicherheit.web.de/startseite/?mc=022220

Hi,

thanks alot for your answers.

Yesterday I have updated the kernel. After a reboot, the ECC-Kernel error still appears. Then I've cleaned
the RAM-Slots with nose-paper :), rebooted the machine, and the error was away! Unbelievable, the error
was before on both the machines.

Yesterday night, i've switched my Slony-replication and the IP address. Right now, thanks to god, I'm on a
safe side. :)

Now i will clean the old production machine :) and putting it back to the replication. :)

Over again, thanks very much, I like PostgreSQL and the Community, it's a very very good project  :)!

Regards,

Martin
_______________________________________________________________________
Jetzt neu! Schützen Sie Ihren PC mit McAfee und WEB.DE. 3 Monate
kostenlos testen. http://www.pc-sicherheit.web.de/startseite/?mc=022220

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)