Re: SUBTRANS: Minimizing calls to SubTransSetParent() - Mailing list pgsql-hackers
From | Julien Tachoires |
---|---|
Subject | Re: SUBTRANS: Minimizing calls to SubTransSetParent() |
Date | |
Msg-id | CAFEQCbFTrRX_hHwXaPwehmD2mY9CB4VApP6m5geJHQwz1phnEA@mail.gmail.com Whole thread Raw |
In response to | Re: SUBTRANS: Minimizing calls to SubTransSetParent() (Simon Riggs <simon.riggs@enterprisedb.com>) |
Responses |
Re: SUBTRANS: Minimizing calls to SubTransSetParent()
|
List | pgsql-hackers |
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
Attachment
pgsql-hackers by date: