Re: SUBTRANS: Minimizing calls to SubTransSetParent()

On Fri, Oct 28, 2022 at 10:55 PM Julien Tachoires <julmon(at)gmail(dot)com> wrote:
>
> Hi,
>
> Le lun. 26 sept. 2022 à 15:57, Simon Riggs
> <simon(dot)riggs(at)enterprisedb(dot)com> a écrit :
> >
> > On Fri, 16 Sept 2022 at 13:20, Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)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