Mark Rostron wrote:
# select relname, age(relfrozenxid), relfrozenxid FROM pg_class WHERE relkind
= 'r' and relname = 'error';
relname |age| relfrozenxid
-+---+--
error | 286569433 | 3781443077
autovacuum_freeze_max_age is the default of 200m,
so I as
> No, it is not. The XID space is continuous and circular. For any given XID,
> there are 2^31-1 possible XIDs that are "before" it and 2^31-1 that are
> "after" it (plus the special
> FrozenXID value, which is always before everything else). There's no
> absolute comparisons possible, only
Mark Rostron writes:
>> No. XID comparisons are modulo 2^31.
> Thanks - I'm still trying to wrap my mind around this (sorry).
> So, for the sake of this description:
> - the XID space (size 2^32) is split into two sub-spaces, each of size 2^31
No, it is not. The XID space is continuous and ci
>
>> When the XID wraps, at the moment it does so, unless you set
>> vacuum_freeze_min_age to 0 and a vacuum has just been performed, is
>> there not a chance that there will be some data loss?
>
>No. XID comparisons are modulo 2^31.
>
Thanks - I'm still trying to wrap my mind around this (s
Mark Rostron writes:
> When the XID wraps, at the moment it does so, unless you set
> vacuum_freeze_min_age to 0 and a vacuum has just been performed, is
> there not a chance that there will be some data loss?
No. XID comparisons are modulo 2^31.
regards, tom lane
--
S
When the XID wraps, at the moment it does so, unless you set
vacuum_freeze_min_age to 0 and a vacuum has just been performed, is there not a
chance that there will be some data loss? If it changes value from (2^32 -1) to
(0), it's value is going to be less than SOME rows - the ones which have no
Anj Adu escribió:
> 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
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 s
Anj Adu 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 autovacuu
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 :
> Anj Adu escribió:
>> Reason we dont turn on autovacuum
Alvaro Herrera wrote:
Devrim GÜNDÜZ escribió:
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
d
Rob Newton escribió:
> Alvaro Herrera wrote:
> >Devrim GÜNDÜZ escribió:
> >
> >>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. T
Anj Adu escribió:
> 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
> resource
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 w
Anj Adu escribió:
> 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
2009/8/11 Anj Adu :
> 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
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 :
> Devrim GÜNDÜZ escribió:
>
>> FWIW, PostgreSQL 8.3+ will
Devrim GÜNDÜZ escribió:
> 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,
Anj Adu escribió:
> 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 Herrerahttp
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.
-
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@postgresql.org)
To make c
21 matches
Mail list logo
