Thread: Zombie processes
I’m running out of ideas for the following problem:
I have a moderately complex query as follows-
SELECT t.term_id, a.user_id, a.time_slot, a.status,
SUM(CASE WHEN a.date>=t.start_date THEN 1 ELSE 0 END), COUNT(a. date)
FROM "Table1" a, "Table2" t, "Table2" ytd, "Table3" cu, "Table4" c, "Table5" co, "Table6" s
WHERE a.type=1 AND a.status IN(1,2,3,4) AND a.date>=ytd.start_date AND a.date<=t.end_date AND
a.date<=now() AND a.user_id=cu.user_id AND a.time_slot=cu.course_id AND cu.course_id=c.course_id AND co.course_offered_id=c.course_offered_id
AND co.school_id=s.school_id AND s.district_id=2 AND ytd.term_id=t.top_term_id GROUP BY a.user_id, a.time_slot, t.term_id, a.status
ORDER BY a.user_id, a.time_slot, t.term_id, a.status
I am running Postgres 8.0.1 on a Windows 2000 server as a service, and the query returns appropriate values after about 30 seconds when executed from a psql console. However, when running this query from a Java application on the same machine through the postgres JDBC driver, the Java app hangs on st.executeQuery(). The query shows up in pg_stat_activity for about 3 minutes then goes away, but the process referenced by the pg_stat_activity remains active and consumes 50% of the CPU resources indefinitely until I kill it off. I let it run for over an hour yesterday. I can reproduce this every time I run this query.
Any ideas?
Bjorn Peterson
Software Engineer
Pearson School Technologies
Bloomington, MN
Bjorn.Peterson@pearson.com
****************************************************************************
This email may contain confidential
material. If you were not an intended recipient,
Please notify the sender and delete all copies.
We may monitor email to and from our network.
***************************************************************************
Peterson, Bjorn wrote: > > I am running Postgres 8.0.1 on a Windows 2000 server as a service, and the > query returns appropriate values after about 30 seconds when executed from a > psql console. However, when running this query from a Java application on > the same machine through the postgres JDBC driver, the Java app hangs on > st.executeQuery(). The query shows up in pg_stat_activity for about 3 > minutes then goes away, but the process referenced by the pg_stat_activity > remains active and consumes 50% of the CPU resources indefinitely until I > kill it off. I let it run for over an hour yesterday. I can reproduce this > every time I run this query. Two things you should start with: 1. Turn query-logging on in your postgresql.conf so we can see exactly what is happening 2. See if you can simulate this using PREPARE/EXECUTE for the query. I'm guessing that the Java side of things is producing a prepared query and substituting parameters in. That might produce a different plan. First thing is to turn query logging on though, that way you can check exactly what is being executed. -- Richard Huxton Archonet Ltd
My coworkers and I studied the EXPLAIN and EXPLAIN ANALYZE output for this query and determined that the query planner seemed to be causing problems with this query. By calling "set enable_nestloop = off" before executing our query we were able to get the execution time down to under 2 minutes, which is acceptable for our purposes. Thanks for your response- Bjorn -----Original Message----- From: Richard Huxton [mailto:dev@archonet.com] Sent: Wednesday, March 30, 2005 1:31 AM To: Peterson, Bjorn Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Zombie processes Peterson, Bjorn wrote: > > I am running Postgres 8.0.1 on a Windows 2000 server as a service, and the > query returns appropriate values after about 30 seconds when executed from a > psql console. However, when running this query from a Java application on > the same machine through the postgres JDBC driver, the Java app hangs on > st.executeQuery(). The query shows up in pg_stat_activity for about 3 > minutes then goes away, but the process referenced by the pg_stat_activity > remains active and consumes 50% of the CPU resources indefinitely until I > kill it off. I let it run for over an hour yesterday. I can reproduce this > every time I run this query. Two things you should start with: 1. Turn query-logging on in your postgresql.conf so we can see exactly what is happening 2. See if you can simulate this using PREPARE/EXECUTE for the query. I'm guessing that the Java side of things is producing a prepared query and substituting parameters in. That might produce a different plan. First thing is to turn query logging on though, that way you can check exactly what is being executed. -- Richard Huxton Archonet Ltd **************************************************************************** This email may contain confidential material. If you were not an intended recipient, Please notify the sender and delete all copies. We may monitor email to and from our network. ****************************************************************************
Richard-
The prepare/execute problem would make sense to me, except that all of the
parameters in the query are integers and are being appended to the actual
query string (for various reasons) prior to calling prepareStatement()
rather than being set as parameters. The planner should have everything
right from the start. Does this seem to you to be a java caveat, or is it
an area where the query planner could be improved?
Thanks-
Bjorn
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Peterson, Bjorn
Sent: Wednesday, March 30, 2005 8:25 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Zombie processes
My coworkers and I studied the EXPLAIN and EXPLAIN ANALYZE output for this
query and determined that the query planner seemed to be causing problems
with this query. By calling "set enable_nestloop = off" before executing
our query we were able to get the execution time down to under 2 minutes,
which is acceptable for our purposes.
Thanks for your response-
Bjorn
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Wednesday, March 30, 2005 1:31 AM
To: Peterson, Bjorn
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Zombie processes
Peterson, Bjorn wrote:
>
> I am running Postgres 8.0.1 on a Windows 2000 server as a service, and the
> query returns appropriate values after about 30 seconds when executed from
a
> psql console. However, when running this query from a Java application on
> the same machine through the postgres JDBC driver, the Java app hangs on
> st.executeQuery(). The query shows up in pg_stat_activity for about 3
> minutes then goes away, but the process referenced by the pg_stat_activity
> remains active and consumes 50% of the CPU resources indefinitely until I
> kill it off. I let it run for over an hour yesterday. I can reproduce
this
> every time I run this query.
Two things you should start with:
1. Turn query-logging on in your postgresql.conf so we can see exactly
what is happening
2. See if you can simulate this using PREPARE/EXECUTE for the query.
I'm guessing that the Java side of things is producing a prepared query
and substituting parameters in. That might produce a different plan.
First thing is to turn query logging on though, that way you can check
exactly what is being executed.
--
Richard Huxton
Archonet Ltd
****************************************************************************
This email may contain confidential material.
If you were not an intended recipient,
Please notify the sender and delete all copies.
We may monitor email to and from our network.
****************************************************************************
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
****************************************************************************
This email may contain confidential material.
If you were not an intended recipient,
Please notify the sender and delete all copies.
We may monitor email to and from our network.
****************************************************************************
Peterson, Bjorn wrote: > My coworkers and I studied the EXPLAIN and EXPLAIN ANALYZE output for this > query and determined that the query planner seemed to be causing problems > with this query. By calling "set enable_nestloop = off" before executing > our query we were able to get the execution time down to under 2 minutes, > which is acceptable for our purposes. Tampering with the enable_xxx settings is NOT recommended for production systems. You might be better off posting the EXPLAIN ANALYSE details to the performance list. -- Richard Huxton Archonet Ltd