Re: Automatic free space map filling

Tom Lane wrote:
> "Matthew T. O'Connor" <matthew(at)zeut(dot)net> writes:
>
>> That patch is a step forward if it's deemed OK by the powers that be.
>> However, autovacuum would still need to be taught to handle simultaneous
>> vacuums. I suppose that in the interim, you could disable autovacuum
>> for the problematic queue table and have cron issue a manual vacuum
>> command for that table at the required frequency.
>>
>
> I'm not sure you should think of that as an "interim" solution. I don't
> really like the idea of multiple autovacuums running concurrently. ISTM
> autovac is intended to be something that lurks in the background and
> doesn't take up an unreasonable percentage of your system bandwidth ...
> but if there's more than one of them, it's going to be mighty hard to
> control the overall load penalty. Plus you have to worry about keeping
> them off each others' backs, ie, not all trying to vac the same table at
> once. And in a scenario like Csaba's, I think the hotspot tables are
> just exactly what they'd all try to vacuum.
>
> For small hotspot tables I think a scheduled vacuum process is just the
> thing, whereas autovac is more of a free-lance thing to keep the rest of
> your DB in line.

While I agree that given the current state of affairs the cron solution
is elegant, I personally want autovac to solve all of our vacuuming
needs, I really dislike the idea of requiring a cron based solution to
solve a fairly typical problem. Besides the cron solution is sloppy, it
blindly vacuums whether it's needed or not resulting in a net increase
of cycles spent vacuuming.

Anyway, I don't know the best way to implement it but I wasn't thinking
of just firing off multiple autovac processes. I was envisioning
something like an autovacuum master process that launches (forks?)
VACUUM commands and has some smarts about how many processes to fire
off, or that it would only fire off simultaneous VACUUMS for tables that
have been flagged as hot spot tables.

I recognize that teaching autovac to handle simultaneous VACUUM's in a
sane way will require a quantum leap of complexity but it still seems a
better long term solution. I would agree that using cron makes sense if
we were seeing lots of different scenarios that we couldn't possibly
anticipate, but I don't think that is where we are.

BTW, this discussion is only relevant if we allow simultaneous vacuum.
Is this something you see as inevitable whether or not you think Hannu's
implementation is acceptable.

Matt