Re: SUBTRANS: Minimizing calls to SubTransSetParent()

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.

Regards,

--
Julien Tachoires
EDB