Re: slow dropping of tables, DropRelFileNodeBuffers, tas

On 3 June 2012 19:07, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> 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?

That's enough for me.

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