Re: slow dropping of tables, DropRelFileNodeBuffers, tas

On Wed, May 30, 2012 at 4:10 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> On 30.05.2012 03:40, Sergey Koposov wrote:
>>
>> I was running some tests on PG9.2beta where I'm creating and dropping
>> large number of tables (~ 20000).
>>
>> And I noticed that table dropping was extremely slow -- e.g. like half a
>> second per table.
>>
>> ...
>>
>>
>> I also stopped PG with gdb a few times and it was always at this
>> backtrace:
>>
>> (gdb) bt
>> #0 tas (lock=0x7fa4e3007538 "\001") at
>> ../../../../src/include/storage/s_lock.h:218
>> #1 0x00000000006e6956 in DropRelFileNodeBuffers (rnode=...,
>> forkNum=VISIBILITYMAP_FORKNUM, firstDelBlock=0) at bufmgr.c:2062
>> #2 0x000000000070c014 in smgrdounlink (reln=0x1618210,
>> forknum=VISIBILITYMAP_FORKNUM, isRedo=0 '\000') at smgr.c:354
>> #3 0x000000000051ecf6 in smgrDoPendingDeletes (isCommit=1 '\001') at
>> storage.c:364
>
>
> 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.

If someone drops many tables in the same transaction, could it be made
to stuff them into a hash table and then drop all of them with one
cycle around the buffer pool?  Or is the use case for that too narrow
a use case to be worthwhile?

Cheers,

Jeff