Re: slow dropping of tables, DropRelFileNodeBuffers, tas

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.

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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com