Обсуждение: vacuum tx id wraparound issues

Hi all;

I'm seeing a number of vacuum's in one of our db's that has a notation (to prevent wraparound).

I've tried a number of things to fix it. last nite during the off hours we ran a "vacuumdb -a"

The vacuumdb did vacuum every db in the cluster but I'm still seeing the vacuums to prevent wraparound.

a few days ago this query showed an avg of 200million for the age, now its 100million.

postgres=# select datname, age(datfrozenxid) from pg_database;
datname | age
-----------+-----------
template1 | 36020008
template0 | 36017769
postgres | 101264283
report | 101264283
kabc | 100009842
prod | 101264283
quota | 101264283
cfg | 101264283
(8 rows)

why did the vacuumdb -a not clear this up for us?

Thanks in advance

Kevin Kempter <kevink@consistentstate.com> writes:
> I'm seeing a number of vacuum's in one of our db's that has a notation (to
> prevent wraparound).

This is a normal condition.  There isn't anything you can do that will
make that stop permanently --- it's just routine housekeeping.

            regards, tom lane

On Thursday 25 June 2009 08:25:53 Tom Lane wrote:
> Kevin Kempter <kevink@consistentstate.com> writes:
> > I'm seeing a number of vacuum's in one of our db's that has a notation
> > (to prevent wraparound).
>
> This is a normal condition. There isn't anything you can do that will
> make that stop permanently --- it's just routine housekeeping.
>
> regards, tom lane

I did a vacuumdb last nite and already this am I see vacuums to prevent wraparound. Do you have any thoughts on increasing the autovacuum_freeze_max_age value?

Sorry, meant to reply on-list. Unfortunately gmail has made it awkward
to stay on-list despite many people complaining about the change.


---------- Forwarded message ----------
From: Greg Stark <gsstark@mit.edu>
Date: Thu, Jun 25, 2009 at 4:28 PM
Subject: Re: vacuum tx id wraparound issues
To: Kevin Kempter <kevink@consistentstate.com>


On Thu, Jun 25, 2009 at 3:29 PM, Kevin
Kempter<kevink@consistentstate.com> wrote:
>
> I did a vacuumdb last nite and already this am I see vacuums to prevent
> wraparound. Do you have any thoughts on increasing the
> autovacuum_freeze_max_age value?


You can safely raise this value substantially. The main cost is that
the clog will take extra space to record the status of all these old
transactions. Beware that if you set this to something like 2 billion
then the vacuums that kick in at that point have a limited amount of
time to complete before the whole database shuts down.

However it's unusual to run into this situation at all, let alone
after a just one day. That would be over 2,000 transactions per second
every second for 24 hours.

I suspect your vacuumdb didn't actually vacuum some tables. Do you
have multiple databases? Did you do vacuumdb -a? What database and
table is the autovacuum process kicking in for?



--
greg
http://mit.edu/~gsstark/resume.pdf



--
greg
http://mit.edu/~gsstark/resume.pdf

On Thu, Jun 25, 2009 at 4:39 PM, Kevin
Kempter<kevink@consistentstate.com> wrote:
>
> we're inserting an average of 70-100 rows per second into these tables.

Hm. And every row is a separate transaction? That's still only a few
hundred rows per second. About 25 million per day. You should have
about 4 days before it hits autovacuum_freeze_max_age-vacuum_freeze_min_age.

Are you using subtransactions heavily (savepoints in sql or exception
clauses in plpgsql)? That could add a multiplier or two to the number
of transaction ids used up.

You can raise autovacuum_freeze_max_age to, say, 800 million to get
four times longer before the autovacuum kicks in. You can also lower
vacuum_freeze_min_age to maybe 25 million. That will give you about
775 million transaction ids, almost 8x what you have now, which will
hopefully give you about a week before autovacuum tries to freeze the
table



--
greg
http://mit.edu/~gsstark/resume.pdf

On Thursday 25 June 2009 10:08:16 Greg Stark wrote:
> On Thu, Jun 25, 2009 at 4:39 PM, Kevin
>
> Kempter<kevink@consistentstate.com> wrote:
> > we're inserting an average of 70-100 rows per second into these tables.
>
> Hm. And every row is a separate transaction? That's still only a few
> hundred rows per second. About 25 million per day. You should have
> about 4 days before it hits
> autovacuum_freeze_max_age-vacuum_freeze_min_age.
>
> Are you using subtransactions heavily (savepoints in sql or exception
> clauses in plpgsql)? That could add a multiplier or two to the number
> of transaction ids used up.
>
> You can raise autovacuum_freeze_max_age to, say, 800 million to get
> four times longer before the autovacuum kicks in. You can also lower
> vacuum_freeze_min_age to maybe 25 million. That will give you about
> 775 million transaction ids, almost 8x what you have now, which will
> hopefully give you about a week before autovacuum tries to freeze the
> table
>
>
>
> --
> greg
> http://mit.edu/~gsstark/resume.pdf

Thanks Greg; I'll give these settings a shot.