pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Mikhail Zhilin <zhilin.ms@phystech.edu>
To: Ashkil Dighin <ashkildighin76@gmail.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Lock contention high
Date: Tue, 12 Oct 2021 13:29:33 +0300
Message-ID: <4da46846-5835-8da7-b3c0-29c7b08e8b09@phystech.edu> (raw)
In-Reply-To: <CAH6zzqCNtbCDSSFweuZp93R7TOxfEB8f75iosExb6sutyAa-Uw@mail.gmail.com>
References: <CAH6zzqCNtbCDSSFweuZp93R7TOxfEB8f75iosExb6sutyAa-Uw@mail.gmail.com>

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
>

view thread (26+ messages)  latest in thread

Message-ID: <4da46846-5835-8da7-b3c0-29c7b08e8b09@phystech.edu>
Permalink:  ../4da46846-5835-8da7-b3c0-29c7b08e8b09@phystech.edu/
Also on:    postgresql.org/message-id/4da46846-5835-8da7-b3c0-29c7b08e8b09@phystech.edu

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: zhilin.ms@phystech.edu, ashkildighin76@gmail.com, pgsql-performance@lists.postgresql.org
  Subject: Re: Lock contention high
  In-Reply-To: <4da46846-5835-8da7-b3c0-29c7b08e8b09@phystech.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox