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

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.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com