We have one production database server , having 6 DBs, Postgres 9.2.1 version.
#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