Thread: 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