Re: autovacuum next steps

Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:
> >After staring at my previous notes for autovac scheduling, it has become
> >clear that this basics of it is not really going to work as specified.
> >So here is a more realistic plan:
> 
> [Snip Detailed Description]
> 
> >How does this sound?
> 
> On first blush, I'm not sure I like this as it doesn't directly attack 
> the table starvation problem, and I think it could be a net loss of speed.
> 
> VACUUM is I/O bound, as such, just sending multiple vacuum commands at a 
> DB isn't going to make things faster, you are now going to have multiple 
> processes reading from multiple tables at the same time.  I think in 
> general this is a bad thing (unless we someday account for I/O made 
> available from multiple tablespaces).

Yeah, I understand that.  However, I think that can be remedied by using
a reasonable autovacuum_vacuum_cost_delay setting, so that each worker
uses less than the total I/O available.  The main point of the proposal
is to allow multiple workers on a DB while also allowing multiple
databases to be processed in parallel.

> I think we can extend the current autovacuum stats to add one more 
> column that specifies "is hot" or something to that effect.  Then when 
> the AV launcher sends a worker to a DB, it will first look for tables 
> marked as hot and work on them.  While working on hot tables, the 
> launcher need not send any additional workers to this database, if the 
> launcher notices that a worker is working on regular tables, it can send 
> another worker which will look for hot tables to working, if the worker 
> doesn't find any hot tables that need work, then it exits leaving the 
> original working to continue plodding along.

How would you define what's a "hot" table?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.