Hi Team,
Good Evening,
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
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
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.
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
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.
Thanks,
Postgann.
Attachment:
Blocking_PROCS_With_PIDS.csv
Description: MS-Excel spreadsheet
Attachment:
pg_stat_activity_output_for_clarification.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Attachment:
Lock_Reference_For_PROC.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
