Re: Autovacuum improvements - Mailing list pgsql-hackers

Alvaro Herrera wrote:
> Matthew T. O'Connor wrote:
>   
>> Alvaro Herrera wrote:
>>     
>>> pg_av_igroupmembers
>>> groupid    oid
>>> month        int
>>> dom        int
>>> dow        int
>>> starttime    timetz
>>> endtime    timetz
>>>       
>> This seems to assume that the start and end time for an interval will be 
>> on the same day, you probably need to specify a start month, dom, dow, 
>> time and an end month, dom, dow and time.
>>     
>
> Actually, I was thinking that if you want intervals that cross day
> boundaries, you just add more tuples (one which finishes at 23:59:59 and
> another which starts at 00:00:00 the next day).
>   

This still seems ambiguous to me, how would I handle a maintenance 
window of Weekends from Friday at 8PM though Monday morning at 6AM? My 
guess from what said is:
mon dom dow starttime endtime
null  null    6      20:00      null
null  null    1      null          06:00

So how do we know to vacuum on Saturday or Sunday?  I think clearly 
defined intervals with explicit start and stop times is cleaner.

>> This all looks reasonable if not a bit complex.  Question, what happens 
>> to the current pg_autovacuum relation?
>>     
>
> I had two ideas: one was to make pg_autovacuum hold default config for
> all tables not mentioned in any group, so sites which are OK with 8.2's
> representation can still use it.  The other idea was to remove it and
> replace it with this mechanism.
>
>   

Probably best to just get rid of it.  GUC variables hold the defaults 
and if we create a default interval / group, it will also have defaults.

>> I think we need more discussion on scheduling, we need to make sure this 
>> solves the vacuum starvation problem.  Does the launcher process 
>> consider each row in pg_av_schedule that applies at the current time 
>> separately?  That is say there are three entries in pg_av_schedule that 
>> apply right now, does that mean that the launcher can fire off three 
>> different vacuums?  Perhaps we need to add a column to pg_av_tablegroup 
>> that specifies the max number of concurrent worker processes for this 
>> table group.
>>     
>
> My idea was to assign each table, or maybe each group, to a queue, and
> then have as much workers as there are queues.  So you could put them
> all in a single queue and it would mean there can be at most one vacuum
> running at any time.  Or you could put each group in a queue, and then
> there could be as many workers as there are groups.  Or you could mix.
>
> And also there would be a "autovac concurrency limit", which would be
> a GUC var saying how many vacuums to have at any time.

Hmm... this seems like queue is nearly a synonym for group.  Can't we 
just add num_workers property to table groups?  That seems to accomplish 
the same thing.  And yes, a GUC variable to limits the total number of 
concurrent autovacuums is probably a good idea.