Re: Autovacuum Improvements

Alvaro Herrera wrote:
> Matthew O'Connor wrote:
>   
>> Glen Parker wrote:
>>     
>>> If it isn't there somewhere already, I would ask to add:
>>>
>>> 4) Expose all information used by autovacuum to form its decisions.
>>>       
>> You could argue that this is already there, although not easy to get at 
>> I suppose.  But all table threshold settings are available either in the 
>> pg_autovacuum relation or the defaults via GUC variables, that plus a 
>> little math will get the information autovacuum uses to form its decisions.
>>     
>
> No, we currently don't expose the number of dead tuples which autovacuum
> uses.
>
>   
>>> 5) Expose a very easy way to discover autovacuum's opinion about a 
>>> particular table, for example "table_needs_vacuum(oid)", ignoring any 
>>> time constraints that may be in place.
>>>       
>> This might be a nice feature however in the presence of the much talked 
>> about but not yet developed maintenance window concept, I'm not sure how 
>>  this should work.  That is, during business hours the table doesn't 
>> need vacuuming, but it will when the evening maintenance window opens up.
>>     
>
> I intend to work on the maintenance window idea for 8.3.  I'm not sure
> if I'll be able to introduce the worker process stuff in there as well.
> I actually haven't done much design on the stuff so I can't say.
>
>   
What does a maintenance window mean?  I am slightly fearful that it as 
other improvements to vacuum are made, it will change it's meaning.

There has been discussion about a bitmap of dirty pages in a relation 
for vacuum to clean.  Do that effect what maintenance means?  eg.  Does 
maintenance mean I can only scan the whole relation for XID wrap in 
maintenance mode and not during non-maintenance time.  Does it mean we 
don't vacuum at all in non-maintenance mode.  Or do we just have a 
different set of thresholds during maintenance.

Further to this was a patch a long time ago for partial vacuum, which 
only vacuumed part of the relation.  It was rejected on grounds of not 
helping as the index cleanup is the expensive part.  My view on this is, 
if with a very large table you should be able to vacuum until you fill 
your maintenance work mem.  You are then forced to process indexes.  
Then that is a good time to stop vacuuming.  You would have to start the 
process again effectively.  This may also change the meaning of 
maintenance window.  Again, only full relation scans in maintenance 
times, possibly something else entirely.

I am unsure of what the long term goal of the maintenance window is.  I 
understand it's to produce a time when vacuum is able to be more 
aggressive on the system.  But I do not know what that means in light of 
other improvements such as those listed above.  Coming up with a method 
for maintenance window that just used a separate set of thresholds is 
one option.  However is that the best thing to do.  Some clarity here 
from others would probably help.  But I also think we need to consider 
the big picture of where vacuum is going before inventing a mechanism 
that may not mean anything come 8.4

> Now, if you (Matthew, or Glen as well!) were to work on that it'll be
> appreciated ;-) and we could team up.
>   

I am happy to try and put in some design thought and discussion with 
others to come up with something that will work well.

Regards

Russell Smith