Re: WAL Insertion Lock Improvements

On Tue, Jan 24, 2023 at 7:00 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> I'm attaching the v3 patch with the above review comments addressed.
> Hopefully, no memory ordering issues now. FWIW, I've added it to CF
> https://commitfest.postgresql.org/42/4141/.
>
> Test results with the v3 patch and insert workload are the same as
> that of the earlier run - TPS starts to scale at higher clients as
> expected after 512 clients and peaks at 2X with 2048 and 4096 clients.
>
> HEAD:
> 1 1380.411086
> 2 1358.378988
> 4 2701.974332
> 8 5925.380744
> 16 10956.501237
> 32 20877.513953
> 64 40838.046774
> 128 70251.744161
> 256 108114.321299
> 512 120478.988268
> 768 99140.425209
> 1024 93645.984364
> 2048 70111.159909
> 4096 55541.804826
>
> v3 PATCHED:
> 1 1493.800209
> 2 1569.414953
> 4 3154.186605
> 8 5965.578904
> 16 11912.587645
> 32 22720.964908
> 64 42001.094528
> 128 78361.158983
> 256 110457.926232
> 512 148941.378393
> 768 167256.590308
> 1024 155510.675372
> 2048 147499.376882
> 4096 119375.457779

I slightly modified the comments and attached the v4 patch for further
review. I also took perf report - there's a clear reduction in the
functions that are affected by the patch - LWLockWaitListLock,
WaitXLogInsertionsToFinish, LWLockWaitForVar and
LWLockConflictsWithVar. Note that I compiled the source code with
-ggdb for capturing symbols for perf, still the benefit stands at > 2X
for a higher number of clients.

HEAD:
+ 16.87% 0.01% postgres [.] CommitTransactionCommand
+ 16.86% 0.00% postgres [.] finish_xact_command
+ 16.81% 0.01% postgres [.] CommitTransaction
+ 15.09% 0.20% postgres [.] LWLockWaitListLock
+ 14.53% 0.01% postgres [.] WaitXLogInsertionsToFinish
+ 14.51% 0.02% postgres [.] LWLockWaitForVar
+ 11.70% 11.63% postgres [.] pg_atomic_read_u32_impl
+ 11.66% 0.08% postgres [.] pg_atomic_read_u32
+ 9.96% 0.03% postgres [.] LWLockConflictsWithVar
+ 4.78% 0.00% postgres [.] LWLockQueueSelf
+ 1.91% 0.01% postgres [.] pg_atomic_fetch_or_u32
+ 1.91% 1.89% postgres [.] pg_atomic_fetch_or_u32_impl
+ 1.73% 0.00% postgres [.] XLogInsert
+ 1.69% 0.01% postgres [.] XLogInsertRecord
+ 1.41% 0.01% postgres [.] LWLockRelease
+ 1.37% 0.47% postgres [.] perform_spin_delay
+ 1.11% 1.11% postgres [.] spin_delay
+ 1.10% 0.03% postgres [.] exec_bind_message
+ 0.91% 0.00% postgres [.] WALInsertLockRelease
+ 0.91% 0.00% postgres [.] LWLockReleaseClearVar
+ 0.72% 0.02% postgres [.] LWLockAcquire
+ 0.60% 0.00% postgres [.] LWLockDequeueSelf
+ 0.58% 0.00% postgres [.] GetTransactionSnapshot
0.58% 0.49% postgres [.] GetSnapshotData
+ 0.58% 0.00% postgres [.] WALInsertLockAcquire
+ 0.55% 0.00% postgres [.] XactLogCommitRecord

TPS (compiled with -ggdb for capturing symbols for perf)
1 1392.512967
2 1435.899119
4 3104.091923
8 6159.305522
16 11477.641780
32 22701.000718
64 41662.425880
128 23743.426209
256 89837.651619
512 65164.221500
768 66015.733370
1024 56421.223080
2048 52909.018072
4096 40071.146985

PATCHED:
+ 2.19% 0.05% postgres [.] LWLockWaitListLock
+ 2.10% 0.01% postgres [.] LWLockQueueSelf
+ 1.73% 1.71% postgres [.] pg_atomic_read_u32_impl
+ 1.73% 0.02% postgres [.] pg_atomic_read_u32
+ 1.72% 0.02% postgres [.] LWLockRelease
+ 1.65% 0.04% postgres [.] exec_bind_message
+ 1.43% 0.00% postgres [.] XLogInsert
+ 1.42% 0.01% postgres [.] WaitXLogInsertionsToFinish
+ 1.40% 0.03% postgres [.] LWLockWaitForVar
+ 1.38% 0.02% postgres [.] XLogInsertRecord
+ 0.93% 0.03% postgres [.] LWLockAcquireOrWait
+ 0.91% 0.00% postgres [.] GetTransactionSnapshot
+ 0.91% 0.79% postgres [.] GetSnapshotData
+ 0.91% 0.00% postgres [.] WALInsertLockRelease
+ 0.91% 0.00% postgres [.] LWLockReleaseClearVar
+ 0.53% 0.02% postgres [.] ExecInitModifyTable

TPS (compiled with -ggdb for capturing symbols for perf)
1 1295.296611
2 1459.079162
4 2865.688987
8 5533.724983
16 10771.697842
32 20557.499312
64 39436.423783
128 42555.639048
256 73139.060227
512 124649.665196
768 131162.826976
1024 132185.160007
2048 117377.586644
4096 88240.336940

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com