We currently use postgres 8.1.x and run the following query
periodically to check for XID wraparound proximity.

select datname, age(datfrozenxid) from pg_database



What is the equivalent check in 8.4

Thank you

Sriram

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--=-jEHWXPbuJSg2d4OK0aMc
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, 2009-08-11 at 14:48 -0700, Anj Adu wrote:
> What is the equivalent check in 8.4

Did you try in on 8.4?

FWIW, PostgreSQL 8.3+ will start autovacuum (even if it is turned off)
if a database is approaching XID wraparound, and get rid of the
problem-- so you don't actually need to check it.
--
Devrim G=C3=9CND=C3=9CZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

--=-jEHWXPbuJSg2d4OK0aMc
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkqB6kEACgkQtl86P3SPfQ5y/wCg16XYubbujuEfI58048T1 oaEK
5NYAoOXfJ1hKPJzJvKE9K0VNf77ULCBP
=6MaT
-----END PGP SIGNATURE-----

--=-jEHWXPbuJSg2d4OK0aMc--

Anj Adu escribi=F3:
> We currently use postgres 8.1.x and run the following query
> periodically to check for XID wraparound proximity.
>
> select datname, age(datfrozenxid) from pg_database
>
>
>
> What is the equivalent check in 8.4

Same.

--
Alvaro Herrera http://www.CommandPrompt.co=
m/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Devrim G=DCND=DCZ escribi=F3:

> FWIW, PostgreSQL 8.3+ will start autovacuum (even if it is turned off)
> if a database is approaching XID wraparound, and get rid of the
> problem-- so you don't actually need to check it.

8.1 does it too. The main difference is that 8.1 will run a
database-wide vacuum, whereas in 8.2 and up it only vacuum tables that
have not been vacuumed recently.

--
Alvaro Herrera http://www.CommandPrompt.co=
m/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

So..we dont have to check the last XID value per table ?

we have a very high volume data warehouse for which autovacuum is not
suitable due to performance reasons. Can we track the last XID on a
per-table basis ?

2009/8/11 Alvaro Herrera <alvherre [at] commandprompt.com>:
> Devrim G=DCND=DCZ escribi=F3:
>
>> FWIW, PostgreSQL 8.3+ will start autovacuum (even if it is turned off)
>> if a database is approaching XID wraparound, and get rid of the
>> problem-- so you don't actually need to check it.
>
> 8.1 does it too. =A0The main difference is that 8.1 will run a
> database-wide vacuum, whereas in 8.2 and up it only vacuum tables that
> have not been vacuumed recently.
>
> --
> Alvaro Herrera =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

2009/8/11 Anj Adu <fotographs [at] gmail.com>:
> So..we dont have to check the last XID value per table ?
>
> we have a very high volume data warehouse for which autovacuum is not
> suitable due to performance reasons. Can we track the last XID on a
> per-table basis ?

autovacuum is highly tunable so as to remove the burden of running it
and having it suck up all your IO mid day. Are you saying that no
amount of autovacuum tuning can fix the overhead issues of autovac, or
that you've just decided not to use it on principle?

Assuming you do the load at night, vacuum after load, no updates
during the day, I can totally see just turning off autovacuum, but
sometimes it nice to leave it on set to some very low load (i.e.
autovacuum_vacuum_cost_delay ms) so that should you forget about
some table, you won't get caught out by table bloat but also won't
have autovacuum killing IO midday.

Just a thought.

Either way, autovacuum WILL kick in if it has to to fix a wrap around
issue even if it's turned off.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Anj Adu escribi=F3:
> So..we dont have to check the last XID value per table ?
>
> we have a very high volume data warehouse for which autovacuum is not
> suitable due to performance reasons. Can we track the last XID on a
> per-table basis ?

Sure, see pg_class.relfrozenxid

--
Alvaro Herrera http://www.CommandPrompt.co=
m/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Reason we dont turn on autovacuum is that we are a "high-volume"
insert shop with minimal updates..We have about 200 million inserts
and a few thousand updates only. Most tables are partitions and get
dropped as part of the purge. Hence..autovacuum is a waste of
resources. However...the XID issue will force the need for an
autovacuum at some point..hence we do it as a one-off occasionally.

2009/8/11 Scott Marlowe <scott.marlowe [at] gmail.com>:
> 2009/8/11 Anj Adu <fotographs [at] gmail.com>:
>> So..we dont have to check the last XID value per table ?
>>
>> we have a very high volume data warehouse for which autovacuum is not
>> suitable due to performance reasons. Can we track the last XID on a
>> per-table basis ?
>
> autovacuum is highly tunable so as to remove the burden of running it
> and having it suck up all your IO mid day. =A0Are you saying that no
> amount of autovacuum tuning can fix the overhead issues of autovac, or
> that you've just decided not to use it on principle?
>
> Assuming you do the load at night, vacuum after load, no updates
> during the day, I can totally see just turning off autovacuum, but
> sometimes it nice to leave it on set to some very low load (i.e.
> autovacuum_vacuum_cost_delay=3D20ms) so that should you forget about
> some table, you won't get caught out by table bloat but also won't
> have autovacuum killing IO midday.
>
> Just a thought.
>
> Either way, autovacuum WILL kick in if it has to to fix a wrap around
> issue even if it's turned off.
>

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Anj Adu escribi=F3:
> Reason we dont turn on autovacuum is that we are a "high-volume"
> insert shop with minimal updates..We have about 200 million inserts
> and a few thousand updates only. Most tables are partitions and get
> dropped as part of the purge. Hence..autovacuum is a waste of
> resources. However...the XID issue will force the need for an
> autovacuum at some point..hence we do it as a one-off occasionally.

Hmm, in 8.2 you won't need to vacuum any tables that you drop or
truncate. Only permanent tables will need to be vacuumed once in a
while.

--
Alvaro Herrera http://www.CommandPrompt.co=
m/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Rob Newton escribi=F3:
> Alvaro Herrera wrote:
> >Devrim G=DCND=DCZ escribi=F3:
> >
> >>FWIW, PostgreSQL 8.3+ will start autovacuum (even if it is turned off=
)
> >>if a database is approaching XID wraparound, and get rid of the
> >>problem-- so you don't actually need to check it.
> >
> >8.1 does it too. The main difference is that 8.1 will run a
> >database-wide vacuum, whereas in 8.2 and up it only vacuum tables that
> >have not been vacuumed recently.
>
> Alvaro, we had an 8.1 server that did not start autovacuum when XID
> wraparound was approaching. Rather, it just stopped performing
> transactions, and returned the usual XID wraparound warning.

My guess is that it did start but it failed to do useful work because of
some bug that caused it to die prematurely. There were bugs in early
8.1 that precluded autovacuum from working, so this is not an idle
hypothesis. (The worst part of this story is that the bug would be fire
at some point but the effect could go unseen for months, even after you
installed the patched version.)

If those autovacuum failures go ignored long enough, you get into the
I-don't-want-to-do-anything-until-you-caress-me mode.

--
Alvaro Herrera http://www.CommandPrompt.co=
m/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Alvaro Herrera wrote:
> Devrim G=DCND=DCZ escribi=F3:
>
>> FWIW, PostgreSQL 8.3+ will start autovacuum (even if it is turned off)
>> if a database is approaching XID wraparound, and get rid of the
>> problem-- so you don't actually need to check it.
>
> 8.1 does it too. The main difference is that 8.1 will run a
> database-wide vacuum, whereas in 8.2 and up it only vacuum tables that
> have not been vacuumed recently.
>

Alvaro, we had an 8.1 server that did not start autovacuum when XID
wraparound was approaching. Rather, it just stopped performing
transactions, and returned the usual XID wraparound warning.
- Rob

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Assuming that autovacuum is off in 8,2 and upwards versions, would I
still have to do a database-wide vacuumdb OR would vacuuming
individual tables that are permanent be sufficient to take care of XID
wraparound?

2009/8/11 Alvaro Herrera <alvherre [at] commandprompt.com>:
> Anj Adu escribi=F3:
>> Reason we dont turn on autovacuum is that we are a "high-volume"
>> insert shop with minimal updates..We have about 200 million inserts
>> and a few thousand updates only. Most tables are partitions and get
>> dropped as part of the purge. Hence..autovacuum is a waste of
>> resources. However...the XID issue will force the need for an
>> autovacuum at some point..hence we do it as a one-off occasionally.
>
> Hmm, in 8.2 you won't need to vacuum any tables that you drop or
> truncate. =A0Only permanent tables will need to be vacuumed once in a
> while.
>
> --
> Alvaro Herrera =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Anj Adu <fotographs [at] gmail.com> writes:
> Assuming that autovacuum is off in 8,2 and upwards versions, would I
> still have to do a database-wide vacuumdb OR would vacuuming
> individual tables that are permanent be sufficient to take care of XID
> wraparound?

In recent releases it is not possible to turn off autovacuum to the
extent of preventing it from doing anti-wraparound vacuuming, so your
question is a bit mis-posed. But yes, you do need a database-wide
manual vacuum if you are trying to forestall automatic anti-wraparound
vacuuming. Vacuuming individual tables isn't sufficient unless you get
*every single one*, including the system catalogs.

In practice, I think worrying about this is pointless in modern PG.
If you want control over the timing of vacuuming on individual large
tables, do them when you want to. The system will occasionally force
vacuums on small tables to prevent wraparound, but that isn't going
to cause you any performance problems.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

We have a few 8.1 installations where the vacuumdb -a command takes
2-3 days to run ..(with a vacuum delay of 10ms)...autovac does not
work for us as we have tables that get constantly dropped due to
partitioning.(autovac would never finish given the size of our
database and the fact that we have some "idle transactions" caused by
our application server coneection pools.)

We have tables that get dropped every day (partitions) and we have
some big ones that dont (the total table sizes range from 2G to 20G
per table for many tables)..

If we manually schedule vacuums on these large permanent tables..will
a one-time VACUUM in the future be smart to figure out how much
vacuuming has been done and run faster ?

On Tue, Oct 13, 2009 at 3:01 PM, Tom Lane <tgl [at] sss.pgh.pa.us> wrote:
> Anj Adu <fotographs [at] gmail.com> writes:
>> Assuming that autovacuum is off in 8,2 and upwards versions, would I
>> still have to do a database-wide vacuumdb =A0OR would vacuuming
>> individual tables that are permanent be sufficient to take care of XID
>> wraparound?
>
> In recent releases it is not possible to turn off autovacuum to the
> extent of preventing it from doing anti-wraparound vacuuming, so your
> question is a bit mis-posed. =A0But yes, you do need a database-wide
> manual vacuum if you are trying to forestall automatic anti-wraparound
> vacuuming. =A0Vacuuming individual tables isn't sufficient unless you get
> *every single one*, including the system catalogs.
>
> In practice, I think worrying about this is pointless in modern PG.
> If you want control over the timing of vacuuming on individual large
> tables, do them when you want to. =A0The system will occasionally force
> vacuums on small tables to prevent wraparound, but that isn't going
> to cause you any performance problems.
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane
>

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Anj Adu escribi=F3:
> We have a few 8.1 installations where the vacuumdb -a command takes
> 2-3 days to run ..(with a vacuum delay of 10ms)...autovac does not
> work for us as we have tables that get constantly dropped due to
> partitioning.(autovac would never finish given the size of our
> database

I think you should set pg_autovacuum.enabled=3Dfalse for those tables to
avoid having autovac work uselessly on them.

> and the fact that we have some "idle transactions" caused by
> our application server coneection pools.)

If this is really a problem, it's not going to be limited to autovacuum;
regular vacuum is going to be affected too.

--
Alvaro Herrera http://www.CommandPrompt.co=
m/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin