Re: drop/truncate table sucks for large values of shared buffers

On Thu, Jul 2, 2015 at 6:38 PM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>
> On 06/27/2015 07:45 AM, Amit Kapila wrote:
>>
>> Sometime back on one of the PostgreSQL blog [1], there was
>> discussion about the performance of drop/truncate table for
>> large values of shared_buffers and it seems that as the value
>> of shared_buffers increase the performance of drop/truncate
>> table becomes worse. I think those are not often used operations,
>> so it never became priority to look into improving them if possible.
>>
>> I have looked into it and found that the main reason for such
>> a behaviour is that for those operations it traverses whole
>> shared_buffers and it seems to me that we don't need that
>> especially for not-so-big tables. We can optimize that path
>> by looking into buff mapping table for the pages that exist in
>> shared_buffers for the case when table size is less than some
>> threshold (say 25%) of shared buffers.
>>
>> Attached patch implements the above idea and I found that
>> performance doesn't dip much with patch even with large value
>> of shared_buffers. I have also attached script and sql file used
>> to take performance data.
>
>
> I'm marking this as "returned with feedback" in the commitfest. In
addition to the issues raised so far,

Don't you think the approach discussed (delayed cleanup of buffers
during checkpoint scan) is sufficient to fix the issues raised.

> ISTM that the patch makes dropping a very large table with small
shared_buffers slower (DropForkSpecificBuffers() is O(n) where n is the
size of the relation, while the current method is O(shared_buffers))
>

Can you please explain how did you reach that conclusion?
It does only when relation size is less than 25% of shared
buffers.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com