Re: autovacuum next steps, take 3 - Mailing list pgsql-hackers

Galy Lee wrote:
> 
> Alvaro Herrera wrote:
> >worker to-do list
> >-----------------
> >It removes from its to-do list the tables being processed.  Finally, it
> >writes the list to disk.
> 
> I am worrying about the worker-to-do-list in your proposal. I think
> worker isn't suitable to maintain any vacuum task list; instead
> it is better to maintain a unified vacuum task queue on autovacuum share
> memory.

Galy,

Thanks for your comments.

I like the idea of having a global task queue, but sadly it doesn't work
for a simple reason: the launcher does not have enough information to
build it.  This is because we need access to catalogs in the database;
pg_class and pg_autovacuum in the current code, and the catalogs related
to the maintenance window feature when we implement it in the (hopefully
near) future.

Another point to be made, though of less importance, is that we cannot
keep such a task list in shared memory, because we aren't able to grow
that memory after postmaster start.  It is of lesser importance, because
we could keep the task list in plain files on disk; this is merely a
SMOP.  The functions to expose the task list to SQL queries would just
need to read those files.  It would be slower than shared memory,
certainly, but I don't think it's a showstopper (given the amount of
work VACUUM takes, anyway).

Not having access to the catalogs is a much more serious problem for the
scheduling.  One could think about dumping catalogs to plain files that
are readable to the launcher, but this is not very workable: how do you
dump pg_class and have it up to date all the time?  You'd have to be
writing that file pretty frequently, which doesn't sound a very good
idea.

Other idea I had was having a third kind of autovacuum process, namely a
"schedule builder", which would connect to the database, read catalogs,
compute needed vacuuming, write to disk, and exit.  This seems similar
to your task-gathering worker.  The launcher could then dispatch regular
workers as appropriate.  Furthermore, the launcher could create a global
schedule, based on the combination of the schedules for all databases.
I dismissed this idea because a schedule gets out of date very quickly
as tables continue to be used by regular operation.  A worker starting
at t0 may find that a task list built at t0-5 min  is not very relevant.
So it needs to build a new task list anyway, which then begs the
question of why not just let the worker itself build its task list?
Also, combining schedules is complicated and you start thinking in
asking the DBA to give each database a priority, which is annoying.

So the idea I am currently playing with is to have workers determine the
task list at start, by looking at both the catalogs and considering the
task lists of other workers.  I think this is the natural evolution of
the other ideas -- the worker is just smarter to start with, and the
whole thing is a lot simpler.


> The task queue might be filled by dedicated task-gathering-worker or it
> might be filled by *external task gatherer*.

The idea of an external task gatherer is an interesting one which I
think would make sense to implement in the future.  I think it is not
very difficult to implement once the proposal we're currently discussing
is done, because it just means we have to modify the part where each
worker decides what needs to be done, and at what times the launcher
decides to start a worker on each database.  The rest of the stuff I'm
working on is just infrastructure to make it happen.

So I think your basic idea here is still workable, just not right now.
Let's discuss it again as soon as I'm done with the current stuff.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support