Re: PATCH: optimized DROP of multiple tables within a transaction

On Mon, Nov 12, 2012 at 4:36 AM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
> Hi,
>
> I've prepared a slightly updated patch, based on the previous review.
> See it attached.

All changes in v3 patch seem good, however I found some places which requires
cosmetic changes. Please see attached v3.1 patch for those changes.

>> Oops, you're right. I omitted 1-3 and 1-4 in actual measurement, but
>> IMO loading data is necessary to fill the shared buffer up, because
>> # of buffers which are deleted during COMMIT is major factor of this
>> patch. And, yes, I verified that all shared buffers are used　after
>> all loading have been finished.
>
> I don't think it's an important factor, as the original code does this:
>
> for (i = 0; i < NBuffers; i++)
> {
> volatile BufferDesc *bufHdr = &BufferDescriptors[i];
> ...
> }
>
> in the DropRelFileNodeAllBuffers. That loops through all shared buffers
> no matter what, so IMHO the performance in this case depends on the
> total size of the shared buffers. But maybe I'm missing something important.

I worry the effect of "continue" in the loop to the performance. If most of
shared buffers are not used at the moment of DROP, the load of DROP doesn't
contain the overhead of LockBufHdr and subsequent few lines.
Do you expect such situation in your use cases?

> I've done a simple benchmark on my laptop with 2GB shared buffers, it's
> attached in the drop-test.py (it's a bit messy, but it works).
[snip]
> With those parameters, I got these numbers on the laptop:
>
> creating 10000 tables
> all tables created in 3.298694 seconds
> dropping 10000 tables one by one ...
> all tables dropped in 32.692478 seconds
> creating 10000 tables
> all tables created in 3.458178 seconds
> dropping 10000 tables in batches by 100...
> all tables dropped in 3.28268 seconds
>
> So it's 33 seconds vs. 3.3 seconds, i.e. 10x speedup. On AWS we usually
> get larger differences, as we use larger shared buffers and the memory
> is significantly slower there.

Do you have performance numbers of same test on not-patched PG?
This patch aims to improve performance of bulk DROP, so 4th number in
the result above should be compared to 4th number of not-patched PG?

--
Shigeru HANADA