1. How to check which NUMA node in PostgreSQL process fetching from the memory?
2. Is NUMA configuration is better for PostgreSQL? vm.zone_reclaim_mode= 0 numactl --interleave = all /init.d/ PostgreSQL start kernel.numa_balancing= 0 On Wednesday, November 17, 2021, arjun shetty <arjunshetty...@gmail.com> wrote: > 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. >>> >>