date:20211014

Re: Lock contention high

2021-10-14 Thread Mikhail Zhilin


Ashkil,

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

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

Re: Lock contention high

2021-10-14 Thread Ashkil Dighin

Hi
Captured the concurrent session with Netsat and pg-stat-actvity. Is the
procedure the right way to capture concurrent sesssions in postgresql?

netstat -a | grep postgres tcp 0 0 0.0.0.0:postgres 0.0.0.0:* LISTEN tcp 0
0 :postgres :53984 ESTABLISHED tcp 0 0 :postgres :54012 ESTABLISHED tcp 0
74 :postgres :53998 ESTABLISHED tcp 0 73 :53986 :postgres ESTABLISHED tcp 0
0 :54004 :postgres ESTABLISHED tcp 0 75 :53990 :postgres ESTABLISHED tcp 0
0 :postgres :53994 ESTABLISHED tcp 0 0 :postgres :54004 ESTABLISHED tcp 0
106 :53978 :postgres ESTABLISHED tcp 0 0 :postgres :53972 ESTABLISHED tcp 0
90 :54000 :postgres ESTABLISHED tcp 0 0 :postgres :54018 ESTABLISHED tcp 0
0 :54016 :postgres ESTABLISHED tcp 0 0 :postgres :53986 ESTABLISHED tcp 0
59 :54006 :postgres ESTABLISHED tcp 0 74 :postgres :53982 ESTABLISHED tcp 0
75 :53994 :postgres ESTABLISHED tcp 0 0 :53970 :postgres ESTABLISHED tcp 0
0 :postgres :53974 ESTABLISHED tcp 0 76 :53988 :postgres ESTABLISHED tcp 0
0 :postgres :54008 ESTABLISHED tcp 0 93 :54014 :postgres ESTABLISHED tcp 0
74 :54012 :postgres ESTABLISHED tcp 0 75 :53972 :postgres ESTABLISHED tcp 0
76 :54002 :postgres ESTABLISHED tcp 0 68 :postgres :54006 ESTABLISHED tcp 0
0 :postgres :53978 ESTABLISHED tcp 0 73 :54008 :postgres ESTABLISHED tcp 0
0 :postgres :53976 ESTABLISHED tcp 0 93 :53974 :postgres ESTABLISHED tcp 0
59 :53998 :postgres ESTABLISHED tcp 74 0 :53984 :postgres ESTABLISHED tcp 0
0 :postgres :54014 ESTABLISHED tcp 0 76 :53982 :postgres ESTABLISHED tcp 0
0 :postgres :54002 ESTABLISHED tcp 0 76 :53996 :postgres ESTABLISHED tcp 0
0 :postgres :53990 ESTABLISHED tcp 0 59 :53976 :postgres ESTABLISHED tcp 0
74 :postgres :53996 ESTABLISHED tcp 0 76 :53992 :postgres ESTABLISHED tcp 0
0 :postgres :54016 ESTABLISHED tcp 0 0 :postgres :54000 ESTABLISHED tcp 0 0
:postgres :53980 ESTABLISHED tcp 0 77 :53980 :postgres ESTABLISHED tcp 0 74
:54018 :postgres ESTABLISHED tcp 0 0 :postgres :53970 ESTABLISHED tcp 0 0
:postgres :53988 ESTABLISHED tcp 0 104 :54010 :postgres ESTABLISHED tcp 0 0
:postgres :54010 ESTABLISHED tcp 0 0 :postgres :53992 ESTABLISHED tcp6 0 0
[::]:postgres

Select pg_stat_activity


datid | datname | pid | leader_pid | usesysid | usename | application_name
| client_addr | client_hostname | client_port | backend_start | xact_start
| query_start | state_change | wait_event_type | wait_event | state |
backend_xid | backend_xmin | query | backend_type
---+--+-++--+--+--+-+-+-+---+---+---+---+-+-++-+--+-+--
| | 2092230 | | 10 | postgres | | | | | 2021-10-13 02:41:12.083391-04 | | |
| Activity | LogicalLauncherMain | | | | | logical replication launcher 16385
| tpcc | 2092540 | | 16384 | tpcc | | 127.0.0.1 | | 53970 | 2021-10-13
02:41:57.336031-04 | | 2021-10-13 02:43:58.97025-04 | 2021-10-13
02:43:58.971538-04 | Client | ClientRead | idle | | | select
sum(d_next_o_id) from district | client backend 16385 | tpcc | 2092541 | |
16384 | tpcc | | 127.0.0.1 | | 53972 | 2021-10-13 02:41:57.836054-04 |
2021-10-13 02:44:04.649045-04 | 2021-10-13 02:44:04.649054-04 | 2021-10-13
02:44:04.649055-04 | | | active | 11301598 | 11301493 | prepare delivery
(INTEGER, INTEGER) AS select delivery($1,$2) | client backend 16385 | tpcc
| 2092548 | | 16384 | tpcc | | 127.0.0.1 | | 53974 | 2021-10-13
02:41:58.336566-04 | 2021-10-13 02:44:04.649153-04 | 2021-10-13
02:44:04.649163-04 | 2021-10-13 02:44:04.649163-04 | | | active | 11301611
| 11301493 | prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER)
as select neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092549
| | 16384 | tpcc | | 127.0.0.1 | | 53976 | 2021-10-13 02:41:58.836269-04 |
2021-10-13 02:44:04.649443-04 | 2021-10-13 02:44:04.649454-04 | 2021-10-13
02:44:04.649454-04 | | | active | | 11301528 | prepare neword (INTEGER,
INTEGER, INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) |
client backend 16385 | tpcc | 2092556 | | 16384 | tpcc | | 127.0.0.1 | |
53978 | 2021-10-13 02:41:59.336172-04 | 2021-10-13 02:44:04.648817-04 |
2021-10-13 02:44:04.648827-04 | 2021-10-13 02:44:04.648828-04 | | | active
| | 11301493 | prepare slev (INTEGER, INTEGER, INTEGER) AS select
slev($1,$2,$3) | client backend 16385 | tpcc | 2092557 | | 16384 | tpcc | |
127.0.0.1 | | 53980 | 2021-10-13 02:41:59.83835-04 | 2021-10-13
02:44:04.649027-04 | 2021-10-13 02:44:04.649036-04 | 2021-10-13
02:44:04.649036-04 | | | active | | 11301493 | prepare slev (INTEGER,
INTEGER, INTEGER) AS select slev($1,$2,$3) | client backend 16385 | tpcc |
2092564 | | 16384 | tpcc | | 127.0.0.1 | | 53982 | 2021-10-13
02:42:00.336974-04 | 2021-10-13 02:44:04.649194-04 | 2021

Re: Lock contention high

2021-10-14 Thread Ashkil Dighin

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

Re: Lock contention high

2021-10-14 Thread Ashkil Dighin

Not using PostGIS

On Thursday, October 14, 2021, Paul Friedman <
[email protected]> wrote:

> Are you using PostGIS?
>
> If so, there is an issue with TOAST table locking having these symptoms.
>
>
> ---Paul
>
>
> On Wed, Oct 13, 2021 at 11:15 AM MichaelDBA 
> wrote:
>
>> 1.Is there a way to tune the lock contention ?
>> 2.Is any recommendations to tune/reduce the lock contention via postgres.conf
>>
>> I think you'd want to find *which* LW locks are being waited on, to see if 
>> it's
>> something that can be easily tuned.
>>
>> You can check pg_stat_activity, or maybe create a cronjob to record its 
>> content
>> for later analysis.
>>
>>
>> Hello,
>>
>> Also turn on log_lock_waits so you can evaluate the actual SQL causing
>> the problems in the PG log files.  Thinking ahead, you may want to consider
>> if using advisory locks from the application side of things might be
>> helpful to manage locks in a more pessimistic way.  Also, join with
>> pg_locks table to find out the specific resources that are in contention.
>>
>> Regards,
>> Michael Vitale
>>
>>
>>

Re: Lock contention high

Re: Lock contention high

Re: Lock contention high

Re: Lock contention high

4 matches

Site Navigation

Mail list logo

Footer information