Re: SUBTRANS: Minimizing calls to SubTransSetParent()

On Tue, 1 Nov 2022 at 08:55, Julien Tachoires <julmon(at)gmail(dot)com> wrote:
>
> > > 4. Test results with transaction 1
> > >
> > > TPS vs number of sub-transaction
> > >
> > > nsubx HEAD patched
> > > --------------------
> > > 0 441109 439474
> > > 8 439045 438103
> > > 16 439123 436993
> > > 24 436269 434194
> > > 32 439707 437429
> > > 40 439997 437220
> > > 48 439388 437422
> > > 56 439409 437210
> > > 64 439748 437366
> > > 72 92869 434448
> > > 80 66577 434100
> > > 88 61243 434255
> > > 96 57016 434419
> > > 104 52132 434917
> > > 112 49181 433755
> > > 120 46581 434044
> > > 128 44067 434268
> > >
> > > Perf profiling on HEAD with 80 sub-transactions:
> > > Overhead Symbol
> > > 51.26% [.] LWLockAttemptLock
> > > 24.59% [.] LWLockRelease
> > > 0.36% [.] base_yyparse
> > > 0.35% [.] PinBuffer
> > > 0.34% [.] AllocSetAlloc
> > > 0.33% [.] hash_search_with_hash_value
> > > 0.22% [.] LWLockAcquire
> > > 0.20% [.] UnpinBuffer
> > > 0.15% [.] SimpleLruReadPage_ReadOnly
> > > 0.15% [.] _bt_compare
> > >
> > > Perf profiling on patched with 80 sub-transactions:
> > > Overhead Symbol
> > > 2.64% [.] AllocSetAlloc
> > > 2.09% [.] base_yyparse
> > > 1.76% [.] hash_search_with_hash_value
> > > 1.62% [.] LWLockAttemptLock
> > > 1.26% [.] MemoryContextAllocZeroAligned
> > > 0.93% [.] _bt_compare
> > > 0.92% [.] expression_tree_walker_impl.part.4
> > > 0.84% [.] SearchCatCache1
> > > 0.79% [.] palloc
> > > 0.64% [.] core_yylex
> > >
> > > 5. Test results with transaction 2
> > >
> > > nsubx HEAD patched
> > > --------------------
> > > 0 440145 443816
> > > 8 438867 443081
> > > 16 438634 441786
> > > 24 436406 440187
> > > 32 439203 442447
> > > 40 439819 443574
> > > 48 439314 442941
> > > 56 439801 443736
> > > 64 439074 441970
> > > 72 439833 444132
> > > 80 148737 439941
> > > 88 413714 443343
> > > 96 251098 442021
> > > 104 70190 443488
> > > 112 405507 438866
> > > 120 177827 443202
> > > 128 399431 441842
> > >
> > > From the performance point of view, this patch clearly fixes the
> > > dramatic TPS collapse shown in these tests.
> >
> > I think these are really promising results. Although the perf result
> > shows that the bottleneck on the SLRU is no more there with the patch,
> > I think it would be nice to see the wait event as well.
>
> Please find attached samples returned by the following query when
> testing transaction 1 with 80 subxacts:
> SELECT wait_event_type, wait_event, locktype, mode, database,
> relation, COUNT(*) from pg_stat_activity AS psa JOIN pg_locks AS pl ON
> (psa.pid = pl.pid) GROUP BY 1, 2, 3, 4, 5, 6 ORDER BY 7 DESC;

These results are compelling, thank you.

Setting this to Ready for Committer.

--
Simon Riggs http://www.EnterpriseDB.com/