Re: Should I implement DROP INDEX CONCURRENTLY?

On Fri, Sep 9, 2011 at 11:02 PM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
> On Wed, Aug 24, 2011 at 1:04 PM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
>> On Wed, Aug 24, 2011 at 12:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Assuming the issue really is the physical unlinks (which I agree I'd
>>> like to see some evidence for), I wonder whether the problem could be
>>> addressed by moving smgrDoPendingDeletes() to after locks are released,
>>> instead of before, in CommitTransaction/AbortTransaction.  There does
>>> not seem to be any strong reason why we have to do that before lock
>>> release, since incoming potential users of a table should not be trying
>>> to access the old physical storage after that anyway.
>>
>> Alright, since this concern about confirming the expensive part of
>> index dropping has come up a few times but otherwise the waters are
>> warm, I'll go ahead and do some work to pin things down a bit before
>> we continue working on those assumptions.
>>
>
> This suspicion seems to be proven correct; there came an opportunity
> where we were removing some indexes on a live system and I took the
> opportunity to carefully control and time the process.  There's not
> much relationship between size of the index and the delay, but the
> pauses are still very real. On the other hand, the first time this was
> noticed there was significantly higher load.
>
> I'd still like to do something to solve this problem, though: even if
> the time-consuming part of the process is not file unlinking, it's
> clearly something after the AccessExclusiveLock is acquired based on
> our other measurements.

This could well be related to the fact that DropRelFileNodeBuffers()
does a scan of shared_buffers, which is an O(N) approach no matter the
size of the index.

On top of that, taking what Robert Haas mentioned on another thread,
InvalidateBuffer currently calls StretegyFreeBuffer(), which waits for
an ExclusiveLock on the BufFreelistLock. On a busy system this will be
heavily contended, so adding blocks to the freelist only if the lock
is free seems warranted.

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