Re: Some thoughts about i/o priorities and throttling vacuum

shridhar_daithankar(at)persistent(dot)co(dot)in (Shridhar Daithankar) writes:
> Tom Lane wrote:
>> I was just thinking of a GUC parameter: wait N milliseconds between
>> pages, where N defaults to zero probably. A user who wants to run his
>> vacuum as a background process could set N larger than zero. I don't
>> believe we are anywhere near being able to automatically adjust the
>> delay based on load, and even if we could, this would ignore the point
>> you make above --- the user's intent has to matter as much as anything
>> else.
>
> I am slightly confused here. IIRC pg_autovacuum never did a vacuum
> full. At the most it does vacuum /vacuum analyse, none of which chew
> disk bandwidth.

[remainder elided; your second sentence is the vital bit...]

> What am I missing?

You are missing that VACUUM most certainly _does_ chew up disk
bandwidth, because it must load the pages of the table into memory.

If the system is busy doing other I/O, then the other I/O has to
compete with the I/O initiated by VACUUM.

VACUUM FULL is certainly more expensive than VACUUM/VACUUM ANALYZE;
the point is that even the latter is NOT free on big tables when there
is a lot of "traffic."

VACUUM is like putting an extra few transport trucks onto the highway.
It may only go from one highway junction to the next, and be fairly
brief, if traffic is moving well. But if traffic is heavy, it adds to
the congestion. (And that's as far as the analogy can go; I can't
imagine a way of drawing the GUC parameter into this...)
--
(format nil "~S(at)~S" "cbbrowne" "libertyrms.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)