How to avoid Force Autovacuum - Mailing list pgsql-general

From Vishalakshi Navaneethakrishnan
Subject How to avoid Force Autovacuum
Date
Msg-id CAP-PUP0QM0ZAiz9vOpNT=WEoa_inoyVfZw2ceHAHiZAqGpSQ1w@mail.gmail.com
Whole thread Raw
Responses Re: How to avoid Force Autovacuum
Re: How to avoid Force Autovacuum
Re: How to avoid Force Autovacuum
List 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

pgsql-general by date:

Previous
From: James Sewell
Date:
Subject: Re: Replication Postgre > Oracle
Next
From: Chris Travers
Date:
Subject: Re: Hierarchical numeric data type