Re: Autovacuum improvements

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.