Re: Relation extension scalability

On Sat, Mar 26, 2016 at 3:18 PM, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:

> We could go further still and have GetPageWithFreeSpace() always
>> search the last, say, two pages of the FSM in all cases. But that
>> might be expensive. The extra call to RelationGetNumberOfBlocks seems
>> cheap enough here because the alternative is to wait for a contended
>> heavyweight lock.
>>
>
> I will try the test with this also and post the results.
>

**Something went wrong in last mail, seems like become separate thread, so
resending the same mail **

I have changed v14 as per this suggestion and results are same as v14.

I have again measured the relation size, this time directly using size
function so results are better understandable.

Relation Size
-----------------
INSERT : 16000 transaction from 32 Client

Base v13 v14_1
--------- --------- --------
TPS 37 255 112
Rel Size 17GB 17GB 18GB

COPY: 32000 transaction from 32 client
Base v13 v14_1
--------- --------- ---------
TPS 121 823 427
Rel Size 11GB 11GB 11GB

Script are attached in the mail
----------------------------------------=
test_size_ins.sh --> run insert test and calculate relation size.
test_size_copy --> run copy test and relation size
copy_script -> copy pg_bench script used by test_size_copy.sh
insert_script --> insert pg_bench script used by test_size_ins.sh
multi_extend_v14_poc_v1.patch --> modified patch of v14.

I also tried modifying v14 from different different angle.

One is like below-->
-------------------------
In AddExtraBlock
{
I add page to FSM one by one like v13 does.
then update the full FSM tree up till root
}

Results:
----------
1. With this performance is little less than v14 but the problem of extra
relation size is solved.
2. With this we can conclude that extra size of relation in v14 is because
some while extending the pages, its not immediately available and at end
some of the pages are left unused.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com