Re: scheduling autovacuum at lean hours only.

On Wed, Feb 11, 2009 at 11:30 PM, Brad Nicholson
<bnichols(at)ca(dot)afilias(dot)info> wrote:
> On Wed, 2009-02-11 at 22:57 +0530, Rajesh Kumar Mallah wrote:
>> On Wed, Feb 11, 2009 at 10:03 PM, Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com> wrote:
>> > On Wed, Feb 11, 2009 at 2:57 PM, Rajesh Kumar Mallah
>> > <mallah(dot)rajesh(at)gmail(dot)com> wrote:
>> >
>> >>> 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 ?
>> >
>> > Effectively, you have control over autovacuum via these params.
>> > You have to remember, that autovacuum doesn't cost much, and it makes
>> > planner know more about data.
>> > It's not there to clean up databases, as you might imagine - it is
>> > there to update stats, and mark pages as free.
>> >
>> > So make sure you tweak that config fist, because I have a funny
>> > feeling that you just think that vacuuming bogs down your machine, and
>> > _can_ be turned off without any bad consequences, which is simply not
>> > true.
>>
>> our usage pattern is such that peak activity (indicated by load average)
>> during day time is 10 times during night hours. Autovacuum just puts
>> more pressure to the system. If less stressing version is used then
>> it shall take longer to complete one cycle, which would mean less
>> performance for longer time . Less performance queues up queries
>> and encourages people to re submit their queries which again
>> adds to bogging up the system.
>
> That's not exactly how it works in practise, if tuned properly. It may
> take longer, but it is less intensive while running.
>
> We had one system that had spikes happening due to the exact case you
> described - there were noticeably high IO wait times while certain
> tables were being vacuumed. We set the cost delay and the wait times
> dropped to the point where it was non-issue.

I think i can take this route and monitor the io activity
during vacuums. thanks everyone for their suggestions.

-- mallah.

>Vacuums take twice as
> long, but there is no measurable impact to the performance.
>
>> In our case i feel the hardware is bit underscaled as compared to
>> load thats why i think running in lean hours is best of both worlds
>> no performance sacrifices and intelligent vacuuming.
>
> That is a different issue altogether.
>
> Not vacuuming a running system at all during peak hours is not
> considered intelligent vacuuming IMHO. There are plenty of use cases
> where small, frequent vacuums keep tables under control at a very low
> cost. Letting them go for extended periods of time without vacuuming
> causes bloat and eventual slowdowns to table access which manifest in
> higher IO usage across the board.
>
> If you really are dead set on vacuuming only at night, you may want to
> do a careful analysis of which tables need to be vacuumed and when, and
> trigger manual vacuums from cron.
>
> --
> Brad Nicholson 416-673-4106
> Database Administrator, Afilias Canada Corp.
>
>