Обсуждение: vacuum tx id wraparound issues
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
> 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 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.