Re: Lock contention high
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
Re: Lock contention high
On Mon, Oct 25, 2021, 5:36 PM Andres Freund wrote: 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. How much larger might you go? Any write ups on lock contention as it relates to shared buffers? How impactful might huge pages (off, transparent or on) be to the use of shared buffers and the related locking mechanism?
Re: Lock contention high
Hi, On 2021-10-25 18:38:40 -0600, Michael Lewis wrote: > On Mon, Oct 25, 2021, 5:36 PM Andres Freund wrote: > 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. > How much larger might you go? I've seen s_b in the ~700GB range being a considerable speedup over lower values quite a few years ago. I don't see a clear cut upper boundary. The one thing this can regress measurably is the speed of dropping / truncating tables. > Any write ups on lock contention as it relates to shared buffers? I don't have a concrete thing to point you to, but searching for NUM_BUFFER_PARTITIONS might point you to some discussions. > How impactful might huge pages (off, transparent or on) be to the use of > shared buffers and the related locking mechanism? Using huge pages can *hugely* help performance-wise. Not directly by relieving postgres-side contention however (it does reduce cache usage somewhat, but it's mainly really just the frequency of TLB misses that makes the difference). Greetings, Andres Freund
