Tom Lane wrote:
"Matthew T. O'Connor" <matthew@zeut.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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
