Re: slow dropping of tables, DropRelFileNodeBuffers, tas

On Thu, May 31, 2012 at 5:04 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 30 May 2012 12:10, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>
>> Hmm, we do this in smgrDoPendingDeletes:
>>
>> for (i = 0; i <= MAX_FORKNUM; i++)
>> {
>>        smgrdounlink(srel, i, false);
>> }
>>
>> So we drop the buffers for each relation fork separately, which means that
>> we scan the buffer pool four times. Relation forks in 8.4 introduced that
>> issue, and 9.1 made it worse by adding another fork for unlogged tables.
>> With some refactoring, we could scan the buffer pool just once. That would
>> help a lot.
>
> That struck me as a safe and easy optimisation. This was a problem I'd
> been trying to optimise for 9.2, so I've written a patch that appears
> simple and clean enough to be applied directly.

By directly do you mean before the fork/commit fest begins?

>
>> Also, I wonder if DropRelFileNodeBuffers() could scan the pool without
>> grabbing the spinlocks on every buffer? It could do an unlocked test first,
>> and only grab the spinlock on buffers that need to be dropped.
>
> Sounds less good and we'd need reasonable proof it actually did
> anything useful without being dangerous.

Doing an initial unlocked test speeds things up another 2.69 fold (on
top of 3.55 for your patch) for me, with 1GB of shared buffers. That
seems like it should be worthwhile.

How do we go about getting reasonable proof that it is safe?

Thanks,

Jeff