Re: scheduling autovacuum at lean hours only.

Rajesh Kumar Mallah <mallah.rajesh 'at' gmail.com> writes:

> Hi,
>
> Is it possible to configure autovacuum to run only
> during certain hours ? We are forced to keep
> it off because it pops up during the peak
> query hours.

You'd rather configure the delaying process to not alter too much
performance. Autovacuum is really not targeted at running once a
day - I think it is partly because the old vacuuming was too weak
(because too seldom in many cases) that autovaccum was added.

A delaying configuration that works nicely for us without
impacting performance much (tested at the time of 8.2 to cause a
+40% response time during autovacuuming, compared to +300% with
more default values):

vacuum_cost_delay = 150
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 1000
autovacuum_vacuum_cost_delay = 300

(Disclaimer: IIRC, Alvaro doesn't like these figures at all)

Of course, these are good for us (bloat is very, very low and
performance impact is not experienced in production), not
necessarily for you. You should conduct your own tests.

Be sure to also consider http://developer.postgresql.org/~wieck/vacuum_cost/

--
Guillaume Cottenceau