Re: Proposed patch: Smooth replication during VACUUM FULL

On Sun, May 1, 2011 at 6:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On Sat, Apr 30, 2011 at 11:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> A bigger objection to this patch is that it seems quite incomplete.
>>> I'm not sure there's much point in adding delays to the first loop of
>>> copy_heap_data() without also providing for delays inside the sorting
>>> code and the eventual index rebuilds; which will make the patch
>>> significantly more complicated and invasive.
>
>> The patch puts the old behaviour of vacuum delay back into VACUUM FULL
>> and seems worth backpatching to 9.0 and 9.1 to me, since it is so
>> simple.
>
> No, it does perhaps 1% of what's needed to make the new implementation
> react to vacuum_cost_delay in a useful way.  I see no point in applying
> this as-is, let alone back-patching it.

Gabriele's test results show the opposite. It is clearly very effective.

Please look again at the test results.

I'm surprised that the reaction isn't "good catch" and the patch
quickly applied.

>> Previously there wasn't any delay in the sort or indexing either, so
>> it's a big ask to put that in now and it would also make backpatching
>> harder.
>
> You're missing the point: there wasn't any sort or reindex in the old
> implementation of VACUUM FULL.  The CLUSTER-based implementation makes
> use of very large chunks of code that were simply never used before
> by VACUUM.
>
>>> Another question is whether this is the right place to be looking
>>> at all.  If Gabriele's setup can't keep up with replication when a
>>> VAC FULL is running, then it can't keep up when under load, period.
>>> This seems like a pretty band-aid-ish response to that sort of problem.
>
>> This isn't about whether the system can cope with the load, its about
>> whether replication lag is affected by the load.
>
> And I think you're missing the point here too.  Even if we cluttered
> the system to the extent of making all steps of VACUUM FULL honor
> vacuum_cost_delay, it wouldn't fix Gabriele's problem, because any other
> I/O-intensive query would produce the same effect.  We could further
> clutter everything else that someone defines as a "maintenance query",
> and it *still* wouldn't fix the problem.  It would be much more
> profitable to attack the performance of replication directly.

I don't think the performance of replication is at issue. This is
about resource control.

The point is that replication is quite likely to be a high priority,
especially with sync rep. Speeding up replication is not the same
thing as rate limiting other users to enforce a lower priority, which
is what vacuum_delay does.

> I don't
> really feel a need to put cost_delay stuff into anything that's not used
> by autovacuum.

It seems reasonable to me to hope that one day we might be able to
specify that certain tasks have a lower rate of resource usage than
others.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services