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

2019-09-02 Thread Tsunakawa, Takayuki
From: Alvaro Herrera [mailto:alvhe...@2ndquadrant.com] > Hmm ... is this patch rejected, or is somebody still trying to get it to > committable state? David, you're listed as committer. I don't think it's rejected. It would be a pity (mottainai) to refuse this, because it provides significant

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

2019-09-02 Thread Alvaro Herrera
On 2019-Aug-14, David Rowley wrote: > For now, I'm out of ideas. If anyone else feels like suggesting > something of picking this up, feel free. Hmm ... is this patch rejected, or is somebody still trying to get it to committable state? David, you're listed as committer. -- Álvaro Herrera

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

2019-08-15 Thread Tomas Vondra
On Wed, Aug 14, 2019 at 07:25:10PM +1200, David Rowley wrote: On Thu, 25 Jul 2019 at 05:49, Tom Lane wrote: On the whole, I don't especially like this approach, because of the confusion between peak lock count and end-of-xact lock count. That seems way too likely to cause problems. Thanks

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

2019-08-14 Thread David Rowley
On Thu, 25 Jul 2019 at 05:49, Tom Lane wrote: > On the whole, I don't especially like this approach, because of the > confusion between peak lock count and end-of-xact lock count. That > seems way too likely to cause problems. Thanks for having a look at this. I've not addressed the points

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

2019-08-01 Thread Thomas Munro
On Thu, Jul 25, 2019 at 5:49 AM Tom Lane wrote: > David Rowley writes: > > Here's a more polished version with the debug code removed, complete > > with benchmarks. > > A few gripes: > > [gripes] Based on the above, I've set this to "Waiting on Author", in the next CF. -- Thomas Munro

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

2019-07-24 Thread Tom Lane
David Rowley writes: > Here's a more polished version with the debug code removed, complete > with benchmarks. A few gripes: You're measuring the number of locks held at completion of the transaction, which fails to account for locks transiently taken and released, so that the actual peak usage

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

2019-07-24 Thread David Rowley
On Wed, 24 Jul 2019 at 16:16, David Rowley wrote: > > On Wed, 24 Jul 2019 at 15:05, David Rowley > wrote: > > To be able to reduce the threshold down again we'd need to make a > > hash_get_num_entries(LockMethodLocalHash) call before performing the > > guts of LockReleaseAll(). We could then

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

2019-07-24 Thread Andres Freund
Hi, On 2019-07-21 21:37:28 +1200, David Rowley wrote: > select.sql: > \set p 1 > select * from ht where a = :p > > Master: > > $ pgbench -n -f select.sql -T 60 -M prepared postgres > tps = 10172.035036 (excluding connections establishing) > tps = 10192.780529 (excluding connections

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

2019-07-23 Thread David Rowley
On Wed, 24 Jul 2019 at 15:05, David Rowley wrote: > To be able to reduce the threshold down again we'd need to make a > hash_get_num_entries(LockMethodLocalHash) call before performing the > guts of LockReleaseAll(). We could then weight that onto some running > average counter with a weight of,

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

2019-07-23 Thread David Rowley
Thanks for having a look at this. On Wed, 24 Jul 2019 at 04:13, Tom Lane wrote: > > David Rowley writes: > > I'm pretty happy with v7 now. If anyone has any objections to it, > > please speak up very soon. Otherwise, I plan to push it about this > > time tomorrow. > > I dunno, this seems close

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

2019-07-23 Thread Tom Lane
David Rowley writes: > I've attached v7, which really is v6 with some comments adjusted and > the order of the hash_get_num_entries and hash_get_max_bucket function > calls swapped. I think hash_get_num_entries() will return 0 most of > the time where we're calling it, so it makes sense to put

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

2019-07-23 Thread David Rowley
On Tue, 23 Jul 2019 at 15:47, Tsunakawa, Takayuki wrote: > OTOH, how about my original patch that is based on the local lock list? I > expect that it won't that significant slowdown in the same test case. If > it's not satisfactory, then v6 is the best to commit. I think we need to move

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

2019-07-22 Thread Tsunakawa, Takayuki
From: David Rowley [mailto:david.row...@2ndquadrant.com] > Another counter-argument to this is that there's already an > unexplainable slowdown after you run a query which obtains a large > number of locks in a session or use prepared statements and a > partitioned table with the default

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

2019-07-22 Thread David Rowley
On Mon, 22 Jul 2019 at 12:48, Tsunakawa, Takayuki wrote: > > From: David Rowley [mailto:david.row...@2ndquadrant.com] > > I went back to the drawing board on this and I've added some code that > > counts > > the number of times we've seen the table to be oversized and just shrinks > > the table

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

2019-07-21 Thread David Rowley
On Mon, 22 Jul 2019 at 16:36, Tsunakawa, Takayuki wrote: > > From: David Rowley [mailto:david.row...@2ndquadrant.com] > > For the use case we've been measuring with partitioned tables and the > > generic plan generation causing a sudden spike in the number of > > obtained locks, then having

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

2019-07-21 Thread Tsunakawa, Takayuki
From: David Rowley [mailto:david.row...@2ndquadrant.com] > For the use case we've been measuring with partitioned tables and the > generic plan generation causing a sudden spike in the number of > obtained locks, then having plan_cache_mode = force_custom_plan will > cause the lock table not to

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

2019-07-21 Thread David Rowley
On Mon, 22 Jul 2019 at 14:21, Tsunakawa, Takayuki wrote: > > From: David Rowley [mailto:david.row...@2ndquadrant.com] > > I personally don't think that's true. The only way you'll notice the > > LockReleaseAll() overhead is to execute very fast queries with a > > bloated lock table. It's pretty

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

2019-07-21 Thread Tsunakawa, Takayuki
From: David Rowley [mailto:david.row...@2ndquadrant.com] > I personally don't think that's true. The only way you'll notice the > LockReleaseAll() overhead is to execute very fast queries with a > bloated lock table. It's pretty hard to notice that a single 0.1ms > query is slow. You'll need to

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

2019-07-21 Thread David Rowley
On Mon, 22 Jul 2019 at 12:48, Tsunakawa, Takayuki wrote: > > From: David Rowley [mailto:david.row...@2ndquadrant.com] > > I went back to the drawing board on this and I've added some code that > > counts > > the number of times we've seen the table to be oversized and just shrinks > > the table

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

2019-07-21 Thread Tsunakawa, Takayuki
From: David Rowley [mailto:david.row...@2ndquadrant.com] > I went back to the drawing board on this and I've added some code that counts > the number of times we've seen the table to be oversized and just shrinks > the table back down on the 1000th time. 6.93% / 1000 is not all that much. I'm

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

2019-07-21 Thread David Rowley
On Thu, 18 Jul 2019 at 14:53, David Rowley wrote: > Is anyone particularly concerned about the worst-case slowdown here > being about 1.54%? The best case, and arguably a more realistic case > above showed a 34% speedup for the best case. I took a bit more time to test the performance on this.

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

2019-07-17 Thread David Rowley
On Thu, 27 Jun 2019 at 12:59, Tsunakawa, Takayuki wrote: > > From: David Rowley [mailto:david.row...@2ndquadrant.com] > Thank you, looks good. I find it ready for committer (I noticed the status > is already set so.) Thanks for looking. I've just been looking at this again and I thought I'd

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

2019-06-26 Thread Tsunakawa, Takayuki
From: David Rowley [mailto:david.row...@2ndquadrant.com] v5 is attached. Thank you, looks good. I find it ready for committer (I noticed the status is already set so.) Regards Takayuki Tsunakawa

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

2019-06-26 Thread David Rowley
On Mon, 17 Jun 2019 at 15:05, Tsunakawa, Takayuki wrote: > (1) > +#define LOCKMETHODLOCALHASH_SHRINK_SIZE 64 > > How about LOCKMETHODLOCALHASH_SHRINK_THRESHOLD, because this determines the > threshold value to trigger shrinkage? Code in PostgreSQL seems to use the > term threshold. That's

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

2019-06-16 Thread Tsunakawa, Takayuki
From: David Rowley [mailto:david.row...@2ndquadrant.com] > I've revised the patch to add a new constant named > LOCKMETHODLOCALHASH_SHRINK_SIZE. I've set this to 64 for now. Once the hash Thank you, and good performance. The patch passed make check. I'm OK with the current patch, but I have a

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

2019-06-14 Thread David Rowley
On Mon, 8 Apr 2019 at 04:09, Tom Lane wrote: > Also, I would not define "significantly bloated" as "the table has > grown at all". I think the threshold ought to be at least ~100 > buckets, if we're starting at 16. I've revised the patch to add a new constant named

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

2019-04-08 Thread Peter Eisentraut
On 2019-04-08 05:46, Tsunakawa, Takayuki wrote: > I'm registering you as another author and me as a reviewer, and marking this > ready for committer. Moved to next commit fest. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA,

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

2019-04-07 Thread Tsunakawa, Takayuki
From: David Rowley [mailto:david.row...@2ndquadrant.com] > It would be good to get your view on the > shrink_bloated_locallocktable_v3.patch I worked on last night. I was > unable to measure any overhead to solving the problem that way. Thanks, it looks super simple and good. I understood the

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

2019-04-07 Thread David Rowley
On Mon, 8 Apr 2019 at 14:54, Tsunakawa, Takayuki wrote: > > From: 'Andres Freund' [mailto:and...@anarazel.de] > > Did you see that people measured slowdowns? > > Yeah, 0.5% decrease with pgbench -M prepared -S (select-only), which feels > like a somewhat extreme test case. And that might be

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

2019-04-07 Thread Tsunakawa, Takayuki
From: 'Andres Freund' [mailto:and...@anarazel.de] > On 2019-04-08 02:28:12 +, Tsunakawa, Takayuki wrote: > > I think the linked list of LOCALLOCK approach is natural, simple, and > > good. > > Did you see that people measured slowdowns? Yeah, 0.5% decrease with pgbench -M prepared -S

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

2019-04-07 Thread 'Andres Freund'
Hi, On 2019-04-08 02:28:12 +, Tsunakawa, Takayuki wrote: > I think the linked list of LOCALLOCK approach is natural, simple, and > good. Did you see that people measured slowdowns? Greetings, Andres Freund

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

2019-04-07 Thread Tsunakawa, Takayuki
From: Tom Lane [mailto:t...@sss.pgh.pa.us] > On the whole I don't think there's an adequate case for committing > this patch. From: Andres Freund [mailto:and...@anarazel.de] > On 2019-04-05 23:03:11 -0400, Tom Lane wrote: > > If I reduce the number of partitions in Amit's example from 8192 > > to

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

2019-04-07 Thread David Rowley
On Mon, 8 Apr 2019 at 04:09, Tom Lane wrote: > Um ... I don't see where you're destroying the old hash? In CreateLocalLockHash. > Also, I entirely dislike wiring in assumptions about hash_seq_search's > private state structure here. I think it's worth having an explicit > entry point in

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

2019-04-07 Thread Tom Lane
David Rowley writes: > Okay. Here's another version with all the average locks code removed > that only recreates the table when it's completely empty. Um ... I don't see where you're destroying the old hash? Also, I entirely dislike wiring in assumptions about hash_seq_search's private state

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

2019-04-07 Thread David Rowley
On Mon, 8 Apr 2019 at 03:47, Andres Freund wrote: > Could you benchmark your adversarial case? Which case? I imagine the worst case for v2 is a query that just constantly asks for over 16 locks. Perhaps a prepared plan, so not to add planner overhead. -- David Rowley

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

2019-04-07 Thread Andres Freund
Hi, On 2019-04-08 03:40:52 +1200, David Rowley wrote: > On Mon, 8 Apr 2019 at 03:20, Tom Lane wrote: > > > > David Rowley writes: > > > The reason I thought it was a good idea to track some history there > > > was to stop the lock table constantly being shrunk back to the default > > > size

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

2019-04-07 Thread David Rowley
On Mon, 8 Apr 2019 at 03:20, Tom Lane wrote: > > David Rowley writes: > > The reason I thought it was a good idea to track some history there > > was to stop the lock table constantly being shrunk back to the default > > size every time a simple single table query was executed. > > I think

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

2019-04-07 Thread Tom Lane
David Rowley writes: > On Mon, 8 Apr 2019 at 02:59, Tom Lane wrote: >> We *should* be using hash_get_num_entries(), but only to verify >> that the table is empty before resetting it. The additional bit >> that is needed is to see whether the number of buckets is large >> enough to justify

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

2019-04-07 Thread David Rowley
On Mon, 8 Apr 2019 at 02:59, Tom Lane wrote: > > David Rowley writes: > > hash_get_num_entries() looks cheap enough to me. Can you explain why > > you think that's too expensive? > > What I objected to cost-wise was counting the number of lock > acquisitions/releases, which seems entirely beside

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

2019-04-07 Thread Tom Lane
David Rowley writes: > On Mon, 8 Apr 2019 at 02:20, Tom Lane wrote: >> I like the concept ... but the particular implementation, not so much. >> It seems way overcomplicated. In the first place, why should we >> add code to copy entries? Just don't do it except when the table >> is empty. In

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

2019-04-07 Thread David Rowley
On Mon, 8 Apr 2019 at 02:36, David Rowley wrote: > > LockMethodLocalHash is special in that it predictably goes to empty > > at the end of every transaction, so that de-bloating at that point > > is a workable strategy. I think we'd probably need something more > > robust if we were trying to

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

2019-04-07 Thread David Rowley
On Mon, 8 Apr 2019 at 02:20, Tom Lane wrote: > I like the concept ... but the particular implementation, not so much. > It seems way overcomplicated. In the first place, why should we > add code to copy entries? Just don't do it except when the table > is empty. In the second, I think we could

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

2019-04-07 Thread Tom Lane
David Rowley writes: > On Sat, 6 Apr 2019 at 16:03, 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

2019-04-07 Thread David Rowley
On Sat, 6 Apr 2019 at 16:03, Tom Lane wrote: > I'd also point out that this is hardly the only place where we've > seen hash_seq_search on nearly-empty hash tables become a bottleneck. > So I'm not thrilled about attacking that with one-table-at-time patches. > I'd rather see us do something to

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

2019-04-07 Thread Peter Eisentraut
On 2019-04-06 05:03, Tom Lane wrote: > Trying a standard pgbench test case (pgbench -M prepared -S with > one client and an -s 10 database), it seems that the patch is about > 0.5% slower than HEAD. Again, that's below the noise threshold, > but it's not promising for the net effects of this

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

2019-04-05 Thread Tom Lane
Andres Freund writes: > I wonder if one approach to solve this wouldn't be to just make the > hashtable drastically smaller. Right now we'll often have have lots > empty entries that are 72 bytes + dynahash overhead. That's plenty of > memory that needs to be skipped over. I wonder if we

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

2019-04-05 Thread Andres Freund
Hi, On 2019-04-05 23:03:11 -0400, Tom Lane wrote: > Peter Eisentraut writes: > > I can't detect any performance improvement with the patch applied to > > current master, using the test case from Yoshikazu Imai (2019-03-19). > > FWIW, I tried this patch against current HEAD (959d00e9d). > Using

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

2019-04-05 Thread Tom Lane
Peter Eisentraut writes: > I can't detect any performance improvement with the patch applied to > current master, using the test case from Yoshikazu Imai (2019-03-19). FWIW, I tried this patch against current HEAD (959d00e9d). Using the test case described by Amit at I do measure an undeniable

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

2019-04-05 Thread Peter Eisentraut
On 2019-03-19 10:21, Tsunakawa, Takayuki wrote: > From: Tsunakawa, Takayuki [mailto:tsunakawa.ta...@jp.fujitsu.com] >> Fixed. > > Rebased on HEAD. Do you need the dlist_foreach_modify() calls? You are not actually modifying the list structure. -- Peter Eisentraut

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

2019-04-04 Thread Imai, Yoshikazu
On Fri, Apr 5, 2019 at 0:05 AM, Tsunakawa, Takayuki wrote: > From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] > > I can't detect any performance improvement with the patch applied to > > current master, using the test case from Yoshikazu Imai (2019-03-19). > > That's strange...

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

2019-04-04 Thread Tsunakawa, Takayuki
Hi Amit-san, Imai-snan, From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > I was able to detect it as follows. > plan_cache_mode = auto > >HEAD: 1915 tps > Patched: 2394 tps > > plan_cache_mode = custom (non-problematic: generic plan is never created) > >HEAD: 2402 tps >

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

2019-04-04 Thread Imai, Yoshikazu
On Fri, Apr 5, 2019 at 1:31 AM, Amit Langote wrote: > On 2019/04/05 5:42, Peter Eisentraut wrote: > > On 2019-04-04 06:58, Amit Langote wrote: > >> Also, since the "speed up partition planning" patch went in > >> (428b260f8), it might be possible to see the performance boost even > >> with the

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

2019-04-04 Thread Amit Langote
On 2019/04/05 5:42, Peter Eisentraut wrote: > On 2019-04-04 06:58, Amit Langote wrote: >> Also, since the "speed up partition planning" patch went in (428b260f8), >> it might be possible to see the performance boost even with the >> partitioning example you cited upthread. > > I can't detect any

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

2019-04-04 Thread Tsunakawa, Takayuki
Hi Peter, Imai-san, From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] > I can't detect any performance improvement with the patch applied to > current master, using the test case from Yoshikazu Imai (2019-03-19). That's strange... Peter, Imai-san, can you compare your test

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

2019-04-04 Thread Peter Eisentraut
On 2019-04-04 06:58, Amit Langote wrote: > Also, since the "speed up partition planning" patch went in (428b260f8), > it might be possible to see the performance boost even with the > partitioning example you cited upthread. I can't detect any performance improvement with the patch applied to

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

2019-04-03 Thread Amit Langote
Hi, On 2019/04/04 13:37, Tsunakawa, Takayuki wrote: > Hi Peter, > > From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] >> I did a bit of performance testing, both a plain pgbench and the >> suggested test case with 4096 partitions. I can't detect any >> performance improvements.

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

2019-04-03 Thread Tsunakawa, Takayuki
Hi Peter, From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] > I did a bit of performance testing, both a plain pgbench and the > suggested test case with 4096 partitions. I can't detect any > performance improvements. In fact, within the noise, it tends to be > just a bit on the

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

2019-03-26 Thread Tsunakawa, Takayuki
From: David Rowley [mailto:david.row...@2ndquadrant.com] > Here a benchmark doing that using pgbench's script weight feature. Wow, I didn't know that pgbench has evolved to have such a convenient feature. Thanks for telling me how to utilize it in testing. PostgreSQL is cool! Regards

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

2019-03-26 Thread Tsunakawa, Takayuki
From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > My understanding of what David wrote is that the slowness of bloated hash > table is hard to notice, because planning itself is pretty slow. With the > "speeding up planning with partitions" patch, planning becomes quite fast, > so the

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

2019-03-26 Thread David Rowley
On Tue, 26 Mar 2019 at 21:55, David Rowley wrote: > > On Tue, 26 Mar 2019 at 21:23, Tsunakawa, Takayuki > wrote: > > Thank you David for explaining. Although I may not understand the effect > > of "speeding up planning with partitions" patch, this patch takes effect > > even without it.

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

2019-03-26 Thread David Rowley
On Tue, 26 Mar 2019 at 21:23, Tsunakawa, Takayuki wrote: > Thank you David for explaining. Although I may not understand the effect of > "speeding up planning with partitions" patch, this patch takes effect even > without it. That is, perform the following in the same session: > > 1. SELECT

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

2019-03-26 Thread Amit Langote
Tsunakawa-san, On 2019/03/26 17:21, Tsunakawa, Takayuki wrote: > From: David Rowley [mailto:david.row...@2ndquadrant.com] >> On Mon, 25 Mar 2019 at 23:44, Peter Eisentraut >> wrote: >>> Perhaps "speeding up planning with partitions" needs to be accepted first? >> >> Yeah, I think it likely will

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

2019-03-26 Thread Tsunakawa, Takayuki
From: David Rowley [mailto:david.row...@2ndquadrant.com] > On Mon, 25 Mar 2019 at 23:44, Peter Eisentraut > wrote: > > Perhaps "speeding up planning with partitions" needs to be accepted first? > > Yeah, I think it likely will require that patch to be able to measure > the gains from this patch.

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

2019-03-25 Thread David Rowley
On Mon, 25 Mar 2019 at 23:44, Peter Eisentraut wrote: > I did a bit of performance testing, both a plain pgbench and the > suggested test case with 4096 partitions. I can't detect any > performance improvements. In fact, within the noise, it tends to be > just a bit on the slower side. > > So

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

2019-03-25 Thread Peter Eisentraut
On 2019-03-19 16:38, Peter Eisentraut wrote: > On 2019-03-19 10:21, Tsunakawa, Takayuki wrote: >> From: Tsunakawa, Takayuki [mailto:tsunakawa.ta...@jp.fujitsu.com] >>> Fixed. >> >> Rebased on HEAD. > > I have committed the first patch that reorganizes the struct. I'll have > to spend some time

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

2019-03-19 Thread Peter Eisentraut
On 2019-03-19 10:21, Tsunakawa, Takayuki wrote: > From: Tsunakawa, Takayuki [mailto:tsunakawa.ta...@jp.fujitsu.com] >> Fixed. > > Rebased on HEAD. I have committed the first patch that reorganizes the struct. I'll have to spend some time evaluating the performance impact of the second patch,

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

2019-03-19 Thread Tsunakawa, Takayuki
From: Tsunakawa, Takayuki [mailto:tsunakawa.ta...@jp.fujitsu.com] > Fixed. Rebased on HEAD. Regards Takayuki Tsunakawa 0001-reorder-LOCALLOCK-structure-members-to-compact-the-s.patch Description: 0001-reorder-LOCALLOCK-structure-members-to-compact-the-s.patch

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

2019-03-19 Thread Imai, Yoshikazu
Hi Tsunakawa-san, Peter On Tue, Mar 19, 2019 at 7:53 AM, Tsunakawa, Takayuki wrote: > From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] > > You posted a link to some performance numbers, but I didn't see the > > test setup explained there. I'd like to get some more information on

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

2019-03-19 Thread Tsunakawa, Takayuki
Hi Peter, Imai-san, From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] > Your changes in LOCALLOCK still refer to PGPROC, from your first version > of the patch. > > I think the reordering of struct members could be done as a separate > preliminary patch. > > Some more

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

2019-03-18 Thread Peter Eisentraut
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

2019-02-19 Thread Tsunakawa, Takayuki
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

2019-02-19 Thread Tsunakawa, Takayuki
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

2019-02-19 Thread Tomas Vondra
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

2019-02-19 Thread Simon Riggs
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

2019-02-18 Thread Andres Freund
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

2019-02-18 Thread Tom Lane
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

2019-02-18 Thread Andres Freund
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

2019-02-18 Thread Tom Lane
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

2019-02-18 Thread Andres Freund
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

2019-02-18 Thread Andres Freund
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

2019-02-18 Thread Tom Lane
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

2019-02-18 Thread Andres Freund
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

2019-02-18 Thread David Rowley
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

2019-02-18 Thread Tom Lane
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 > acquire a lot of locks and release them very soon after.

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

2019-02-18 Thread Tom Lane
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

2019-02-18 Thread Andres Freund
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

2019-02-18 Thread David Rowley
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

2019-02-18 Thread Tom Lane
"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