Обсуждение: Long Query

Hey All,

I'm developing a large, PosgreSQL-based content management system. It runs
via Apache/mod_perl. I have a weird issue that has come up with a few
queries: they take forever! They're nothing complex - simpl queries that
join two tables and look up data via a primary key ID. When I paste one
of these queries into psql, they return their results immediately. When
they're executed via the application, in mod_perl, however, they can take
up to 20 seconds! During that time, one postmaster shoots to the top of
the list in the top utility, sucking up 90%+ of the CPU time.

Can anyone suggest how I might figure out why the query takes so long in
one context but not another? Since it's the postmaster process that sucks
up all the cycles, I'm assuming that Perl/DBI is just waiting on
PostgreSQL. Any pointers to help with monitoring what Postgres is doing
during all that time would be helpful.

Thanks!

David

PS: The PostgreSQL server is a pretty default installation, with no
performance tuning. Here's the EXPLAIN output of one of the queries:

Merge Join  (cost=0.00..82.29 rows=1 width=240)
  ->  Index Scan using pk_story__id on story s  (cost=0.00..8.14 rows=10 width=168)
  ->  Index Scan using fdx_story__story_instance on story_instance i (cost=0.00..59.00 rows=1000 width=72)

EXPLAIN


--
David Wheeler                                         AIM: dwTheory
David@Wheeler.net                                     ICQ: 15726394
                                                   Yahoo!: dew7e
                                                   Jabber: Theory@jabber.org

David Wheeler <David@Wheeler.net> writes:

> I'm developing a large, PosgreSQL-based content management system. It runs
> via Apache/mod_perl. I have a weird issue that has come up with a few
> queries: they take forever! They're nothing complex - simpl queries that
> join two tables and look up data via a primary key ID. When I paste one
> of these queries into psql, they return their results immediately. When
> they're executed via the application, in mod_perl, however, they can take
> up to 20 seconds! During that time, one postmaster shoots to the top of
> the list in the top utility, sucking up 90%+ of the CPU time.
>
> Can anyone suggest how I might figure out why the query takes so long in
> one context but not another? Since it's the postmaster process that sucks
> up all the cycles, I'm assuming that Perl/DBI is just waiting on
> PostgreSQL. Any pointers to help with monitoring what Postgres is doing
> during all that time would be helpful.

Make sure PG is logging to a file (rather than /dev/null), crank up
the log level, and make sure the queries that PG gets are what the app
is supposed to send.

-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.

On 7 Aug 2001, Doug McNaught wrote:

> Make sure PG is logging to a file (rather than /dev/null), crank up
> the log level, and make sure the queries that PG gets are what the app
> is supposed to send.

Thanks for the suggestion - I'll do that.

As it turns out, though, our application is doing 100s of unnecessary
queries for virtually every request, and this is the root of the problem
-- not a bad PostgreSQL query. Sorry to have bothered everyone with this!

David

--
David Wheeler                                         AIM: dwTheory
David@Wheeler.net                                     ICQ: 15726394
                                                   Yahoo!: dew7e
                                                   Jabber: Theory@jabber.org