Re: heap vacuum & cleanup locks

On Tue, Jun 7, 2011 at 3:43 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Tue, Jun 7, 2011 at 8:24 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:
>> On Mon, Jun 6, 2011 at 11:30 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> But I think you've hit the important point here. The problem is not
>>> whether VACUUM waits for the pin, its that the pins can be held for
>>> extended periods.
>>
>> Yes
>>
>>> It makes more sense to try to limit pin hold times than it does to
>>> come up with pin avoidance techniques.
>>
>> Well it's super-exclusive-vacuum-lock avoidance techniques. Why
>> shouldn't it make more sense to try to reduce the frequency and impact
>> of the single-purpose outlier in a non-critical-path instead of
>> burdening every other data reader with extra overhead?
>>
>> I think Robert's plan is exactly right though I would phrase it
>> differently. We should get the exclusive lock, freeze/kill any xids
>> and line pointers, then if the pin-count is 1 do the compaction.
>
> Would that also be possible during recovery?
>
> A similar problem exists with Hot Standby, so I'm worried fixing just
> VACUUMs would be a kluge.

We have to do the same operation on both the master and standby, so if
the master decides to skip the compaction then the slave will skip it
as well (and need not worry about waiting for pin-count 1). But if
the master does the compaction then the slave will have to get a
matching cleanup lock, just as now.

Your idea of somehow adjusting things so that we don't hold the buffer
pin for a long period of time would be better in that regard, but I'm
not sure how to do it. Presumably we could rejigger things to copy
the tuples instead of holding a pin, but that would carry a
performance penalty for the (very common) case where there is no
conflict with VACUUM.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company