On 4/2/20 12:37 PM, postgann2020 s wrote:
Hi Team,
Good Evening,
Could someone please help us share the procedure to troubleshoot the
locks on proc issues.
Environment:
============
1 pgpool server (Master Pool Node) using Straming replication with
load balancing
4 DB nodes (1Master and 3 Slaves).
Versions:
1. postgres: 9.5.15
2. pgpool : 3.9
3. repmgr: 4.1
We are continuously facing locking issues for below procedures , due to
this the rest of the call for these procs going into waiting
state.Which cause the DB got hung. Below are the procs running with
DB_User2 from the application.
1. select * from Schema1.duct_remove_validation($1,$2,$3,$4) ==> This
proc it self calling Schema1.cable_remove_validation($1,$2).
2. select * from Schema1.cable_remove_validation($1,$2) ==> This is
also calling from the applications
To figure out below we need to see what is happening in above.
if we ran explain analyze, its taking msec only, but if we run
simultaneouly from application getting locked and waiting state.
We have ran below query for showing blocking queries and attached output
in Blocking_Queries_with_PID.csv file:
SELECT
pl.pid as blocked_pid
,psa.usename as blocked_user
,pl2.pid as blocking_pid
,psa2.usename as blocking_user
,psa.query as blocked_statement
FROM pg_catalog.pg_locks pl
JOIN pg_catalog.pg_stat_activity psa
ON pl.pid = psa.pid
JOIN pg_catalog.pg_locks pl2
JOIN pg_catalog.pg_stat_activity psa2
ON pl2.pid = psa2.pid
ON pl.transactionid = pl2.transactionid
AND pl.pid != pl2.pid
WHERE NOT pl.granted;
Output: attached output in Blocking_Queries_with_PID.csv file
The waiting connections are keep on accumulating and cause DB hung.
I have attached pg_stat_activity excel file with the user along with the
proc queries which cause waiting state.
Finds:
There are total 18 connections for DB_User2 which are running only above
2 procs, Out of that only one connection with 18732 is running proc
(select * from Schema1.duct_remove_validation($1,$2,$3,$4))from long
time and reset of all 17 connections are in waiting state from the long
time.
There are many exclusive locks on table for 18732 and other process as
well. I have attached pg_locks reference excel(Lock_Reference_For_PROC)
with highlighted pid 18732.
Could someone please suggest the procedure to troubleshoot this issue.
Please find the attachment for reference.
Thanks,
Postgann.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
