Thread: unexplained autovacuum to prevent wraparound

It appears to me that in my 8.4.0 system, autovacuum is running to prevent
wraparound contrary to the documentation. I have it set to a tables'
relfrozenxid has to get to 1.5 billion before that kicks in:

> show autovacuum_freeze_max_age;
 1500000000

> show vacuum_freeze_table_age;
 1300000000

> show vacuum_freeze_min_age;
 1000000000

Table foo has an age just over 1 billion, still well under the
freeze_table_age:

> select age(relfrozenxid) from pg_class where relname='foo';
    age
------------
 1055823634

yet, I see this in pg_stat_activity:

autovacuum: VACUUM public.foo (to prevent wraparound)

One possibly interesting thing is that this seems to have started just after
I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more
frequent analyze runs.  I wonder if that could be related.

Any ideas?  These wraparound autovacuums are taking up my vacuum workers so
no analyze workers can run.

Thanks!
Gordon


--
View this message in context:
http://old.nabble.com/unexplained-autovacuum-to-prevent-wraparound-tp27883825p27883825.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Gordon Shannon escribió:

> One possibly interesting thing is that this seems to have started just after
> I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more
> frequent analyze runs.  I wonder if that could be related.

You probably set the other values to 0, which includes the freeze age.
You need to set it (and all other values) to -1 instead.

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

This is 8.4, there is no pg_autovacuum table.  I set it like this:

alter table foo set (autovacuum_analyze_scale_factor=0.01);

On Fri, 2010-03-12 at 16:45 -0700, Gordon Shannon wrote:
> This is 8.4, there is no pg_autovacuum table.  I set it like this:
>
> alter table foo set (autovacuum_analyze_scale_factor=0.01);

That is 1% changes. I think you want .10

Sincerely,

Joshua D. Drake


>


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

Thanks, but I do want 1%.




--
If I had more time, I could have written you a shorter letter.  (Blaise Pascal)

On Fri, 2010-03-12 at 16:45 -0700, Gordon Shannon wrote:
> This is 8.4, there is no pg_autovacuum table.  I set it like this:
>
> alter table foo set (autovacuum_analyze_scale_factor=0.01);

That is 1% changes. I think you want .10

Sincerely,

Joshua D. Drake


>


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

Ah, now I see what you meant.  Forgive me, I thought you were referring to the pg_autovacuum table in 8.3 where you have to specifiy something for each column, and -1 says use the default.  It appears in 8.4.0 I have to explicitly set ALL (?) other storage parameters to -1 to get the default, otherwise I am getting zero for each value??  I don't believe the documentation mentions this rather important detail: http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS.  Did I miss it somewhere? 

Thanks!
Gordon




--
If I had more time, I could have written you a shorter letter.  (Blaise Pascal)

Gordon Shannon escribió:
> Ah, now I see what you meant.  Forgive me, I thought you were referring to
> the pg_autovacuum table in 8.3 where you have to specifiy something for each
> column, and -1 says use the default.  It appears in 8.4.0 I have to
> explicitly set ALL (?) other storage parameters to -1 to get the default,
> otherwise I am getting zero for each value??  I don't believe the
> documentation mentions this rather important detail:
> http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS.
> Did I miss it somewhere?

Err, no, that would be a bug.  Please update to 8.4.2, I think we fixed
it there.

revision 1.28.2.1
date: 2009-08-27 13:19:31 -0400;  author: alvherre;  state: Exp;  lines: +10 -10;
Fix handling of autovacuum reloptions.

In the original coding, setting a single reloption would cause default
values to be used for all the other reloptions.  This is a problem
particularly for autovacuum reloptions.

Itagaki Takahiro


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

That looks like the fix for this, thanks!  I will try to upgrade soon.

-- Gordon