Re: Lock contention high - Mailing list pgsql-performance

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.

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