Re: Lock contention high

[arjun shetty <arjunshetty955@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxx> wrote:

Hi,

On October 27, 2021 2:44:56 PM PDT, Ashkil Dighin <ashkildighin76@xxxxxxxxx> 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.