Thread: Autovacuum does not stay turned off

We are attempting to turn off autovacuum but it keeps coming back.  We
can't afford the performance hit from vacuum while end users are
accessing our system.

Postgresql Version:  8.3.3
OS:  Linux 2.6.18-53.el5PAE #1 SMP

Running PostgreSQL setting:
sspg=# show autovacuum;
 autovacuum
------------
 off
(1 row)

pg.log Log Entries:
2008-08-26 15:24:50 GMTLOG:  autovacuum launcher started
-- and then we manually kill it
postgres 32371  0.0  0.1 1133768 23572 ?       Ss   15:16   0:00
postgres: autovacuum worker process   rollup_data_store
# kill 32371
2008-08-26 15:24:53 GMTFATAL:  terminating autovacuum process due to
administrator command

Does anyone know what will cause this bahavior for autovacuum?

Thank you in advance

-Jerry

On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote:
> Does anyone know what will cause this bahavior for autovacuum?

http://www.postgresql.org/docs/current/interactive/runtime-config-autovacuum.html
-> autovacuum_freeze_max_age

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote:
>
> Does anyone know what will cause this bahavior for autovacuum?

You're probably approaching the wraparound limit in some database.

If you think you can't afford the overhead when users are accessing
the system, when are you vacuuming?

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

This makes sense.  What queries can I run to see how close to the limit we are?  We need to determine if we should stop the process which updates and inserts into this table until after the critical time this afternoon when we can perform the required maintenance on this table.

hubert depesz lubaczewski wrote:

On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote: 

Does anyone know what will cause this bahavior for autovacuum?   

http://www.postgresql.org/docs/current/interactive/runtime-config-autovacuum.html
-> autovacuum_freeze_max_age

depesz
 

Andrew Sullivan wrote:

On Tue, Aug 26, 2008 at 09:27:48AM -0600, Jerry Champlin wrote: 

Does anyone know what will cause this bahavior for autovacuum?   

You're probably approaching the wraparound limit in some database.  

If you think you can't afford the overhead when users are accessing
the system, when are you vacuuming?

A
 

We are changing the table structure tonight.  These two tables are very highly updated.  The goal is to use autovacuum but not have it take 10 days to run on a 13MM record table.

Thanks

-Jerry

On Tue, Aug 26, 2008 at 10:45:31AM -0600, Jerry Champlin wrote:
> This makes sense.  What queries can I run to see how close to the limit
> we are?  We need to determine if we should stop the process which
> updates and inserts into this table until after the critical time this
> afternoon when we can perform the required maintenance on this table.

select datname, age(datfrozenxid) from pg_database;

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Thanks for the help.  The applied solution follows.  We will be taking a
number of maintenance steps to manage these very high update tables
which I will summarize later as I suspect we are not the only ones with
this challenge.

http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
http://www.postgresql.org/docs/current/interactive/catalog-pg-autovacuum.html

data_store=# SELECT relname, oid, age(relfrozenxid) FROM pg_class WHERE
relkind = 'r';
...
hour_summary            | 16392 | 252934596
percentile_metadata     | 20580 | 264210966
(51 rows)

data_store=# insert into pg_autovacuum values
(16392,false,350000000,2,350000000,1,200,200,350000000,500000000);
INSERT 0 1
data_store=# insert into pg_autovacuum values
(20580,false,350000000,2,350000000,1,200,200,350000000,500000000);
INSERT 0 1
data_store=#


hubert depesz lubaczewski wrote:
> On Tue, Aug 26, 2008 at 10:45:31AM -0600, Jerry Champlin wrote:
>
>> This makes sense.  What queries can I run to see how close to the limit
>> we are?  We need to determine if we should stop the process which
>> updates and inserts into this table until after the critical time this
>> afternoon when we can perform the required maintenance on this table.
>>
>
> select datname, age(datfrozenxid) from pg_database;
>
> Best regards,
>
> depesz
>
>