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