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

Re: slow dropping of tables, DropRelFileNodeBuffers, tas

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Sergey Koposov <koposov(at)ast(dot)cam(dot)ac(dot)uk>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: slow dropping of tables, DropRelFileNodeBuffers, tas
Date: 2012-06-01 00:58:11
Message-ID: CAMkU=1xmcHJA3ma_r5vpyOjWUYQzmUGiqd3LnZkJgtf6-2fWYw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, May 31, 2012 at 11:09 AM, Sergey Koposov <koposov(at)ast(dot)cam(dot)ac(dot)uk> wrote:
> On Thu, 31 May 2012, Simon Riggs wrote:
>
>>
>> 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.
>
>
> Thanks! The patch indeed improved the timings, The dropping of 100 tables in
> a single commit before the patch took ~ 50 seconds, now it takes ~ 5 sec (it
> would be nice to reduce it further though, because the dropping of 10000
> tables still takes ~10 min).

I'm surprised it helped that much.  I thought the most it could
theoretically could help would be a factor of 4.

I tried the initially unlocked test, and for me it cut the time by a
factor of 3.  But I only have a 1GB shared_buffers at the max, I would
expect it help more at larger sizes because there is a constant
overhead not related to scanning the shared buffers which gets diluted
out the larger shared_buffers is.

I added to that a drop-all very similar to what Simon posted and got
another factor of 3.

But, if you can do this during a maintenance window, then just
restarting with a much smaller shared_buffers should give you a much
larger speed up than either or both of these.  If I can extrapolate up
to 10G from my current curve, setting it to 8MB instead would give a
speed up of nearly 400 fold.

Cheers,

Jeff

In response to

pgsql-hackers by date

Next:From: Jeff JanesDate: 2012-06-01 03:44:59
Subject: Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile
Previous:From: Jeff JanesDate: 2012-06-01 00:45:40
Subject: Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

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