Postgres Pro
Downloads
Home   >   mailing lists

Re: Relation extension scalability - Mailing list pgsql-hackers

From Dilip Kumar
Subject Re: Relation extension scalability
Date
Msg-id CAFiTN-sn1HLEOGWm5-U9xmzNJDdXpAMHYeWh3i-u-pP5rcFROQ@mail.gmail.com
Whole thread Raw
In response to Re: Relation extension scalability  (Dilip Kumar <dilipbalaut@gmail.com>)
Responses Re: Relation extension scalability
List pgsql-hackers
Tree view
On Fri, Dec 18, 2015 at 10:51 AM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
On Sun, Jul 19 2015 9:37 PM Andres Wrote,

> The situation the read() protect us against is that two backends try to
> extend to the same block, but after one of them succeeded the buffer is
> written out and reused for an independent page. So there is no in-memory
> state telling the slower backend that that page has already been used.

I was looking into this patch, and done some performance testing..

Currently i have done testing my my local machine, later i will perform on big machine once i get access to that.

Just wanted to share the current result what i get i my local machine
Machine conf (Intel(R) Core(TM) i7-4870HQ CPU @ 2.50GHz, 8 core and 16GM of RAM).

Test Script:
./psql -d postgres -c "COPY (select g.i::text FROM generate_series(1, 10000) g(i)) TO '/tmp/copybinarywide' WITH BINARY";

./psql -d postgres -c "truncate table data"
./psql -d postgres -c "checkpoint"
./pgbench -f copy_script -T 120 -c$ -j$ postgres

This time i have done some testing on big machine with 64 physical cores @ 2.13GHz and 50GB of RAM

There is performance comparison of base, extend without RelationExtensionLock patch given by Andres and
multi-extend patch (this will extend the multiple blocks at a time based on a configuration parameter.)

Problem Analysis:
------------------------
1. With base code when i try to observe the problem using perf and other method (gdb), i found that RelationExtensionLock is main bottleneck.
2. Then after using RelationExtensionLock Free patch, i observed now contention is FileWrite (All backends are trying to extend the file.)

Performance Summary and Analysis:
------------------------------------------------
1. In my performance results Multi Extend shown best performance and scalability.
2. I think by extending in multiple blocks we solves both the problem(Extension Lock and Parallel File Write).
3. After extending one Block immediately adding to FSM so in most of the cases other backend can directly find it without taking extension lock. 

Currently the patch is in initial stage, i have done only test performance and pass the regress test suit.

Open problems
-----------------------------
1. After extending the page we are adding it directly to FSM, so if vacuum find this page as new it will give WARNING.
2. In RelationGetBufferForTuple, when PageIsNew, we are doing PageInit, same need to be consider for index cases.


Test Script:
-------------------------
./psql -d postgres -c "COPY (select g.i::text FROM generate_series(1, 10000) g(i)) TO '/tmp/copybinarywide' WITH BINARY";

./psql -d postgres -c "truncate table data"
./psql -d postgres -c "checkpoint"
./pgbench -f copy_script -T 120 -c$ -j$ postgres

Performanec Data:
--------------------------
There are Three code base for performance
1. Base Code

3. Multi extend patch attached in the mail.
#extend_num_pages : This this new config parameter to tell how many extra page extend in case of normal extend..
may be it will give more control to user if we make it relation property.

I will work on the patch for this CF, so adding it to CF.


Shared Buffer 48 GB


ClientsBase (TPS)Lock Free Patch
Multi-extend extend_num_pages=5
1142138148
2251253280
4237416464
8168491575
16141448404
32122337332




Shared Buffer 64 MB


ClientsBase (TPS)Multi-extend extend_num_pages=5
1140148
2252266
4229437
8153475
16132364


Regards,
Dilip Kumar
 
Attachment

pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: proposal: PL/Pythonu - function ereport
Next
From: Amit Kapila
Date:
Subject: Re: [PATCH] Refactoring of LWLock tranches