Re: SUBTRANS: Minimizing calls to SubTransSetParent()

Hi,

Le mar. 1 nov. 2022 à 09:39, Dilip Kumar <dilipbalaut(at)gmail(dot)com> a écrit :
>
> 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.

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