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.albe@xxxxxxxxxxx> 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
