Re: SUBTRANS: Minimizing calls to SubTransSetParent() - Mailing list pgsql-hackers

On Tue, 1 Nov 2022 at 08:55, Julien Tachoires <julmon@gmail.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/