Re: Lock contention high

[Mikhail Zhilin <zhilin.ms@xxxxxxxxxxxx>]

    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.ms@xxxxxxxxxxxx> 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