Re: Autovacuum in the backend

Josh Berkus wrote:

>Qingqing,
>  
>
>>add a parameter to let user pass in the configuration parameters:
>>* autovacuum_command = "-s 100 -S 1 ..."
>>    
>>
>
>um, can we have these as separate GUCs and not lumped together as a string?  
>i.e.:
>autovacuum_frequency = 60  #seconds, 0 = disable
>autovacuum_vacuum_threshold = 200
>autovacuum_vacuum_multiple = 0.5
>autovacuum_analyze_threshold = 100
>autovacuum_analyze_multiple = 0.4
>
>AV should be disabled by default.  It should also automatically use the global 
>vacuum_delay settings.
>  
>

Agreed, disabled by default (at least for 8.1, perhaps a topic of 
conversation for 8.2+), yes it should obey the global vacuum_delay 
settings, and yes it should have it's own GUC's as you suggested (all of 
this was the case with the patch that I submitted for 8.0, which Alvarro 
is now working on).

>>But it would be very nice to have something _similar_ to FSM, say DSM
>>(dead space map), which is filled in when a tuple is marked as "dead for
>>all running backends", which could be used to implement a vacuum which
>>vacuums only those pages, which do actually contain removable tuples.
>>    
>>
>
>Speaking of FSM improvements, it would be **really** useful to have a pg_stats 
>view that let you know how full the FSM was, overall.  something like:
>pg_stats_fsm_usage
>fsm_relations    fsm_relations_used    fsm_pages        fsm_pages_used
>1000            312                200000        11579
>
>This would allow for other schemes of vacuum automation.
>  
>

Interesting, perhaps if FSM data is exported to the stats system 
autovacuum could use that.  What might be best is both a view that 
showed overall FSM information, but then also export FSM information on 
a per table basis, perhaps as additional columns added to existing stats 
tables.