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.
