Обсуждение: XID wraparound in 8.4
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
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ÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org
Вложения
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 Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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, whereas in 8.2 and up it only vacuum tables that have not been vacuumed recently. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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@commandprompt.com>: > 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, whereas in 8.2 and up it only vacuum tables that > have not been vacuumed recently. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. >
2009/8/11 Anj Adu <fotographs@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=20ms) 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.
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 http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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@gmail.com>: > 2009/8/11 Anj Adu <fotographs@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=20ms) 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. >
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 > 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.com/ The PostgreSQL Company - Command Prompt, Inc.
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. 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.com/ The PostgreSQL Company - Command Prompt, Inc.
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 > 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
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@commandprompt.com>: > 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 >> 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.com/ > The PostgreSQL Company - Command Prompt, Inc. >
Anj Adu <fotographs@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
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@sss.pgh.pa.us> wrote: > Anj Adu <fotographs@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 >
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 I think you should set pg_autovacuum.enabled=false 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.com/ The PostgreSQL Company - Command Prompt, Inc.