PostgreSQL utilizes lightweight locks(LWLocks) to synchronize and control access to the buffer content. A process acquires an LWLock in a shared mode to read from the buffer and an exclusive mode to write to the buffer. Therefore, while holding an exclusive lock, a process prevents other processes from acquiring a shared or exclusive lock. Also, a shared lock can be acquired concurrently by other processes. The issue starts when many processes acquire an exclusive lock on buffer content. As a result, LwlockAcquire seen as top hot function in profilng.
Here need to understand LwlockAcquire is lock contention or cpu time spent inside the method/ function(top function in profiling)
It can analysed log “LwStatus” with parameters like ex-acquire-count(exclusive mode) , sh-acquire-count , block-count and spin-delay-count
Total lock acquisition request = ex-acquire-count+sh-acquire-count)
Time lock contention %= block count)/ Total lock acquisition request.
Time lock contention may provide as most of cpu time inside the function rather than spinning/ waiting for lock.
I suspect lock contention and performance issues with __int128. And I would like to check the performance by forcibly disabling int128(Maxalign16bytes) and enable like long long(maxlign 8bytes).
Is it possible to disable int128 in PostgreSQL?
On Thursday, October 28, 2021, Andres Freund <andres@anarazel.de> wrote:
Hi,
On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin <ashkildighin76@gmail.com> wrote: >Hi, >Yes, lock contention reduced with postgresqlv14. >Lock acquire reduced 18% to 10% >10.49 %postgres postgres [.] LWLockAcquire >5.09% postgres postgres [.] _bt_compare > >Is lock contention can be reduced to 0-3%?
Probably not, or at least not easily. Because of the atomic instructions the locking also includes some other costs (e.g. cache misses, serializing store buffers,...).
There's a good bit we can do to increase the cache efficiency around buffer headers, but it won't get us quite that low I'd guess.
>On pg-stat-activity shown LwLock as “BufferCounter” and “WalInsert”
Without knowing what proportion they have to each and to non-waiting backends that unfortunately doesn't help that much..
Andres
-- Sent from my Android device with K-9 Mail. Please excuse my brevity.