Re: Never-idle autovacuum, and does (auto)vacuuming fill the OS cache ?

On Thursday 06 October 2011 07:00:20 Andrew Sullivan wrote:
> On Thu, Oct 06, 2011 at 11:58:15AM +0200, Vincent de Phily wrote:
> > Fearing that vacuuming might accumulate lateness and hoping to see the
> > system idle every now and then,
>
> Why is your goal to see the system idle every now and then? It's not
> going to get tired if it keeps working, and if you have a lot of work
> and can spin out that work so that the system always has a little bit
> of work to do, then you use your resources more efficiently.
>
> Normally, one likes to see some idle time because it is evidence of
> "headroom" -- that you have more capacity than you actually need. If
> that's the reason you want to see the idle times, then surely you
> don't want to tune the system with the goal of causing idleness. You
> want to tune the system so that the work gets done in as smooth and
> fast a way possible. So I would aim for maximum throughput (including
> but not exclusively complete table maintenance) and then check whether
> you're getting any idle time. Off the cuff, though, it sounds to me
> like you need more capacity than you have.

I agree idleness per se is not the goal, and whatever work needs to be done
might as well be spread smoothly over time. Idleness *is* however a measure of
the ressource headroom available, and that headroom was incomfortably small
(for IO) in this case. I was just groping for more performance out of the
system.

In the case of vacuuming however, I think there's a point to be made about
finishing fast when all vacuum workers are constantly busy : say the vacuum
daemon notices that there are 10 tables that need vacuuming now. It allocates
3 workers, but while they do their intentionally-slow work, the other 7 tables
keep creating more vacuumable tuples, so it'll be more work overall because
they're "late" in their "vacuum schedule". Does that make sense (I'm not sure
id does) ?

Anyway, my particular issue is solved for now : I realized those tables were
terribly bloated (often more than 99% slack), so I vacuum-fulled them and now
the autovacuums run very fast and the disk is 90% idle again. That slack
probably appeared at table initialization time because the fsm was not big
enough. I since raised the fsm, but I think it's big enough during normal
(non-init) usage anyway.

I'm still interested in more opinions about my two questions :
* When does it make sense to make autovacuum more aggressive on IO, and by
how much ?
* Does vacuuming fill the OS's disk cache, and is it an issue if it does ?

Cheers.

--
Vincent de Phily