How to avoid Force Autovacuum

From: Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to avoid Force Autovacuum
Date: 2013-08-07 09:46:54
Message-ID: CAP-PUP0QM0ZAiz9vOpNT=WEoa_inoyVfZw2ceHAHiZAqGpSQ1w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

Hi All,

We have one production database server , having 6 DBs, Postgres 9.2.1
version.

This is my vacuum settings in Production database

#autovacuum = on # Enable autovacuum subprocess?
'on'
# requires track_counts to also be
on.
log_autovacuum_min_duration = 0
autovacuum_vacuum_threshold = 50000 # min number of row updates before
# vacuum
autovacuum_analyze_threshold = 50000
maintenance_work_mem = 2GB # min 1MB

#################

Daily once we are executing "Vacuum Freeze analyze" -- To prevent
transaction id wraparound
using this command
vacuumdb -F -z -h localhost -U postgres dbname

Even sometimes autovacuum running on the databases and increase the load
(Above 200) very much and the server was unresponsive

I have seen the autovacum worker process in top command,

While i executing pg_stat_activity as postgres user, i have seen the pid of
autovacuum process in the result but the query filed is "Empty"

while i check in Pg_class table i got the value as last_autoanalyze_field
is not null in one of the table.

So i am guessing this is the auto analyze query.

But why it increased the load very high?

How can i avoid the autovacuum process ? And also autovacuum executed in
the template0 database also. But I cant connect the database , since it has
"datallowconn=F"

If i update the value to true and then execute "vacuum freeze analyze" will
make any problems?

since template0 has no activities why the age(datfrozenxid) increasing
heavily and reach the thresold value?

Do i need to disable autovacuum for particular tables to avoid force
autovacuum ?

Can you please suggest me in this case?
--
Best Regards,
Vishalakshi.N

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sebastian Kornehl 2013-08-07 10:24:28 Reltuples/n_live_tup values wrong
Previous Message 李海龙 2013-08-07 06:51:10 Re: BUG #8327: a bug of spgist index in a heavy write condition

Browse pgsql-general by date

  From Date Subject
Next Message Chris Travers 2013-08-07 10:21:02 Re: Hierarchical numeric data type
Previous Message James Sewell 2013-08-07 08:59:17 Re: Replication Postgre > Oracle