Re: Relation extension scalability - Mailing list pgsql-hackers
| From | Amit Kapila |
|---|---|
| Subject | Re: Relation extension scalability |
| Date | |
| Msg-id | CAA4eK1LOnxz4Qa_DquqbanSPXscTJXrKexJii8h3gnD9z8UY-A@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 |
On Mon, Feb 29, 2016 at 3:37 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
On Wed, Feb 10, 2016 at 7:06 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
Test2: Identify that improvement in case of multiextend is becuase of avoiding context switch or some other factor, like reusing blocks b/w backend by putting in FSM..
1. Test by just extending multiple blocks and reuse in it's own backend (Don't put in FSM)
Insert 1K record data don't fits in shared buffer 512MB Shared Buffer
Client Base Extend 800 block self use Extend 1000 Block
1 117 131 118
2 111 203 140
3 51 242 178
4 51 231 190
5 52 259 224
6 51 263 243
7 43 253 254
8 43 240 254
16 40 190 243
We can see the same improvement in case of self using the blocks also, It shows that Sharing the blocks between the backend was not the WIN but avoiding context switch was the measure win.
One thing that is slightly unclear is that whether there is any overhead due to buffer eviction especially when the buffer to be evicted is already dirty and needs XLogFlush(). One reason why it might not hurt is that by the time we tries to evict the buffer, corresponding WAL is already flushed by WALWriter or other possibility could be that even if it is getting done during buffer eviction, the impact for same is much lesser. Can we try to measure the number of flush calls which happen during buffer eviction?
2. Tested the Number of ProcSleep during the Run.
This is the simple script of copy 10000 record in one transaction of size 4 BytesBASE CODE PATCH MULTI EXTENDClient Base_TPS ProcSleep Count Extend By 10 Block Proc Sleep Count
2 280 457,506 311 62,641
3 235 1,098,701 358 141,624
4 216 1,155,735 368 188,173What we can see in above test that, in Base code performance is degrading after 2 threads, while Proc Sleep count in increasing with huge amount.Compared to that in Patch, with extending 10 blocks at a time Proc Sleep reduce to ~1/8 and we can see it is constantly scaling.
Proc Sleep test for Insert test when data don't fits in shared buffer and inserting big record of 1024 bytes, is currently running
once I get the data will post the same.
Okay. However, I wonder if the performance data for the case when data doesn't fit into shared buffer also shows similar trend, then it might be worth to try by doing extend w.r.t load in system. I mean to say we can batch the extension requests (as we have done in ProcArrayGroupClearXid) and extend accordingly, if that works out then the benefit could be that we don't need any configurable knob for the same.
pgsql-hackers by date: