Re: Autovacuum Issue

On Wed, Nov 23, 2011 at 11:25 AM, J Ramesh Kumar <rameshj1977(at)gmail(dot)com>wrote:

> Hi,
>
> My application is performing 1600 inserts per second and 7 updates per
> second. The updates occurred only in a small table which has only 6 integer
> columns. The inserts occurred in all other daily tables. My application
> creates around 75 tables per day. No updates/deletes occurred in those 75
> daily tables (only inserts and drop tables if older than 40 days). Since
> only inserts in the daily tables, I disabled autovacuum in the conf file
> and I can see it is off stat using show command.
>
> *sasdb=# show "autovacuum";
> autovacuum
> ------------
> off
> (1 row)*
>
> But the autovacuum is running frequently and it impact the performance of
> my system(high CPU). You can see the autovacuum in the pg_stat_activity.
> *
> sasdb=# select current_query from pg_stat_activity where current_query
> like 'autovacuum%';
> ** current_query **
>
> ------------------------------**------------------------------**
> ---------------------------
> autovacuum: VACUUM public.xxxxx**_17_Oct_11 (to prevent wraparound)
> autovacuum: VACUUM public.**xxxxx**_17_Oct_11 (to prevent wraparound)
> autovacuum: VACUUM public.**xxxxx**_17_Oct_11 (to prevent wraparound)
> (3 rows)
>
>
> *

Its pretty clear, its to prevent tranx wrap-around.

autovacuum_freeze_max_age (integer)

Specifies the maximum age (in transactions) that a table's pg_class.
relfrozenxid field can attain before a VACUUM operation is forced to
prevent transaction ID wraparound within the table. Note that the system
will launch autovacuum processes to prevent wraparound even when autovacuum
is otherwise disabled.

http://developer.postgresql.org/pgdocs/postgres/runtime-config-autovacuum.html
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

> **Why the autovacuum is running even though, I disabled ? Am I miss
> anything ?
>
> And also please share your views on my decision about disable autovacuum
> for my application. I am planning to run vacuum command daily on that small
> table which has frequent updates.
>
> Thanks,
> Ramesh
>