Thread: Zombie processes

Zombie processes

From
"Peterson, Bjorn"
Date:

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.

 ***************************************************************************

 

Re: Zombie processes

From
Richard Huxton
Date:
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

Re: Zombie processes

From
"Peterson, Bjorn"
Date:
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.
****************************************************************************

Re: Zombie processes

From
"Peterson, Bjorn"
Date:
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.
****************************************************************************

Re: Zombie processes

From
Richard Huxton
Date:
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