Re: Speed up transaction completion faster after many relations are accessed in a transaction

On 2019-02-20 07:20, Tsunakawa, Takayuki wrote: > From: Tom Lane [mailto:t...@sss.pgh.pa.us] >> Hm. Putting a list header for a purely-local data structure into shared >> memory seems quite ugly. Isn't there a better place to keep that? > > Agreed. I put it in the global variable. I think

RE: Speed up transaction completion faster after many relations are accessed in a transaction

From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Hm. Putting a list header for a purely-local data structure into shared > memory seems quite ugly. Isn't there a better place to keep that? Agreed. I put it in the global variable. > Do we really want a dlist here at all? I'm concerned that

RE: Speed up transaction completion faster after many relations are accessed in a transaction

From: Tomas Vondra [mailto:tomas.von...@2ndquadrant.com] > On 2/12/19 7:33 AM, Tsunakawa, Takayuki wrote: > > Imai-san confirmed performance improvement with this patch: > > > > https://commitfest.postgresql.org/22/1993/ > > > > Can you quantify the effects? That is, how much slower/faster does

Re: Speed up transaction completion faster after many relations are accessed in a transaction

On 2/12/19 7:33 AM, Tsunakawa, Takayuki wrote: > ... > > This problem was uncovered while evaluating partitioning performance. > When the application PREPAREs a statement once and then > EXECUTE-COMMIT repeatedly, the server creates a generic plan on the > 6th EXECUTE. Unfortunately, creation

Re: Speed up transaction completion faster after many relations are accessed in a transaction

On Tue, 19 Feb 2019 at 00:20, Andres Freund wrote: > On 2019-02-18 19:13:31 -0500, Tom Lane wrote: > > Andres Freund writes: > > > On 2019-02-18 19:01:06 -0500, Tom Lane wrote: > > >> Mmm ... AIUI, the patches currently proposed can only help for what > > >> David called "point lookup"

Re: Speed up transaction completion faster after many relations are accessed in a transaction

Hi, On 2019-02-18 20:29:29 -0500, Tom Lane wrote: > Andres Freund writes: > > but it's smaller (althoug there's plenty trailing space). > > I think you're supposing that these things are independently palloc'd, but > they aren't --- dynahash lays them out in arrays without palloc padding. I

Re: Speed up transaction completion faster after many relations are accessed in a transaction

Andres Freund writes: > On 2019-02-18 19:24:54 -0500, Tom Lane wrote: >> Yeah, but if we want to rearrange the members into an illogical order >> to save some space, we should do that independently of this patch --- > Sure, we should do that. I don't buy the "illogical" bit, just moving >

Re: Speed up transaction completion faster after many relations are accessed in a transaction

Hi, On 2019-02-18 19:24:54 -0500, Tom Lane wrote: > Yeah, but if we want to rearrange the members into an illogical order > to save some space, we should do that independently of this patch --- Sure, we should do that. I don't buy the "illogical" bit, just moving hashcode up to after tag isn't

Re: Speed up transaction completion faster after many relations are accessed in a transaction

Andres Freund writes: > On 2019-02-18 18:42:32 -0500, Tom Lane wrote: >> Do we really want a dlist here at all? I'm concerned that bloating >> LOCALLOCK will cost us when there are many locks involved. This patch >> increases the size of LOCALLOCK by 25% if I counted right, which does >> not

Re: Speed up transaction completion faster after many relations are accessed in a transaction

Hi, On 2019-02-18 19:13:31 -0500, Tom Lane wrote: > Andres Freund writes: > > On 2019-02-18 19:01:06 -0500, Tom Lane wrote: > >> Mmm ... AIUI, the patches currently proposed can only help for what > >> David called "point lookup" queries. There are still going to be > >> queries that scan a

Re: Speed up transaction completion faster after many relations are accessed in a transaction

Hi, On 2019-02-18 18:42:32 -0500, Tom Lane wrote: > "Tsunakawa, Takayuki" writes: > > The attached patch speeds up transaction completion when any prior > > transaction accessed many relations in the same session. > > Hm. Putting a list header for a purely-local data structure into shared >

Re: Speed up transaction completion faster after many relations are accessed in a transaction

Andres Freund writes: > On 2019-02-18 19:01:06 -0500, Tom Lane wrote: >> Mmm ... AIUI, the patches currently proposed can only help for what >> David called "point lookup" queries. There are still going to be >> queries that scan a large proportion of a partition tree, so if you've >> got tons

Re: Speed up transaction completion faster after many relations are accessed in a transaction

Hi, On 2019-02-18 19:01:06 -0500, Tom Lane wrote: > Andres Freund writes: > > Isn't a large portion of benefits in this patch going to be mooted by > > the locking improvements discussed in the other threads? I.e. there's > > hopefully not going to be a ton of cases with low overhead where we >

Re: Speed up transaction completion faster after many relations are accessed in a transaction

On Tue, 19 Feb 2019 at 12:56, Andres Freund wrote: > Isn't a large portion of benefits in this patch going to be mooted by > the locking improvements discussed in the other threads? I.e. there's > hopefully not going to be a ton of cases with low overhead where we > acquire a lot of locks and

Re: Speed up transaction completion faster after many relations are accessed in a transaction

David Rowley writes: > On Tue, 19 Feb 2019 at 12:42, Tom Lane wrote: >> My own thought about how to improve this situation was just to destroy >> and recreate LockMethodLocalHash at transaction end (or start) >> if its size exceeded $some-value. Leaving it permanently bloated seems >> like

Re: Speed up transaction completion faster after many relations are accessed in a transaction

Hi, On 2019-02-19 12:52:08 +1300, David Rowley wrote: > On Tue, 19 Feb 2019 at 12:42, Tom Lane wrote: > > My own thought about how to improve this situation was just to destroy > > and recreate LockMethodLocalHash at transaction end (or start) > > if its size exceeded $some-value. Leaving it

Re: Speed up transaction completion faster after many relations are accessed in a transaction

On Tue, 19 Feb 2019 at 12:42, Tom Lane wrote: > My own thought about how to improve this situation was just to destroy > and recreate LockMethodLocalHash at transaction end (or start) > if its size exceeded $some-value. Leaving it permanently bloated seems > like possibly a bad idea, even if we

Re: Speed up transaction completion faster after many relations are accessed in a transaction

"Tsunakawa, Takayuki" writes: > The attached patch speeds up transaction completion when any prior > transaction accessed many relations in the same session. Hm. Putting a list header for a purely-local data structure into shared memory seems quite ugly. Isn't there a better place to keep

Speed up transaction completion faster after many relations are accessed in a transaction

Hello, The attached patch speeds up transaction completion when any prior transaction accessed many relations in the same session. The transaction records its own acquired lock information in the LOCALLOCK structure (a pair of lock object and lock mode). It stores LOCALLOCKs in a hash table

<    1   2