Re: autovacuum next steps

Alvaro Herrera wrote:
> Matthew T. O'Connor wrote:
>> 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.

So you are telling people to choose an autovacuum_delay so high that 
they need to run multiple autovacuums at once to keep up?  I'm probably 
being to dramatic, but it seems inconsistent.

>> 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?

I wasn't clear, I would have the Admin specified it, and we can store it 
as an additional column in the pg_autovacuum_settings table.  Or perhaps 
if the table is below some size threshold and autovacuum seems that it 
needs to be vacuumed every time it checks it 10 times in a row or 
something like that.