Re: scheduling autovacuum at lean hours only.

On Wed, Feb 11, 2009 at 7:11 PM, Guillaume Cottenceau <gc(at)mnc(dot)ch> wrote:
> 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

why is it not a good idea to give end users control over when they
want to run it ?

>
> (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
>