Re: SUBTRANS: Minimizing calls to SubTransSetParent()

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.

Regards,

--
Julien Tachoires
EDB