From: Adrian Klaver <adrian.klaver@aklaver.com>
To: postgann2020 s <postgann2020@gmail.com>
To: pgsql-general@postgresql.org
To: pgsql-performance@postgresql.org
To: pgsql-admin@postgresql.org
Subject: Re: Could someone please help us share the procedure to troubleshoot the locks on proc issues.
Date: Thu, 2 Apr 2020 16:00:12 -0700
Message-ID: <b91f4296-c72f-ae4d-a788-4c9d2a193cd2@aklaver.com> (raw)
In-Reply-To: <CANynezMVB145OgFU_oniPQe9D_jBFtSSKJpO3-xBHe7T1L8geQ@mail.gmail.com>
References: <CANynezMVB145OgFU_oniPQe9D_jBFtSSKJpO3-xBHe7T1L8geQ@mail.gmail.com>
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.klaver@aklaver.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adrian.klaver@aklaver.com, postgann2020@gmail.com, pgsql-general@postgresql.org, pgsql-admin@postgresql.org
Subject: Re: Could someone please help us share the procedure to troubleshoot the locks on proc issues.
In-Reply-To: <b91f4296-c72f-ae4d-a788-4c9d2a193cd2@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox