Re: Could someone please help us share the procedure to troubleshoot the locks on proc issues.

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