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.
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.
