Re: RFC: changing autovacuum_naptime semantics

Galy Lee wrote:

Hi,

> Alvaro Herrera wrote:
> > I still haven't received the magic bullet to solve the hot table
> > problem, but these at least means we continue doing *something*.
> 
> Can I know about what is your plan or idea for autovacuum improvement
> for 8.3 now? And also what is the roadmap of autovacuum improvement for 8.4?

Things I want to do for 8.3:

- Make use of the launcher/worker stuff, that is, allow multiple autovacuum processes in parallel.  With luck we'll
findout how to deal with hot tables.
 

Things I'm not sure we'll be able to have in 8.3, in which case I'll get
to them for early 8.4:

- The maintenance window stuff, i.e., being able to throttle workers depending on a user-defined schedule.

8.4 material:

- per-tablespace throttling, coordinating IO from multiple workers


I don't have anything else as detailed as a "plan".  If you have
suggestions, I'm all ears.

Now regarding your restartable vacuum work.  I think that stopping a
vacuum at some point and being able to restart it later is very cool and
may get you some hot chicks, but I'm not sure it's really useful.  I
think it makes more sense to do something like throttling an ongoing
vacuum to a reduced IO rate, if the maintenance window closes.  So if
you're in the middle of a heap scan and the maintenance window closes,
you immediately stop the scan and go the the index cleanup phase, *at a
reduced IO rate*.  This way, the user will be able to get the benefits
of vacuuming at some not-too-distant future, without requiring the
maintenance window to open again, but without the heavy IO impact that
was allowed during the maintenance window.

Does this make sense?

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