Re: Lock contention high

Ashkil,

Can you bind postgres in single NUMA node, for instance:
 $ taskset -pc 0-63 <POSTMASTER_PID>

Then run your benchmark, compare results in terms of benchmark metrics &
presence on LWLock(Acquire|Release) in perf top.

BR,
 Michael.

On 10/14/21 9:45 AM, Ashkil Dighin wrote:
>
> NUMA node0 CPU(s):   0-63,128-191NUMA node1 CPU(s):   64-127,192-255
> Thread(s) per core:  2
> Core(s) per socket:  64
> Socket(s):           2
> NUMA node(s):        2
> corepinning(ta perf lock contention results for 24,32 vu
> 0-63
>   24: 18.03%  postgres  postgres            [.] LWLockAcquire
>   32: 7.02%  postgres  postgres             [.] LWLockAcquire
> 64-127
>   24: 17.96%  postgres  postgres            [.] LWLockAcquire
>   32: 7.04%  postgres  postgres             [.] LWLockAcquire
> 0-63,128-191(Node0)
>   24: 18.4%  postgres  postgres            [.] LWLockAcquire
>   32: 7.07%  postgres  postgres            [.] LWLockAcquire
> 64-127,192-255(Node1)
>   24: 18.3%  postgres  postgres            [.] LWLockAcquire
>   32: 7.06%  postgres  postgres            [.] LWLockAcquire
>
> I do not understand on interconnect type and has restrictions on lscpu .
>
> On Tuesday, October 12, 2021, Mikhail Zhilin <zhilin(dot)ms(at)phystech(dot)edu
> <mailto:zhilin(dot)ms(at)phystech(dot)edu>> wrote:
>
> Hi,
>
> How many sockets are on motherboard?
> What is CPU model and interconnect type (UPI?)?
> Can you share output of "lscpu"?
>
> If you have more than 1 NUMA node it may be worth to run
> PostgreSQL in single NUMA node via taskset. It will eliminate
> access to remote memory and speed up processing.
>
> Thanks,
>  Michael.
>
> On 10/12/21 10:35 AM, Ashkil Dighin wrote:
>>
>> Hi,
>> Lock contention observed high in PostgreSQLv13.3
>> The source code compiled with GNC(GCCv11.x)
>> PostgreSQL version: 13.3
>> Operating system:   RHEL8.3
>> Kernel name:4.18.0-305.10.2.el8_4.x86_64
>> RAM Size:512GB
>> SSD: 1TB
>> The environment used IBM metal and test benchmark
>> environment HammerDbv4.2
>> Test case :TPC-C
>>
>> Perf data for 24vu(TPC-C)
>> --------------------------------
>>
>>       18.99%  postgres  postgres            [.] LWLockAcquire
>>      7.09%  postgres  postgres            [.] _bt_compare
>>      8.66%  postgres  postgres            [.] LWLockRelease
>>      2.28%  postgres  postgres            [.] GetSnapshotData
>>      2.25%  postgres  postgres            [.]
>> hash_search_with_hash_value
>>      2.11%  postgres  postgres            [.] XLogInsertRecord
>>      1.98%  postgres  postgres            [.] PinBuffer
>>
>> 1.Is there a way to tune the lock contention ?
>> 2.Is any recommendations to tune/reduce the lock contention via
>> postgres.conf
>>
>> Postgres.conf used  in Baremetal
>> ========================
>> shared_buffers = 128GB(1/4 th RAM size)
>> effective_cachesize=392 GB(1/3 or 75% of RAM size)
>> huge_pages = on
>> temp_buffers = 4000MB
>> work_mem = 4000MB
>> maintenance_work_mem = 512MB
>> autovacuum_work_mem = -1
>> max_stack_depth = 7MB
>> dynamic_shared_memory_type = posix
>> max_files_per_process = 4000
>> effective_io_concurrency = 32
>> wal_level = minimal
>> synchronous_commit = off
>> wal_buffers = 512MB
>> checkpoint_timeout = 1h
>> checkpoint_completion_target = 1
>> checkpoint_warning = 0
>> log_min_messages = error
>> log_min_error_statement = error
>> log_timezone = 'GB'
>> autovacuum = off
>> datestyle = 'iso, dmy'
>> timezone = 'GB'
>> lc_messages = 'en_GB.UTF-8'
>> lc_monetary = 'en_GB.UTF-8'
>> lc_numeric = 'en_GB.UTF-8'
>> lc_time = 'en_GB.UTF-8'
>> default_text_search_config = 'pg_catalog.english'
>> max_locks_per_transaction = 64
>> max_pred_locks_per_transaction = 64
>>
>> Best Regards
>> Anil
>>
>