Re: Lock contention high

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> 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
>
>
>