Hi Askhil 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. On Friday, November 12, 2021, Ashkil Dighin <ashkildighi...@gmail.com> wrote: > Hi > 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 <and...@anarazel.de> wrote: > >> Hi, >> >> On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin < >> ashkildighi...@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. >> >
