Thanks Adrian, will share the details.
On Fri, Apr 3, 2020 at 4:30 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> 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(dot)klaver(at)aklaver(dot)com
>