Re: Lock contention high

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 <arjunshetty955(at)gmail(dot)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 <ashkildighin76(at)gmail(dot)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 <andres(at)anarazel(dot)de> wrote:
>>
>>> Hi,
>>>
>>> On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin <
>>> ashkildighin76(at)gmail(dot)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.
>>>
>>