Re: How to avoid Force Autovacuum

Vishalakshi Navaneethakrishnan <nvishalakshi(at)sirahu(dot)com> wrote:

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

There were some fixes for autovacuum problems in 9.2.3.  Some other
fixes will be coming when 9.2.5 is released.  Many of your problems
are likely to go away by staying up-to-date on minor releases.

http://www.postgresql.org/support/versioning/

> autovacuum_vacuum_threshold = 50000

By setting this so high, you are increasing the amount of work
autovacuum will need to do when it does work on a table.  A smaller
value tends to give less "bursty" performance.  Also, any small,
frequently-updated tables may bloat quite a bit in 50000
transactions.

> maintenance_work_mem = 2GB

Each autovacuum worker will allocate this much RAM.  If all of your
autovacuum workers wake up at once, would losing 2GB for each one
from your cache cause a significant performance hit?  (Since you
didn't say how much RAM the machine has, it's impossible to tell.)

> How can i avoid the autovacuum process ?

Vacuuming is a necessary part of PostgreSQL operations, and
autovacuum is almost always part of a good vacuum plan.  The bug
fixes in 9.2.3 will help avoid some of the most extreme problems,
but you might also want to reduce the threshold so that it has less
work to do each time it wakes up, reducing the impact.

> And also autovacuum executed in the template0 database also.

What does running this in psql this show?:

\x on
select * from pg_database where datname = 'template0';
select * from pg_stat_database where datname = 'template0';

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company