Thread: We find few queries running three times simultaneously with sameparameters on postgres db
We find few queries running three times simultaneously with sameparameters on postgres db
From
amandeep singh
Date:
Hi Team
We have been observing our postgres database from past few days,We found few queries running three times simultaneously with same parameters.I would like to back track how a query is running multiple times.
2018-06-26 15:22:13.620045+05:30 | 00:00:00.198777 | active | 22234 | select * from xyz where x_id=$1
2018-06-26 15:22:13.618832+05:30 | 00:00:00.19999 | active | 22233 | select * from xyz where x_id=$1
2018-06-26 15:22:13.612721+05:30 | 00:00:00.206101 | active | 23927 | select * from xyz where x_id=$1
Our application is developed in java struts 1.3 and hibernate as ORM and tomcat 8.5 for hosting.
PostgreSQL version: 10.3
Kindly suggest.
Thanks and Regards:
Amandeep Singh
Re: We find few queries running three times simultaneously with sameparameters on postgres db
From
Andreas Kretschmer
Date:
Am 26.06.2018 um 12:19 schrieb amandeep singh: > We have been observing our postgres database from past few days,We > found few queries running three times simultaneously with same > parameters.I would like to back track how a query is running multiple > times. they are independend each other. You can run those queries with EXPLAIN ANALYSE to see the execution plan. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Re: We find few queries running three times simultaneously with sameparameters on postgres db
From
Saurabh Agrawal
Date:
Am 26.06.2018 um 12:19 schrieb amandeep singh:We have been observing our postgres database from past few days,We found few queries running three times simultaneously with same parameters.I would like to back track how a query is running multiple times.
Can you check the requests made by your application/ ORM? This looks like application is making multiple requests, rather than something happening on the database?
Re: We find few queries running three times simultaneously with sameparameters on postgres db
From
Edson Carlos Ericksson Richter
Date:
Em 26/06/2018 08:49, Saurabh Agrawal escreveu: > > > Am 26.06.2018 um 12:19 schrieb amandeep singh: > > We have been observing our postgres database from past few > days,We found few queries running three times simultaneously > with same parameters.I would like to back track how a query is > running multiple times. > > > Can you check the requests made by your application/ ORM? This looks > like application is making multiple requests, rather than something > happening on the database? > I agree with Saurabh Agrawal. Sometimes, users just double (or triple) click a form button, and then resulting activity is executed more than once... What I used is to deactivate buttons after first click. This is even more noticeable when working on intranet apps ou high speed internet connections. Users, sometimes, need to be educated that one click is enough :-) Regards, Edson.
Re: We find few queries running three times simultaneously with same parameters on postgres db
From
Andreas Kretschmer
Date:
On 26 June 2018 12:32:44 CEST, Andreas Kretschmer <andreas@a-kretschmer.de> wrote: > > >Am 26.06.2018 um 12:19 schrieb amandeep singh: >> We have been observing our postgres database from past few days,We >> found few queries running three times simultaneously with same >> parameters.I would like to back track how a query is running multiple > >> times. > >they are independend each other. You can run those queries with EXPLAIN > >ANALYSE to see the execution plan. > >Regards, Andreas seems like i misunderstud your query. are you sure, that the parameter $1 is equal across all different connections? Andreas -- 2ndQuadrant - The PostgreSQL Support Company
Re: We find few queries running three times simultaneously with sameparameters on postgres db
From
amandeep singh
Date:
Hi Andreas
The value for $1 is same in all queries.
@Edson: number of such running queries are always 3.
From: Andreas Kretschmer
Sent: Tuesday, 26 June, 6:04 PM
Subject: Re: We find few queries running three times simultaneously with same parameters on postgres db
To: pgsql-general@lists.postgresql.org
On 26 June 2018 12:32:44 CEST, Andreas Kretschmer wrote: > > >Am 26.06.2018 um 12:19 schrieb amandeep singh: >> We have been observing our postgres database from past few days,We >> found few queries running three times simultaneously with same >> parameters.I would like to back track how a query is running multiple > >> times. > >they are independend each other. You can run those queries with EXPLAIN > >ANALYSE to see the execution plan. > >Regards, Andreas seems like i misunderstud your query. are you sure, that the parameter $1 is equal across all different connections? Andreas -- 2ndQuadrant - The PostgreSQL Support Company
Re: We find few queries running three times simultaneously with sameparameters on postgres db
From
Melvin Davidson
Date:
On Tue, Jun 26, 2018 at 1:26 PM, amandeep singh <singh_a@live.in> wrote:
Hi AndreasThe value for $1 is same in all queries.@Edson: number of such running queries are always 3.From: Andreas KretschmerSent: Tuesday, 26 June, 6:04 PMSubject: Re: We find few queries running three times simultaneously with same parameters on postgres dbOn 26 June 2018 12:32:44 CEST, Andreas Kretschmer wrote: > > >Am 26.06.2018 um 12:19 schrieb amandeep singh: >> We have been observing our postgres database from past few days,We >> found few queries running three times simultaneously with same >> parameters.I would like to back track how a query is running multiple > >> times. > >they are independend each other. You can run those queries with EXPLAIN > >ANALYSE to see the execution plan. > >Regards, Andreas seems like i misunderstud your query. are you sure, that the parameter $1 is equal across all different connections? Andreas -- 2ndQuadrant - The PostgreSQL Support Company
This query will show you the pid, client_addr and user that submitted the queries, in addition to other useful information.
SELECT backend_start as be_start,
datname,
pid as pid,
client_addr,
usename as user,
state,
query,
wait_event_type,
query_start,
current_timestamp - query_start as duration
FROM pg_stat_activity
WHERE pg_backend_pid() <> pid
ORDER BY 1,
datname,
query_start;
datname,
pid as pid,
client_addr,
usename as user,
state,
query,
wait_event_type,
query_start,
current_timestamp - query_start as duration
FROM pg_stat_activity
WHERE pg_backend_pid() <> pid
ORDER BY 1,
datname,
query_start;
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Re: We find few queries running three times simultaneously with sameparameters on postgres db
From
Edson Carlos Ericksson Richter
Date:
Em 26/06/2018 14:26, amandeep singh escreveu: > Hi Andreas > > The value for $1 is same in all queries. > > @Edson: number of such running queries are always 3. I see. It seems a programming logic error to me. I also use JPA (Hibernate and/or EclipseLink) and I don't have such problem. But I could replicate your scenario doing the following (with EclipseLink, didn't test with Hibernate): 1) In persistence.xml, disable all caches (this is very important for make reproducible) 2) Load three Person records that live on same city (objects Person and City mapped to tables Person and City, being city an attribute of Person object): TypedQuery<Person> qry = em.createQuery("select P from Person P where P.city.name = :cityname"); qry.setParameter("cityname", "Porto Alegre"); qry.setMaxResults(3); List<Person> rs = qry.getResultList(); 3) This will cause one query against Person table, and exactly 3 queries issued with same parameters to City table. I really don't know how your code works, and is quite hard to guess, but as you can see, it is easy to reproduce similar behavior. Now, if I enable EclipseLink caches, only one query is issued for each City key - so, I'll have one query for Person and one query for only. Hope this helps to enlighten your problem. :-) Regards, Edson
Re: We find few queries running three times simultaneously with sameparameters on postgres db
From
amandeep singh
Date:
Thanks Edson for ur support.
As I am not a developer , I will share this test case with development team.
Once I get feedback from team, will share it on this mailing list.
From: Edson Carlos Ericksson Richter <richter@simkorp.com.br>
Sent: Wednesday, June 27, 2018 2:02:19 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: We find few queries running three times simultaneously with same parameters on postgres db
Em 26/06/2018 14:26, amandeep singh escreveu:
> Hi Andreas
>
> The value for $1 is same in all queries.
>
> @Edson: number of such running queries are always 3.
I see. It seems a programming logic error to me.
I also use JPA (Hibernate and/or EclipseLink) and I don't have such problem.
But I could replicate your scenario doing the following (with
EclipseLink, didn't test with Hibernate):
1) In persistence.xml, disable all caches (this is very important for
make reproducible)
2) Load three Person records that live on same city (objects Person and
City mapped to tables Person and City, being city an attribute of Person
object):
TypedQuery<Person> qry = em.createQuery("select P from Person P where
P.city.name = :cityname");
qry.setParameter("cityname", "Porto Alegre");
qry.setMaxResults(3);
List<Person> rs = qry.getResultList();
3) This will cause one query against Person table, and exactly 3 queries
issued with same parameters to City table.
I really don't know how your code works, and is quite hard to guess, but
as you can see, it is easy to reproduce similar behavior.
Now, if I enable EclipseLink caches, only one query is issued for each
City key - so, I'll have one query for Person and one query for only.
Hope this helps to enlighten your problem.
:-)
Regards,
Edson
Re: We find few queries running three times simultaneously with sameparameters on postgres db
From
amandeep singh
Date:
From: amandeep singh
Sent: Wednesday, 27 June, 8:42 PM
Subject: Re: We find few queries running three times simultaneously with same parameters on postgres db
To: pgsql-general@lists.postgresql.org, Edson Carlos Ericksson Richter
Thanks Edson for ur support.
As I am not a developer , I will share this test case with development team.
Once I get feedback from team, will share it on this mailing list.
From: Edson Carlos Ericksson Richter <richter@simkorp.com.br>
Sent: Wednesday, June 27, 2018 2:02:19 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: We find few queries running three times simultaneously with same parameters on postgres db
Em 26/06/2018 14:26, amandeep singh escreveu:
> Hi Andreas
>
> The value for $1 is same in all queries.
>
> @Edson: number of such running queries are always 3.
I see. It seems a programming logic error to me.
I also use JPA (Hibernate and/or EclipseLink) and I don't have such problem.
But I could replicate your scenario doing the following (with
EclipseLink, didn't test with Hibernate):
1) In persistence.xml, disable all caches (this is very important for
make reproducible)
2) Load three Person records that live on same city (objects Person and
City mapped to tables Person and City, being city an attribute of Person
object):
TypedQuery<Person> qry = em.createQuery("select P from Person P where
P.city.name = :cityname");
qry.setParameter("cityname", "Porto Alegre");
qry.setMaxResults(3);
List<Person> rs = qry.getResultList();
3) This will cause one query against Person table, and exactly 3 queries
issued with same parameters to City table.
I really don't know how your code works, and is quite hard to guess, but
as you can see, it is easy to reproduce similar behavior.
Now, if I enable EclipseLink caches, only one query is issued for each
City key - so, I'll have one query for Person and one query for only.
Hope this helps to enlighten your problem.
:-)
Regards,
Edson