Tom Lane wrote:
> I think the context for this is that you have an agreed-on maintenance
> window, say extending from 2AM to 6AM local time, and you want to get
> all your vacuuming done in that window without undue spikes in the
> system load (because you do still have live users then, just not as many
> as during prime time). If there were a decent way to estimate the
> amount of work to be done then it'd be possible to spread the work
> fairly evenly across the window. What I do not see is where you get
> that estimate from --- especially since you probably have more than one
> table to vacuum in your window.
It is true that there is not a decent way to estimate the amount of work
to be done. But the purpose in here is not “spread the vacuum over 6
hours exactly”, it is “finish vacuum within 6 hours, and spread the
spikes as much as possible”. So the maximum estimation of the work is
enough to refine the vacuum within the window, it is fine if vacuum run
quickly than schedule. Also we don’t need to estimate the time of
vacuum, we only need to compare the actual progress of time window and
the progress of the work, and then adjust them to have the same pace in
the delay point.
The maximum of the work of vacuum can be estimated by size of the heap,
the size of the index, and the number of dead tuples. For example the
lazy vacuum has the following works:
1. scan heap
2. vacuum index
3. vacuum heap
4. truncate heap
Although 2 and 4 are quite unpredictable, but the total amount of work
including 1, 2, 3, and 4 can be estimated.
> The other problem is that "vacuum only during a maintenance window"
> doesn't seem all that compelling a policy anyway. We see a lot of
> examples of tables that need to be vacuumed much more often than once
> a day. So I'd rather put effort into making sure that vacuum can be run
> in the background even under high load, instead of designing around a
> maintenance-window assumption.
This feature is not necessary has a maintenance window assumption. For
example, if a table needs to be vacuumed every 3 hours to sweep the
garbage, then instead of tuning cost delay GUC hardly to refine vacuum
in 3 hours, we can make vacuum finish within the time frame by “VACUUM
IN time” feature.
If we can find a good way to tune the cost delay GUC to enable vacuum to
catch up with the speed of garbage generation in the high frequency
update system, then we won’t need this feature. For example, the
interval of two vacuums can be estimated by tracking the speed of the
dead tuple generation, but how can you tune the vacuum time to fit in
the interval of two vacuums? It seems that there is not easy to tune the
delay time of vacuum correctly.
Best Regards
--
Galy Lee <lee.galy _at_ oss.ntt.co.jp>
NTT Open Source Software Center
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
