Re: [HACKERS] Relation extension scalability

On Fri, Apr 8, 2016 at 11:38 AM, Robert Haas wrote: > Yeah. I've committed the patch now, with some cosmetic cleanup. > Thanks Robert !!! -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] Relation extension scalability

On Tue, Apr 5, 2016 at 1:05 PM, Peter Geoghegan wrote: > On Tue, Apr 5, 2016 at 10:02 AM, Robert Haas wrote: >> So the first thing here is that the patch seems to be a clear win in >> this test. For a single copy, it seems to be pretty much a wash. >> When running 4 copies in parallel, it is abo

Re: [HACKERS] Relation extension scalability

On Tue, Apr 5, 2016 at 10:02 AM, Robert Haas wrote: > So the first thing here is that the patch seems to be a clear win in > this test. For a single copy, it seems to be pretty much a wash. > When running 4 copies in parallel, it is about 20-25% faster with both > logged and unlogged tables. The

Re: [HACKERS] Relation extension scalability

On Thu, Mar 31, 2016 at 9:03 AM, Dilip Kumar wrote: > If you need some more information please let me know ? I repeated the testing described in http://www.postgresql.org/message-id/ca+tgmoyouqf9cgcpgygngzqhcy-gcckryaqqtdu8kfe4n6h...@mail.gmail.com on a MacBook Pro (OS X 10.8.5, 2.4 GHz Intel Cor

Re: [HACKERS] Relation extension scalability

On Thu, Mar 31, 2016 at 4:28 PM, Robert Haas wrote: > Yeah, kind of. But obviously if we could make the limit smaller > without hurting performance, that would be better. > > Per my note yesterday about performance degradation with parallel > COPY, I wasn't able to demonstrate that this patch gi

Re: [HACKERS] Relation extension scalability

On Thu, Mar 31, 2016 at 4:28 PM, Robert Haas wrote: > On Thu, Mar 31, 2016 at 12:59 AM, Dilip Kumar > wrote: > > On Tue, Mar 29, 2016 at 10:08 AM, Amit Kapila > > wrote: > >> Yes, that makes sense. One more point is that if the reason for v13 > >> giving better performance is extra blocks (whi

Re: [HACKERS] Relation extension scalability

On Thu, Mar 31, 2016 at 12:59 AM, Dilip Kumar wrote: > On Tue, Mar 29, 2016 at 10:08 AM, Amit Kapila > wrote: >> Yes, that makes sense. One more point is that if the reason for v13 >> giving better performance is extra blocks (which we believe in certain cases >> can leak till the time Vacuum up

Re: [HACKERS] Relation extension scalability

On Thu, Mar 31, 2016 at 10:29 AM, Dilip Kumar wrote: > > On Tue, Mar 29, 2016 at 10:08 AM, Amit Kapila > wrote: > >> Yes, that makes sense. One more point is that if the reason for v13 >> giving better performance is extra blocks (which we believe in certain >> cases can leak till the time Vacu

Re: [HACKERS] Relation extension scalability

On Tue, Mar 29, 2016 at 10:08 AM, Amit Kapila wrote: > Yes, that makes sense. One more point is that if the reason for v13 > giving better performance is extra blocks (which we believe in certain > cases can leak till the time Vacuum updates the FSM tree), do you think it > makes sense to once t

Re: [HACKERS] Relation extension scalability

On Wed, Mar 30, 2016 at 7:51 AM, Dilip Kumar wrote: > + if (lockWaiters) > + /* > + * Here we are using same freespace for all the Blocks, but that > + * is Ok, because all are newly added blocks and have same freespace > + * And even some block which we just added to FreespaceMap above, is > + *

Re: [HACKERS] Relation extension scalability

On Wed, Mar 30, 2016 at 7:19 AM, Robert Haas wrote: Thanks for review and better comments.. > hio.c: In function ‘RelationGetBufferForTuple’: > hio.c:231:20: error: ‘freespace’ may be used uninitialized in this > function [-Werror=uninitialized] > hio.c:185:7: note: ‘freespace’ was declared her

Re: [HACKERS] Relation extension scalability

On Tue, Mar 29, 2016 at 1:29 PM, Dilip Kumar wrote: > Attaching new version v18 > > - Some cleanup work on v17. > - Improved UpdateFreeSpaceMap function. > - Performance and space utilization are same as V17 Looks better. Here's a v19 that I hacked on a bit. Unfortunately, one compiler I tried

Re: [HACKERS] Relation extension scalability

On Tue, Mar 29, 2016 at 2:09 PM, Dilip Kumar wrote: > > Attaching new version v18 - Some cleanup work on v17. - Improved *UpdateFreeSpaceMap *function. - Performance and space utilization are same as V17 -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com multi_extend_v18.pat

Re: [HACKERS] Relation extension scalability

On Tue, Mar 29, 2016 at 7:26 AM, Robert Haas wrote: > Well, it's less important in that case, but I think it's still worth > doing. Some people are going to do just plain GetPageWithFreeSpace(). > I am attaching new version v17. Its like this... In *RelationAddExtraBlocks* { -- Add Block

Re: [HACKERS] Relation extension scalability

On Tue, Mar 29, 2016 at 3:21 AM, Petr Jelinek wrote: > On 28/03/16 14:46, Dilip Kumar wrote: > >> >> Conclusion: >> --- >> 1. I think v15 is solving the problem exist with v13 and performance >> is significantly high compared to base, and relation size is also >> s

Re: [HACKERS] Relation extension scalability

On Mon, Mar 28, 2016 at 8:46 AM, Dilip Kumar wrote: > Found one problem with V15, so sending the new version. > In V15 I am taking prev_blkno as targetBlock instead it should be the last > block of the relation at that time. Attaching new patch. BlockNumber targetBlock, -othe

Re: [HACKERS] Relation extension scalability

On Sun, Mar 27, 2016 at 9:51 PM, Dilip Kumar wrote: > I think this is better option, Since we will search last two pages of FSM > tree, then no need to update the upper level of the FSM tree. Right ? Well, it's less important in that case, but I think it's still worth doing. Some people are goin

Re: [HACKERS] Relation extension scalability

On 28/03/16 14:46, Dilip Kumar wrote: Conclusion: --- 1. I think v15 is solving the problem exist with v13 and performance is significantly high compared to base, and relation size is also stable, So IMHO V15 is winner over other solution, what other thinks ? I

Re: [HACKERS] Relation extension scalability

On Mon, Mar 28, 2016 at 3:02 PM, Dilip Kumar wrote: > 1. Relation Size : No change in size, its same as base and v13 > > 2. INSERT 1028 Byte 1000 tuple performance > --- > Client base v13 v15 > 1 117 124 122 > 2 111 126 123 > 4 51 128 125 >

Re: [HACKERS] Relation extension scalability

On Mon, Mar 28, 2016 at 7:21 AM, Dilip Kumar wrote: > I agree with that conclusion. I'm not quite sure where that leaves >> us, though. We can go back to v13, but why isn't that producing extra >> pages? It seems like it should: whenever a bulk extend rolls over to >> a new FSM page, concurren

Re: [HACKERS] Relation extension scalability

On Mon, Mar 28, 2016 at 11:00 AM, Amit Kapila wrote: > I have not debugged the flow, but by looking at v13 code, it looks like it > will search both old and new. In > function > GetPageWithFreeSpaceExtended()->fsm_search_from_addr()->fsm_search_avail(), > the basic idea of search is: Start the

Re: [HACKERS] Relation extension scalability

On Mon, Mar 28, 2016 at 1:55 AM, Robert Haas wrote: > > On Sun, Mar 27, 2016 at 8:00 AM, Dilip Kumar wrote: > > > 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

Re: [HACKERS] Relation extension scalability

On Mon, Mar 28, 2016 at 1:55 AM, Robert Haas wrote: > > 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 > > } > > Not following this. Did you attach this version? >

Re: [HACKERS] Relation extension scalability

On Sun, Mar 27, 2016 at 8:00 AM, Dilip Kumar wrote: > Relation Size > - > INSERT : 16000 transaction from 32 Client > > Base v13 v14_1 > - - > TPS 37 255

Re: [HACKERS] Relation extension scalability

On Sat, Mar 26, 2016 at 3:18 PM, Dilip Kumar 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 al

Re: [HACKERS] Relation extension scalability

On Sat, Mar 26, 2016 at 3:18 PM, Dilip Kumar wrote: > 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 w

Re: [HACKERS] Relation extension scalability

On Sat, Mar 26, 2016 at 8:07 AM, Robert Haas wrote: > I think we need to start testing these patches not only in terms of > how *fast* they are but how *large* the relation ends up being when > we're done. A patch that inserts the rows slower but the final > relation is smaller may be better ove

Re: [HACKERS] Relation extension scalability

On Fri, Mar 25, 2016 at 1:05 PM, Dilip Kumar wrote: > On Fri, Mar 25, 2016 at 3:04 AM, Robert Haas wrote: >> 1. Callers who use GetPageWithFreeSpace() rather than >> GetPageFreeSpaceExtended() will fail to find the new pages if the >> upper map levels haven't been updated by VACUUM. >> >> 2. Even

Re: [HACKERS] Relation extension scalability

On Fri, Mar 25, 2016 at 3:04 AM, Robert Haas wrote: > 1. Callers who use GetPageWithFreeSpace() rather than > GetPageFreeSpaceExtended() will fail to find the new pages if the > upper map levels haven't been updated by VACUUM. > > 2. Even callers who use GetPageFreeSpaceExtended() may fail to fin

Re: [HACKERS] Relation extension scalability

On Thu, Mar 24, 2016 at 7:17 AM, Dilip Kumar wrote: >> Yet another possibility could be to call it as >> GetPageWithFreeSpaceExtended and call it from GetPageWithFreeSpace with >> value of oldPage as InvalidBlockNumber. > > Yes I like this.. Changed the same. After thinking about this some more,

Re: [HACKERS] Relation extension scalability

On Thu, Mar 24, 2016 at 6:13 PM, Amit Kapila wrote: > > 1. > +GetPageWithFreeSpaceUsingOldPage(Relation rel, BlockNumber oldPage, > + Size spaceNeeded) > { > .. > + /* > + * If fsm_set_and_search found a suitable new block, return that. > + * Otherwise, search as usual. > + */ > .. > } > > In the

Re: [HACKERS] Relation extension scalability

On Thu, Mar 24, 2016 at 1:48 PM, Petr Jelinek wrote: > > On 24/03/16 07:04, Dilip Kumar wrote: >> >> >> On Thu, Mar 24, 2016 at 10:44 AM, Robert Haas > > wrote: >> >> On Wed, Mar 23, 2016 at 9:43 PM, Amit Kapila >> mailto:amit.kapil...@gmail.com>> wrote: >>

Re: [HACKERS] Relation extension scalability

On 24/03/16 07:04, Dilip Kumar wrote: On Thu, Mar 24, 2016 at 10:44 AM, Robert Haas mailto:robertmh...@gmail.com>> wrote: On Wed, Mar 23, 2016 at 9:43 PM, Amit Kapila mailto:amit.kapil...@gmail.com>> wrote: > RecordAndGetPageWithFreeSpace() tries to search from the oldPage passed to

Re: [HACKERS] Relation extension scalability

On Thu, Mar 24, 2016 at 10:44 AM, Robert Haas wrote: > On Wed, Mar 23, 2016 at 9:43 PM, Amit Kapila > wrote: > > RecordAndGetPageWithFreeSpace() tries to search from the oldPage passed > to > > it, rather than from top, so even if RecordPageWithFreeSpace() doesn't > > update till root, it will

Re: [HACKERS] Relation extension scalability

On Wed, Mar 23, 2016 at 9:43 PM, Amit Kapila wrote: > RecordAndGetPageWithFreeSpace() tries to search from the oldPage passed to > it, rather than from top, so even if RecordPageWithFreeSpace() doesn't > update till root, it will be able to search the newly added page. I agree > with whatever yo

Re: [HACKERS] Relation extension scalability

On Thu, Mar 24, 2016 at 12:09 AM, Robert Haas wrote: > > On Tue, Mar 22, 2016 at 1:12 PM, Petr Jelinek wrote: > > I've read this over several times and looked at > RecordAndGetPageWithFreeSpace() and I'm still confused. First of all, > if the lock was acquired by some other backend which did > R

Re: [HACKERS] Relation extension scalability

On 23/03/16 20:43, Robert Haas wrote: On Wed, Mar 23, 2016 at 3:33 PM, Petr Jelinek wrote: Btw thinking about it some more, ISTM that not finding the block and just doing the extension if the FSM wasn't extended correctly previously is probably cleaner behavior than what we do now. The reasonin

Re: [HACKERS] Relation extension scalability

On Wed, Mar 23, 2016 at 3:33 PM, Petr Jelinek wrote: > Btw thinking about it some more, ISTM that not finding the block and just > doing the extension if the FSM wasn't extended correctly previously is > probably cleaner behavior than what we do now. The reasoning for that > opinion is that if the

Re: [HACKERS] Relation extension scalability

On 23/03/16 20:19, Petr Jelinek wrote: On 23/03/16 20:01, Robert Haas wrote: On Wed, Mar 23, 2016 at 2:52 PM, Petr Jelinek wrote: Second, if the other backend extended the relation in some other manner and did not extend the FSM, how does calling RecordAndGetPageWithFreeSpace help? As far as

Re: [HACKERS] Relation extension scalability

On 23/03/16 20:01, Robert Haas wrote: On Wed, Mar 23, 2016 at 2:52 PM, Petr Jelinek wrote: Second, if the other backend extended the relation in some other manner and did not extend the FSM, how does calling RecordAndGetPageWithFreeSpace help? As far as I can see, GetPageWithFreeSpace and Reco

Re: [HACKERS] Relation extension scalability

On Wed, Mar 23, 2016 at 2:52 PM, Petr Jelinek wrote: >> Second, if the other backend extended the relation in >> some other manner and did not extend the FSM, how does calling >> RecordAndGetPageWithFreeSpace help? As far as I can see, >> GetPageWithFreeSpace and RecordAndGetPageWithFreeSpace are

Re: [HACKERS] Relation extension scalability

On 23/03/16 19:39, Robert Haas wrote: On Tue, Mar 22, 2016 at 1:12 PM, Petr Jelinek wrote: I also think the code simplicity makes this worth it. Agreed. I went over this patch and did a cleanup pass today. I discovered that the LockWaiterCount() function was broken if you try to tried to u

Re: [HACKERS] Relation extension scalability

On Tue, Mar 22, 2016 at 1:12 PM, Petr Jelinek wrote: > I also think the code simplicity makes this worth it. Agreed. I went over this patch and did a cleanup pass today. I discovered that the LockWaiterCount() function was broken if you try to tried to use it on a lock that you didn't hold or

Re: [HACKERS] Relation extension scalability

On 22/03/16 10:15, Dilip Kumar wrote: On Mon, Mar 21, 2016 at 8:10 PM, Amit Kapila mailto:amit.kapil...@gmail.com>> wrote: 11. +{ +/* +* First try to get the lock in no-wait mode, if succeed extend one + * block, else get the lock in normal mode and after we get the loc

Re: [HACKERS] Relation extension scalability

On Mon, Mar 21, 2016 at 8:10 PM, Amit Kapila wrote: > Review comments: > > Thanks for the review, Please find my response inline.. > 1. > /* > + * RelationAddOneBlock > + * > + * Extend relation by one block and lock the buffer > + */ > +static Buffer > +RelationAddOneBlock(Relation relation,

Re: [HACKERS] Relation extension scalability

On Fri, Mar 18, 2016 at 2:38 PM, Dilip Kumar wrote: > > > On Thu, Mar 17, 2016 at 1:31 PM, Petr Jelinek wrote: >> >> Great. >> >> Just small notational thing, maybe this would be simpler?: >> extraBlocks = Min(512, lockWaiters * 20); > > > Done, new patch attached. > Review comments: 1. /* + *

Re: [HACKERS] Relation extension scalability

On Mon, Mar 14, 2016 at 8:26 AM, Petr Jelinek wrote: > Well any value we choose will be very arbitrary. If we look at it from the > point of maximum absolute disk space we allocate for relation at once, > the 4MB limit would represent 2.5 orders of magnitude change. That sounds > like enough for

Re: [HACKERS] Relation extension scalability

On Thu, Mar 17, 2016 at 1:31 PM, Petr Jelinek wrote: > Great. > > Just small notational thing, maybe this would be simpler?: > extraBlocks = Min(512, lockWaiters * 20); > Done, new patch attached. -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com diff --git a/src/backend/acces

Re: [HACKERS] Relation extension scalability

On 17/03/16 04:42, Dilip Kumar wrote: On Mon, Mar 14, 2016 at 8:26 AM, Petr Jelinek mailto:p...@2ndquadrant.com>> wrote: Well any value we choose will be very arbitrary. If we look at it from the point of maximum absolute disk space we allocate for relation at once, the 4MB limit wo

Re: [HACKERS] Relation extension scalability

On 14/03/16 03:29, Dilip Kumar wrote: On Mon, Mar 14, 2016 at 5:02 AM, Jim Nasby mailto:jim.na...@bluetreble.com>> wrote: Well, 16MB is 2K pages, which is what you'd get if 100 connections were all blocked and we're doing 20 pages per waiter. That seems like a really extreme scenari

Re: [HACKERS] Relation extension scalability

On Mon, Mar 14, 2016 at 5:02 AM, Jim Nasby wrote: > Well, 16MB is 2K pages, which is what you'd get if 100 connections were > all blocked and we're doing 20 pages per waiter. That seems like a really > extreme scenario, so maybe 4MB is a good compromise. That's unlikely to be > hit in most cases,

Re: [HACKERS] Relation extension scalability

On 3/11/16 9:57 PM, Petr Jelinek wrote: I also think some kind of clamp is a good idea. It's not that uncommon to run max_connections significantly higher than 100, so the extension could be way larger than 16MB. In those cases this patch could actually make things far worse as ev

Re: [HACKERS] Relation extension scalability

On Sat, Mar 12, 2016 at 8:37 AM, Amit Kapila wrote: > Can you post the numbers for 1, 5, 10, 15, 25 or whatever other multiplier > you have tried, so that it is clear that 20 is best? I had Tried with 1, 10, 20 and 50. 1. With base code it was almost the same as base code. 2. With 10 thread d

Re: [HACKERS] Relation extension scalability

On 12/03/16 01:01, Jim Nasby wrote: On 3/11/16 5:14 PM, Petr Jelinek wrote: I don't really understand this part about concurrent DDL. If there were concurrent DDL going on, presumably other backends would be blocked on the relation lock, not the relation extension lock - and it doesn't seem lik

Re: [HACKERS] Relation extension scalability

On 12/03/16 03:46, Dilip Kumar wrote: On Sat, Mar 12, 2016 at 5:31 AM, Jim Nasby mailto:jim.na...@bluetreble.com>> wrote: FWIW, this is definitely a real possibility in any shop that has very high downtime costs and high transaction rates. I also think some kind of clamp is a good

Re: [HACKERS] Relation extension scalability

On Sat, Mar 12, 2016 at 8:16 AM, Dilip Kumar wrote: > > > On Sat, Mar 12, 2016 at 5:31 AM, Jim Nasby wrote: >> >> FWIW, this is definitely a real possibility in any shop that has very high downtime costs and high transaction rates. >> >> I also think some kind of clamp is a good idea. It's not th

Re: [HACKERS] Relation extension scalability

On Sat, Mar 12, 2016 at 5:31 AM, Jim Nasby wrote: > FWIW, this is definitely a real possibility in any shop that has very high > downtime costs and high transaction rates. > > I also think some kind of clamp is a good idea. It's not that uncommon to > run max_connections significantly higher than

Re: [HACKERS] Relation extension scalability

On 3/11/16 5:14 PM, Petr Jelinek wrote: I don't really understand this part about concurrent DDL. If there were concurrent DDL going on, presumably other backends would be blocked on the relation lock, not the relation extension lock - and it doesn't seem likely that you'd often have a huge pile

Re: [HACKERS] Relation extension scalability

On 11/03/16 22:29, Robert Haas wrote: On Thu, Mar 10, 2016 at 8:54 PM, Petr Jelinek wrote: I am not talking about extension locks, the lock queue can be long because there is concurrent DDL for example and then once DDL finishes suddenly 100 connections that tried to insert into table will try

Re: [HACKERS] Relation extension scalability

On Thu, Mar 10, 2016 at 8:54 PM, Petr Jelinek wrote: > I am not talking about extension locks, the lock queue can be long because > there is concurrent DDL for example and then once DDL finishes suddenly 100 > connections that tried to insert into table will try to get extension lock > and this wi

Re: [HACKERS] Relation extension scalability

On 11/03/16 02:44, Dilip Kumar wrote: On Fri, Mar 11, 2016 at 12:04 AM, Petr Jelinek mailto:p...@2ndquadrant.com>> wrote: Thanks for looking.. Those look good. The patch looks good in general now. I am bit scared by the lockWaiters * 20 as it can result in relatively big changes in

Re: [HACKERS] Relation extension scalability

On Fri, Mar 11, 2016 at 12:04 AM, Petr Jelinek wrote: Thanks for looking.. Those look good. The patch looks good in general now. I am bit scared by > the lockWaiters * 20 as it can result in relatively big changes in rare > corner cases when for example a lot of backends were waiting for lock on

Re: [HACKERS] Relation extension scalability

On 10/03/16 09:57, Dilip Kumar wrote: On Thu, Mar 10, 2016 at 7:55 AM, Petr Jelinek mailto:p...@2ndquadrant.com>> wrote: Thanks for the comments.. Hmm, why did you remove the comment above the call to UnlockRelationForExtension? While re factoring I lose this comment.. Fixed it I

Re: [HACKERS] Relation extension scalability

On Thu, Mar 10, 2016 at 7:55 AM, Petr Jelinek wrote: Thanks for the comments.. > Hmm, why did you remove the comment above the call to > UnlockRelationForExtension? While re factoring I lose this comment.. Fixed it > It still seems relevant, maybe with some minor modification? > > Also there

Re: [HACKERS] Relation extension scalability

On 10/03/16 02:53, Dilip Kumar wrote: Attaching a latest patch. Hmm, why did you remove the comment above the call to UnlockRelationForExtension? It still seems relevant, maybe with some minor modification? Also there is a bit of whitespace mess inside the conditional lock block. -- Pe

Re: [HACKERS] Relation extension scalability

On Wed, Mar 9, 2016 at 1:39 AM, Robert Haas wrote: > LockWaiterCount() bravely accesses a shared memory data structure that > is mutable with no locking at all. That might actually be safe for > our purposes, but I think it would be better to take the partition > lock in shared mode if it doesn'

Re: [HACKERS] Relation extension scalability

On Tue, Mar 8, 2016 at 11:20 AM, Dilip Kumar wrote: > I have come up with this patch.. > > If this approach looks fine then I will prepare final patch (more comments, > indentation, and improve some code) and do some long run testing (current > results are 5 mins run). > > Idea is same what Robert

Re: [HACKERS] Relation extension scalability

On Tue, Mar 8, 2016 at 8:34 PM, Amit Kapila wrote: > > >> Hmm. Can we drive this off of the heavyweight lock manager's idea of > > >> how big the relation extension lock wait queue is, instead of adding > > >> more stuff to PGPROC? > > > > > > One idea to make it work without adding additional s

Re: [HACKERS] Relation extension scalability

On Tue, Mar 8, 2016 at 7:23 PM, Robert Haas wrote: > > On Tue, Mar 8, 2016 at 4:27 AM, Amit Kapila wrote: > >> Hmm. Can we drive this off of the heavyweight lock manager's idea of > >> how big the relation extension lock wait queue is, instead of adding > >> more stuff to PGPROC? > > > > One ide

Re: [HACKERS] Relation extension scalability

On Tue, Mar 8, 2016 at 4:27 AM, Amit Kapila wrote: >> Hmm. Can we drive this off of the heavyweight lock manager's idea of >> how big the relation extension lock wait queue is, instead of adding >> more stuff to PGPROC? > > One idea to make it work without adding additional stuff in PGPROC is tha

Re: [HACKERS] Relation extension scalability

On Mon, Mar 7, 2016 at 8:34 PM, Robert Haas wrote: > > On Fri, Mar 4, 2016 at 11:49 PM, Amit Kapila wrote: > > I think one thing which needs more thoughts about this approach is that we > > need to maintain some number of slots so that group extend for different > > relations can happen in parall

Re: [HACKERS] Relation extension scalability

On Fri, Mar 4, 2016 at 11:49 PM, Amit Kapila wrote: > I think one thing which needs more thoughts about this approach is that we > need to maintain some number of slots so that group extend for different > relations can happen in parallel. Do we want to provide simultaneous > extension for 1, 2,

Re: [HACKERS] Relation extension scalability

On Fri, Mar 4, 2016 at 9:59 PM, Robert Haas wrote: > > On Fri, Mar 4, 2016 at 12:06 AM, Dilip Kumar wrote: > > I have tried the approach of group extend, > > > > 1. We convert the extension lock to TryLock and if we get the lock then > > extend by one block.2. > > 2. If don't get the Lock then us

Re: [HACKERS] Relation extension scalability

Robert Haas writes: > This approach seems good to me, and the performance results look very > positive. The nice thing about this is that there is not a > user-configurable knob; the system automatically determines when > larger extensions are needed, which will mean that real-world users > are m

Re: [HACKERS] Relation extension scalability

On Fri, Mar 4, 2016 at 12:06 AM, Dilip Kumar wrote: > I have tried the approach of group extend, > > 1. We convert the extension lock to TryLock and if we get the lock then > extend by one block.2. > 2. If don't get the Lock then use the Group leader concep where only one > process will extend for

Re: [HACKERS] Relation extension scalability

On Wed, Mar 2, 2016 at 10:31 AM, Dilip Kumar wrote: > 1. One option can be as you suggested like ProcArrayGroupClearXid, With > some modification, because when we wait for the request and extend w.r.t > that, may be again we face the Context Switch problem, So may be we can > extend in some multi

Re: [HACKERS] Relation extension scalability

On Tue, Mar 1, 2016 at 4:36 PM, Amit Kapila wrote: > 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 trie

Re: [HACKERS] Relation extension scalability

On Mon, Feb 29, 2016 at 3:37 PM, Dilip Kumar wrote: > > On Wed, Feb 10, 2016 at 7:06 PM, Dilip Kumar > 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

Re: [HACKERS] Relation extension scalability

On Wed, Feb 10, 2016 at 7:06 PM, Dilip Kumar wrote: > I have tested Relation extension patch from various aspects and performance results and other statistical data are explained in the mail. Test 1: Identify the Heavy Weight lock is the Problem or the Actual Context Switch 1. I converted the Re

Re: [HACKERS] Relation extension scalability

On 2016-02-10 10:32:44 +0530, Dilip Kumar wrote: > On Fri, Feb 5, 2016 at 4:50 PM, Amit Kapila wrote: > > > > Could you also measure how this behaves for an INSERT instead of a COPY > > > workload? > > > > > I think such a test will be useful. > > > > I have measured the performance with insert

Re: [HACKERS] Relation extension scalability

On Fri, Feb 5, 2016 at 4:50 PM, Amit Kapila wrote: > > Could you also measure how this behaves for an INSERT instead of a COPY > > workload? > > I think such a test will be useful. > I have measured the performance with insert to see the behavior when it don't use strategy. I have tested multipl

Re: [HACKERS] Relation extension scalability

On Tue, Feb 2, 2016 at 9:19 PM, Andres Freund wrote: > > On 2016-01-28 16:53:08 +0530, Dilip Kumar wrote: > > test_script: > > > > ./psql -d postgres -c "truncate table data" > > ./psql -d postgres -c "checkpoint" > > ./pgbench -f copy_script -T 120 -c$ -j$ postgres > > > > Shared Buf

Re: [HACKERS] Relation extension scalability

Andres Freund wrote: > Could you also measure how this behaves for [...] While we're proposing benchmark cases -- I remember this being an issue with toast tables getting very large values of xml which causes multiple toast pages to be extended for each new value inserted. If there are multiple

Re: [HACKERS] Relation extension scalability

On 2016-01-28 16:53:08 +0530, Dilip Kumar wrote: > test_script: > > ./psql -d postgres -c "truncate table data" > ./psql -d postgres -c "checkpoint" > ./pgbench -f copy_script -T 120 -c$ -j$ postgres > > Shared Buffer48GB > Table:Unlogged Table > ench -c$ -j$ -f -M Prepared po

Re: [HACKERS] Relation extension scalability

On Tue, Feb 2, 2016 at 10:49 AM, Andres Freund wrote: > I'm doubtful that anything that does the victim buffer search while > holding the extension lock will actually scale in a wide range of > scenarios. The copy scenario here probably isn't too bad because the > copy ring buffes are in use, and

Re: [HACKERS] Relation extension scalability

On 2016-02-02 10:12:38 -0500, Robert Haas wrote: > Here's a sketch of another approach to this problem. Get rid of the > relation extension lock. Instead, have an array of, say, 256 lwlocks. > Each one protects the extension of relations where hash(relfilenode) % > 256 maps to that lock. To exte

Re: [HACKERS] Relation extension scalability

On Thu, Jan 28, 2016 at 6:23 AM, Dilip Kumar wrote: > [ new patch ] This patch contains a useless hunk and also code not in PostgreSQL style. Get pgindent set up and it will do it correctly for you, or look at the style of the surrounding code. What I'm a bit murky about is *why* this should be

Re: [HACKERS] Relation extension scalability

On Thu, Jan 28, 2016 at 4:53 PM, Dilip Kumar wrote: > I did not find in regression in normal case. > Note: I tested it with previous patch extend_num_pages=10 (guc parameter) > so that we can see any impact on overall system. > Just forgot to mentioned That i have run pgbench read-write case. S

Re: [HACKERS] Relation extension scalability

On Mon, Jan 25, 2016 at 11:59 AM, Dilip Kumar wrote: 1. >> Patch is not getting compiled. >> >> 1>src/backend/access/heap/hio.c(480): error C2065: 'buf' : undeclared >> identifier >> > Oh, My mistake, my preprocessor is ignoring this error and relacing it > with BLKSIZE > FIXED > 3. I think yo

Re: [HACKERS] Relation extension scalability

On Sat, Jan 23, 2016 at 4:28 PM, Amit Kapila wrote: > I found one more problem with patch. > > ! UnlockReleaseBuffer(buffer); > ! RecordPageWithFreeSpace(relation, BufferGetBlockNumber(buffer), > freespace); > > You can't call BufferGetBlockNumber(buffer) after releasing > the pin on buffer which

Re: [HACKERS] Relation extension scalability

On Sat, Jan 23, 2016 at 12:19 PM, Amit Kapila wrote > > > Few comments about patch: > Thanks for reviewing.. > 1. > Patch is not getting compiled. > > 1>src/backend/access/heap/hio.c(480): error C2065: 'buf' : undeclared > identifier > 1>src/backend/access/heap/hio.c(480): error C2065: 'buf' : u

Re: [HACKERS] Relation extension scalability

On Sat, Jan 23, 2016 at 12:19 PM, Amit Kapila wrote: > On Tue, Jan 12, 2016 at 2:41 PM, Dilip Kumar > wrote: > >> On Thu, Jan 7, 2016 at 4:53 PM, Andres Freund wrote: >> >>> On 2016-01-07 16:48:53 +0530, Amit Kapila wrote: >>> >>> I think it's a worthwhile approach to pursue. But until it actua

Re: [HACKERS] Relation extension scalability

On Tue, Jan 12, 2016 at 2:41 PM, Dilip Kumar wrote: > On Thu, Jan 7, 2016 at 4:53 PM, Andres Freund wrote: > >> On 2016-01-07 16:48:53 +0530, Amit Kapila wrote: >> >> I think it's a worthwhile approach to pursue. But until it actually >> fixes the problem of leaving around uninitialized pages I

Re: [HACKERS] Relation extension scalability

On Thu, Jan 7, 2016 at 4:53 PM, Andres Freund wrote: > On 2016-01-07 16:48:53 +0530, Amit Kapila wrote: > > I think it's a worthwhile approach to pursue. But until it actually > fixes the problem of leaving around uninitialized pages I don't think > it's very meaningful to do performance comparis

Re: [HACKERS] Relation extension scalability

On 2016-01-07 16:48:53 +0530, Amit Kapila wrote: > What I could understand from above e-mail is that Dilip has tried to > extend relation multiple-pages-at-a-time and observed that it gives > comparable or better performance as compare to Andres's idea of > lock-free extension and it doesn't regres

Re: [HACKERS] Relation extension scalability

On Thu, Dec 31, 2015 at 6:22 AM, Dilip Kumar wrote: > On Fri, Dec 18, 2015 at 10:51 AM, Dilip Kumar > 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 succeede

Re: [HACKERS] Relation extension scalability

On Fri, Dec 18, 2015 at 10:51 AM, Dilip Kumar 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

Re: [HACKERS] Relation extension scalability

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 backen

Re: [HACKERS] Relation extension scalability

On 2015-07-19 11:56:47 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2015-07-19 11:28:25 -0400, Tom Lane wrote: > >> At this point session 1 will go and create page 44, won't it, and you > >> just wasted a page. > > > My local code now recognizes that case and uses the page. We just need

Re: [HACKERS] Relation extension scalability

Andres Freund writes: > On 2015-07-19 11:28:25 -0400, Tom Lane wrote: >> At this point session 1 will go and create page 44, won't it, and you >> just wasted a page. > My local code now recognizes that case and uses the page. We just need > to do an PageIsNew(). Er, what? How can you tell wheth

  1   2   >