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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
