Yes, lock contention reduced with postgresqlv14.
Lock acquire reduced 18% to 10%
10.49 %postgres postgres [.] LWLockAcquire
5.09% postgres postgres [.] _bt_compare
5.09% postgres postgres [.] _bt_compare
Is lock contention can be reduced to 0-3%?
On pg-stat-activity shown LwLock as “BufferCounter” and “WalInsert”
On Tuesday, October 26, 2021, Andres Freund <andres@xxxxxxxxxxx> wrote:
Hi,
On 2021-10-12 13:05:12 +0530, Ashkil Dighin wrote:
> PostgreSQL version: 13.3
You could try postgres 14 - that did improve scalability in some areas.
> Perf data for 24vu(TPC-C)
> --------------------------------
>
> 18.99% postgres postgres [.] LWLockAcquire
> 7.09% postgres postgres [.] _bt_compare
> 8.66% postgres postgres [.] LWLockRelease
> 2.28% postgres postgres [.] GetSnapshotData
> 2.25% postgres postgres [.] hash_search_with_hash_value
> 2.11% postgres postgres [.] XLogInsertRecord
> 1.98% postgres postgres [.] PinBuffer
To be more useful you'd need to create a profile with 'caller' information
using 'perf record --call-graph dwarf', and then check what the important
callers are.
> Postgres.conf used in Baremetal
> ========================
> shared_buffers = 128GB(1/4 th RAM size)
> effective_cachesize=392 GB(1/3 or 75% of RAM size)
If your hot data set is actually larger than s_b, I'd recommend trying a
larger s_b. It's plausible that a good chunk of lock contention is from that.
Greetings,
Andres Freund
