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.