Re: Could someone please help us share the procedure to troubleshoot the locks on proc issues.
Thanks Adrian, will share the details. On Fri, Apr 3, 2020 at 4:30 AM Adrian Klaver 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 >
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.kla...@aklaver.com
Could someone please help us share the procedure to troubleshoot the locks on proc issues.
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 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. Blocking_PROCS_With_PIDS.csv Description: MS-Excel spreadsheet pg_stat_activity_output_for_clarification.xlsx Description: MS-Excel 2007 spreadsheet Lock_Reference_For_PROC.xlsx Description: MS-Excel 2007 spreadsheet
Re: BUG #16334: We recently upgraded PG version from 9.5 to 10.10 and system performance is not so good
Hello Julien, Thanks for your inputs, I shall get back to you If some information is needed. *Regards* *Tejaswini G C* *IT Retail Team* On Thu, Apr 2, 2020 at 4:35 PM Julien Rouhaud wrote: > Please don't top-post, it makes it hard to follow the discussion. > > On Thu, Apr 02, 2020 at 03:52:56PM +0530, Tejaswini GC wrote: > > > > I'm in touch with our hosting team to get more information for your > queries. > > As of now I can share these details. > > We did analyze on the DB, but not the vacuum, > > We are using AWS RDS. > > > > Please help me with these queries as well. > > > > 1) Will the process change if we use AWS RDS. > > > No idea, that's a question you should ask them. > > > > 2) What kind of vacuum should be done on the DB, as there are many types > of > > vacuum. > > > A regular vacuum, as in: > > VACUUM ANALYZE > > in all your databases. > > > > On Thu, Apr 2, 2020 at 2:17 PM Julien Rouhaud > wrote: > > > > > Hi, > > > > > > On Thu, Apr 02, 2020 at 03:52:25AM +, PG Bug reporting form wrote: > > > > The following bug has been logged on the website: > > > > > > > > Bug reference: 16334 > > > > Logged by: Tejaswini GC > > > > Email address: tejaswini...@decathlon.com > > > > PostgreSQL version: 10.10 > > > > Operating system: Centos 7 > > > > Description: > > > > > > > > > First of all, this is not a bug. You should have instead started a > > > discussion > > > on pgsql-general or pgsql-performance. I'm redirecting the discussion > on > > > -performance. > > > > > > > > > > We have upgraded our database into version 10.10. > > > > > > > > > How did you upgrade? > > > > > > > > > > After upgrading we could see that the system performance is bad , and > > > one of > > > > the applications linked to it via web service is not working. > > > > > > > > > Do you have any errors in the postgres logs? > > > > > > > > > > During this upgrade we have not done any code changes either on the > > > > application side or on our ERP side. > > > > > > > > We are trying to debug everything from application perse, but till > now > > > we do > > > > not have any lead. > > > > > > > > Can you tell us are there any measures that we need to take after > > > upgrade. > > > > > > > > > It depends on how you did the upgrade. If you used pg_upgrade, did you > > > run the > > > generated script as documented in step 13 at > > > https://www.postgresql.org/docs/current/pgupgrade.html? > > > > > > Otherwise, at least a database-wide VACUUM ANALYZE on every database > is the > > > bare minimum to run after an upgrade. > > > >
Re: BUG #16334: We recently upgraded PG version from 9.5 to 10.10 and system performance is not so good
Please don't top-post, it makes it hard to follow the discussion. On Thu, Apr 02, 2020 at 03:52:56PM +0530, Tejaswini GC wrote: > > I'm in touch with our hosting team to get more information for your queries. > As of now I can share these details. > We did analyze on the DB, but not the vacuum, > We are using AWS RDS. > > Please help me with these queries as well. > > 1) Will the process change if we use AWS RDS. No idea, that's a question you should ask them. > 2) What kind of vacuum should be done on the DB, as there are many types of > vacuum. A regular vacuum, as in: VACUUM ANALYZE in all your databases. > On Thu, Apr 2, 2020 at 2:17 PM Julien Rouhaud wrote: > > > Hi, > > > > On Thu, Apr 02, 2020 at 03:52:25AM +, PG Bug reporting form wrote: > > > The following bug has been logged on the website: > > > > > > Bug reference: 16334 > > > Logged by: Tejaswini GC > > > Email address: tejaswini...@decathlon.com > > > PostgreSQL version: 10.10 > > > Operating system: Centos 7 > > > Description: > > > > > > First of all, this is not a bug. You should have instead started a > > discussion > > on pgsql-general or pgsql-performance. I'm redirecting the discussion on > > -performance. > > > > > > > We have upgraded our database into version 10.10. > > > > > > How did you upgrade? > > > > > > > After upgrading we could see that the system performance is bad , and > > one of > > > the applications linked to it via web service is not working. > > > > > > Do you have any errors in the postgres logs? > > > > > > > During this upgrade we have not done any code changes either on the > > > application side or on our ERP side. > > > > > > We are trying to debug everything from application perse, but till now > > we do > > > not have any lead. > > > > > > Can you tell us are there any measures that we need to take after > > upgrade. > > > > > > It depends on how you did the upgrade. If you used pg_upgrade, did you > > run the > > generated script as documented in step 13 at > > https://www.postgresql.org/docs/current/pgupgrade.html? > > > > Otherwise, at least a database-wide VACUUM ANALYZE on every database is the > > bare minimum to run after an upgrade. > >
Re: BUG #16334: We recently upgraded PG version from 9.5 to 10.10 and system performance is not so good
Hello Julien, Thanks for your response. I'm in touch with our hosting team to get more information for your queries. As of now I can share these details. We did analyze on the DB, but not the vacuum, We are using AWS RDS. Please help me with these queries as well. 1) Will the process change if we use AWS RDS. 2) What kind of vacuum should be done on the DB, as there are many types of vacuum. Awaiting your reply! Thanks! *Regards* *Tejaswini G C* *IT Retail Team* On Thu, Apr 2, 2020 at 2:17 PM Julien Rouhaud wrote: > Hi, > > On Thu, Apr 02, 2020 at 03:52:25AM +, PG Bug reporting form wrote: > > The following bug has been logged on the website: > > > > Bug reference: 16334 > > Logged by: Tejaswini GC > > Email address: tejaswini...@decathlon.com > > PostgreSQL version: 10.10 > > Operating system: Centos 7 > > Description: > > > First of all, this is not a bug. You should have instead started a > discussion > on pgsql-general or pgsql-performance. I'm redirecting the discussion on > -performance. > > > > We have upgraded our database into version 10.10. > > > How did you upgrade? > > > > After upgrading we could see that the system performance is bad , and > one of > > the applications linked to it via web service is not working. > > > Do you have any errors in the postgres logs? > > > > During this upgrade we have not done any code changes either on the > > application side or on our ERP side. > > > > We are trying to debug everything from application perse, but till now > we do > > not have any lead. > > > > Can you tell us are there any measures that we need to take after > upgrade. > > > It depends on how you did the upgrade. If you used pg_upgrade, did you > run the > generated script as documented in step 13 at > https://www.postgresql.org/docs/current/pgupgrade.html? > > Otherwise, at least a database-wide VACUUM ANALYZE on every database is the > bare minimum to run after an upgrade. >
Re: BUG #16334: We recently upgraded PG version from 9.5 to 10.10 and system performance is not so good
Hi, On Thu, Apr 02, 2020 at 03:52:25AM +, PG Bug reporting form wrote: > The following bug has been logged on the website: > > Bug reference: 16334 > Logged by: Tejaswini GC > Email address: tejaswini...@decathlon.com > PostgreSQL version: 10.10 > Operating system: Centos 7 > Description: First of all, this is not a bug. You should have instead started a discussion on pgsql-general or pgsql-performance. I'm redirecting the discussion on -performance. > We have upgraded our database into version 10.10. How did you upgrade? > After upgrading we could see that the system performance is bad , and one of > the applications linked to it via web service is not working. Do you have any errors in the postgres logs? > During this upgrade we have not done any code changes either on the > application side or on our ERP side. > > We are trying to debug everything from application perse, but till now we do > not have any lead. > > Can you tell us are there any measures that we need to take after upgrade. It depends on how you did the upgrade. If you used pg_upgrade, did you run the generated script as documented in step 13 at https://www.postgresql.org/docs/current/pgupgrade.html? Otherwise, at least a database-wide VACUUM ANALYZE on every database is the bare minimum to run after an upgrade.
