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

Hi,

Le mar. 1 nov. 2022 à 09:39, Dilip Kumar <dilipbalaut@gmail.com> a écrit :
>
> On Fri, Oct 28, 2022 at 10:55 PM Julien Tachoires <julmon@gmail.com> wrote:
> >
> > Hi,
> >
> > Le lun. 26 sept. 2022 à 15:57, Simon Riggs
> > <simon.riggs@enterprisedb.com> a écrit :
> > >
> > > On Fri, 16 Sept 2022 at 13:20, Simon Riggs <simon.riggs@enterprisedb.com> wrote:
> > > >
> > > > Thanks for the review.
> > > >
> > > > v10 attached
> > >
> > > v11 attached, corrected for recent commit
> > > 14ff44f80c09718d43d853363941457f5468cc03.
> >
> > Please find below the performance tests results I have produced for this patch.
> > Attaching some charts and the scripts used to reproduce these tests.
> >
> > 1. Assumption
> >
> > The number of sub-transaction issued by only one long running
> > transaction may affect global TPS throughput if the number of
> > sub-transaction exceeds 64 (sub-overflow)
> >
> > 2. Testing scenario
> >
> > Based on pgbench, 2 different types of DB activity are applied concurrently:
> > - 1 long running transaction, including N sub-transactions
> > - X pgbench clients running read-only workload
> >
> > Tests are executed with a varying number of sub-transactions: from 0 to 128
> > Key metric is the TPS rate reported by pgbench runs in read-only mode
> >
> > Tests are executed against
> > - HEAD (14a737)
> > - HEAD (14a737) + 002_minimize_calls_to_SubTransSetParent.v11.patch
> >
> > 3. Long transaction anatomy
> >
> > Two different long transactions are tested because they don't have the
> > exact same impact on performance.
> >
> > Transaction number 1 includes one UPDATE affecting each row of
> > pgbench_accounts, plus an additional UPDATE affecting only one row but
> > executed in its own rollbacked sub-transaction:
> > BEGIN;
> > SAVEPOINT s1;
> > SAVEPOINT s2;
> > -- ...
> > SAVEPOINT sN - 1;
> > UPDATE pgbench_accounts SET abalance = abalance + 1  WHERE aid > 0;
> > SAVEPOINT sN;
> > UPDATE pgbench_accounts SET abalance = abalance + 1 WHERE aid = 12345;
> > ROLLBACK TO SAVEPOINT sN;
> > -- sleeping until the end of the test
> > ROLLBACK;
> >
> > Transaction 2 includes one UPDATE affecting each row of pgbench_accounts:
> > BEGIN;
> > SAVEPOINT s1;
> > SAVEPOINT s2;
> > -- ...
> > SAVEPOINT sN;
> > UPDATE pgbench_accounts SET abalance = abalance + 1 WHERE aid > 0;
> > -- sleeping until the end of the test
> > ROLLBACK;
> >
> > 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;

Regards,


--
Julien Tachoires
EDB