Re: Lock contention high

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-10-13
02:44:04.649203-04 | 2021-10-13 02:44:04.649203-04 | | | active | 11301619
| 11301493 | prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER)
as select neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092565
| | 16384 | tpcc | | 127.0.0.1 | | 53984 | 2021-10-13 02:42:00.838269-04 |
2021-10-13 02:44:04.649441-04 | 2021-10-13 02:44:04.649452-04 | 2021-10-13
02:44:04.649453-04 | | | active | | 11301528 | prepare neword (INTEGER,
INTEGER, INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) |
client backend 16385 | tpcc | 2092572 | | 16384 | tpcc | | 127.0.0.1 | |
53986 | 2021-10-13 02:42:01.337933-04 | 2021-10-13 02:44:04.648136-04 |
2021-10-13 02:44:04.648144-04 | 2021-10-13 02:44:04.648144-04 | | | active
| 11301528 | 11301396 | prepare delivery (INTEGER, INTEGER) AS select
delivery($1,$2) | client backend 16385 | tpcc | 2092573 | | 16384 | tpcc |
| 127.0.0.1 | | 53988 | 2021-10-13 02:42:01.839434-04 | 2021-10-13
02:44:04.648999-04 | 2021-10-13 02:44:04.649007-04 | 2021-10-13
02:44:04.649007-04 | LWLock | ProcArray | active | 11301596 | 11301493 |
prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092580 | | 16384
| tpcc | | 127.0.0.1 | | 53990 | 2021-10-13 02:42:02.339335-04 | 2021-10-13
02:44:04.649463-04 | 2021-10-13 02:44:04.649474-04 | 2021-10-13
02:44:04.649474-04 | | | active | | 11301528 | prepare neword (INTEGER,
INTEGER, INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) |
client backend 16385 | tpcc | 2092581 | | 16384 | tpcc | | 127.0.0.1 | |
53992 | 2021-10-13 02:42:02.838867-04 | 2021-10-13 02:44:04.649161-04 |
2021-10-13 02:44:04.64917-04 | 2021-10-13 02:44:04.64917-04 | | | active |
11301616 | 11301493 | prepare neword (INTEGER, INTEGER, INTEGER, INTEGER,
INTEGER) as select neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc |
2092588 | | 16384 | tpcc | | 127.0.0.1 | | 53994 | 2021-10-13
02:42:03.343136-04 | 2021-10-13 02:44:04.64934-04 | 2021-10-13
02:44:04.649351-04 | 2021-10-13 02:44:04.649352-04 | | | active | |
11301528 | prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as
select neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092589 | |
16384 | tpcc | | 127.0.0.1 | | 53996 | 2021-10-13 02:42:03.839278-04 |
2021-10-13 02:44:04.648822-04 | 2021-10-13 02:44:04.648834-04 | 2021-10-13
02:44:04.648834-04 | | | active | | | prepare neword (INTEGER, INTEGER,
INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) | client
backend 16385 | tpcc | 2092596 | | 16384 | tpcc | | 127.0.0.1 | | 53998 |
2021-10-13 02:42:04.34021-04 | 2021-10-13 02:44:04.649134-04 | 2021-10-13
02:44:04.649143-04 | 2021-10-13 02:44:04.649144-04 | | | active | 11301614
| 11301493 | prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER)
as select neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092597
| | 16384 | tpcc | | 127.0.0.1 | | 54000 | 2021-10-13 02:42:04.840163-04 |
2021-10-13 02:44:04.649429-04 | 2021-10-13 02:44:04.649438-04 | 2021-10-13
02:44:04.649438-04 | | | active | | 11301528 | prepare delivery (INTEGER,
INTEGER) AS select delivery($1,$2) | client backend 16385 | tpcc | 2092604
| | 16384 | tpcc | | 127.0.0.1 | | 54002 | 2021-10-13 02:42:05.340832-04 |
2021-10-13 02:44:04.649156-04 | 2021-10-13 02:44:04.649166-04 | 2021-10-13
02:44:04.649166-04 | LWLock | WALInsert | active | 11301618 | 11301493 |
prepare neword (INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092605 | | 16384
| tpcc | | 127.0.0.1 | | 54004 | 2021-10-13 02:42:05.841658-04 | 2021-10-13
02:44:04.649089-04 | 2021-10-13 02:44:04.649099-04 | 2021-10-13
02:44:04.6491-04 | | | active | 11301608 | 11301493 | prepare neword
(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092612 | | 16384
| tpcc | | 127.0.0.1 | | 54006 | 2021-10-13 02:42:06.342751-04 | 2021-10-13
02:44:04.649428-04 | 2021-10-13 02:44:04.649437-04 | 2021-10-13
02:44:04.649437-04 | | | active | | 11301528 | prepare delivery (INTEGER,
INTEGER) AS select delivery($1,$2) | client backend 16385 | tpcc | 2092613
| | 16384 | tpcc | | 127.0.0.1 | | 54008 | 2021-10-13 02:42:06.841509-04 |
2021-10-13 02:44:04.649237-04 | 2021-10-13 02:44:04.649249-04 | 2021-10-13
02:44:04.649249-04 | | | active | 11301622 | 11301493 | prepare neword
(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092620 | | 16384
| tpcc | | 127.0.0.1 | | 54010 | 2021-10-13 02:42:07.341743-04 | 2021-10-13
02:44:04.648736-04 | 2021-10-13 02:44:04.648746-04 | 2021-10-13
02:44:04.648746-04 | | | active | 11301580 | 11301493 | prepare neword
(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092621 | | 16384
| tpcc | | 127.0.0.1 | | 54012 | 2021-10-13 02:42:07.841876-04 | 2021-10-13
02:44:04.648983-04 | 2021-10-13 02:44:04.648991-04 | 2021-10-13
02:44:04.648991-04 | | | active | 11301600 | 11301493 | prepare neword
(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) as select
neword($1,$2,$3,$4,$5,0) | client backend 16385 | tpcc | 2092628 | | 16384
| tpcc | | 127.0.0.1 | | 54014 | 2021-10-13 02:42:08.342179-04 | 2021-10-13
02:44:04.649464-04 | 2021-10-13 02:44:04.649473-04 | 2021-10-13
02:44:04.649474-04 | | | active | | 11301528 | prepare neword (INTEGER,
INTEGER, INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) |
client backend 16385 | tpcc | 2092629 | | 16384 | tpcc | | 127.0.0.1 | |
54016 | 2021-10-13 02:42:08.845321-04 | 2021-10-13 02:44:04.649456-04 |
2021-10-13 02:44:04.649472-04 | 2021-10-13 02:44:04.649472-04 | | | active
| | 11301528 | prepare slev (INTEGER, INTEGER, INTEGER) AS select
slev($1,$2,$3) | client backend 16385 | tpcc | 2092636 | | 16384 | tpcc | |
127.0.0.1 | | 54018 | 2021-10-13 02:42:09.341768-04 | 2021-10-13
02:44:04.649394-04 | 2021-10-13 02:44:04.649404-04 | 2021-10-13
02:44:04.649404-04 | | | active | | 11301528 | prepare neword (INTEGER,
INTEGER, INTEGER, INTEGER, INTEGER) as select neword($1,$2,$3,$4,$5,0) |
client backend 12711 | postgres | 2093365 | | 10 | postgres | psql | | | -1
| 2021-10-13 02:44:04.64633-04 | 2021-10-13 02:44:04.648186-04 | 2021-10-13
02:44:04.648186-04 | 2021-10-13 02:44:04.648186-04 | | | active | |
11301528 | select * from pg_stat_activity; | client backend | | 2092227 | |
| | | | | | 2021-10-13 02:41:12.082448-04 | | | | Activity | BgWriterMain |
| | | | background writer | | 2092226 | | | | | | | | 2021-10-13
02:41:12.081979-04 | | | | Activity | CheckpointerMain | | | | |
checkpointer | | 2092228 | | | |

On Tuesday, October 12, 2021, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Tue, 2021-10-12 at 13:05 +0530, Ashkil Dighin wrote:
> > 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 ?
>
> How many concurrent sesions are you running?
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>