Re: autovacuum next steps, take 2

On Thu, Feb 22, 2007 at 10:32:44PM -0500, Matthew T. O'Connor wrote:
> Jim C. Nasby wrote:
> >On Thu, Feb 22, 2007 at 09:32:57AM -0500, Matthew T. O'Connor wrote:
> >
> >>So the heuristic would be:
> >>* Launcher fires off workers into a database at a given interval
> >>(perhaps configurable?)
> >>* Each worker works on tables in size order.
> >>* If a worker ever catches up to an older worker, then the younger
> >>worker exits.
> >>
> >>This sounds simple and workable to me, perhaps we can later modify this
> >>to include some max_workers variable so that a worker would only exit if
> >>it catches an older worker and there are max_workers currently active.
> >>
> >
> >That would likely result in a number of workers running in one database,
> >unless you limited how many workers per database. And if you did that,
> >you wouldn't be addressing the frequently update table problem.
> >
> >A second vacuum in a database *must* exit after a fairly short time so
> >that we can go back in and vacuum the important tables again (well or
> >the 2nd vacuum has to periodically re-evaluate what tables need to be
> >vacuumed).
> >
>
> I'm not sure this is a great idea, but I don't see how this would result
> in large numbers of workers working in one database. If workers work
> on tables in size order, and exit as soon as they catch up to an older
> worker, I don't see the problem. Newer works are going to catch-up to
> older workers pretty quickly since small tables will vacuum fairly quickly.

The reason that won't necessarily happen is because you can get large
tables popping up as needing vacuuming at any time.

Round 1:
Fire up autovac worker; starts working and soon hits 100G table

Round 2:
Another worker starts. Since round 1, a 98G table now needs vacuuming,
which this worker soon hits.

Round 3:
89G table now needs vacuuming. Worker 3 starts up and soon hits it.

So now we have 3 workers, all hammering away in the same database, and
likely causing a lot of random IO.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)