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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
