Skip site navigation (1) Skip section navigation (2)

Re: slow dropping of tables, DropRelFileNodeBuffers, tas

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, Sergey Koposov <koposov(at)ast(dot)cam(dot)ac(dot)uk>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: slow dropping of tables, DropRelFileNodeBuffers, tas
Date: 2012-06-07 20:52:01
Message-ID: 28119.1339102321@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
>> On 30 May 2012 12:10, Heikki Linnakangas
>> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>>> 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.

With shared_buffers set to 1GB, I see about a 2X reduction in the total
time to drop a simple table, ie
	create table zit(f1 text primary key);
	drop table zit;
(This table definition is chosen to ensure there's an index and a toast
table involved, so several scans of the buffer pool are needed.)  The
DROP goes from about 40ms to about 20ms on a fairly recent Xeon desktop.
So I'm convinced this is a win.

I extended the patch to also cover DropDatabaseBuffers,
FlushRelationBuffers, and FlushDatabaseBuffers, which have got the exact
same type of full-pool scan loop, and committed it.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-06-07 21:54:42
Subject: Re: slow dropping of tables, DropRelFileNodeBuffers, tas
Previous:From: Lonni J FriedmanDate: 2012-06-07 20:08:27
Subject: Re: pg_basebackup blocking all queries with horrible performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group