Thread: mod_perl/dbi/apache query will not finish

mod_perl/dbi/apache query will not finish

From
"Jason S. Friedman"
Date:
I've been experiencing this problem ever since upgrading from RedHat 7.3
to 8.0, which included a Postgresql upgrde to version 7.2.2.

I send a query to the Postgres backend using a mod_perl script and DBI.
I watch the trace log, it looks something like this:

dbd_st_execute: statement = >SELECT CITY_ID, CITY_NAME
FROM LOCAL_INFO_CITIES
WHERE STATE_ID = '5'
AND ( PROVISIONAL_IND <> 'P' OR CITY_ID = 0 )
ORDER BY CITY_NAME
<
    <- execute= 76 at create_local_info_2.pl line 61
    -> bind_columns for DBD::Pg::st (DBI::st=HASH(0x82c6ad4)~0x82e7df8 undef SCALAR(0x82a0e68) SCALAR(0x82a0e80))
thr#804b3c0
    <- bind_columns= 1 at create_local_info_2.pl line 62
    -> fetch for DBD::Pg::st (DBI::st=HASH(0x82c6ad4)~0x82e7df8) thr#804b3c0
dbd_st_fetch
    <- fetch= [ '10040' 'Agoura Hills' ] row1 at create_local_info_2.pl line 63
    -> fetch for DBD::Pg::st (DBI::st=HASH(0x82c6ad4)~0x82e7df8) thr#804b3c0
dbd_st_fetch
    <- fetch= [ '10019' 'All Cities' ] row2 at create_local_info_2.pl line 63
    -> fetch for DBD::Pg::st (DBI::st=HASH(0x82c6ad4)~0x82e7df8) thr#804b3c0
dbd_st_fetch
    <- fetch= [ '752' 'Auberry' ] row3 at create_local_info_2.pl line 63

...

    <- fetch= [ '564' 'Irvine' ] row27 at create_local_info_2.pl line 63
    -> fetch for DBD::Pg::st (DBI::st=HASH(0x82c6ad4)~0x82e7df8) thr#804b3c0
dbd_st_fetch
    <- fetch= [ '244' 'Lake Elsinore' ] row28 at create_local_info_2.pl line 63


And that's it.  I can see that it wants to return 76 rows, but it stops at row28.  Sometimes, several minutes later, it
doesreturn the remaining rows, but by then the browser has given up. 

Queries returning, say, only 25 rows work beautifully.

The processor load is light.  Here's a partial output from a "top" command:

  5:00am  up 11 days, 17 min,  1 user,  load average: 0.16, 0.42, 0.31
150 processes: 147 sleeping, 2 running, 1 zombie, 0 stopped
CPU states:  1.1% user,  2.6% system,  0.0% nice,  1.3% idle
Mem:   384680K av,  375728K used,    8952K free,       0K shrd,   26500K buff
Swap:  787176K av,  106680K used,  680496K free                  242228K cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
31788 matching  15   0  1072 1072   780 R     6.5  0.2   0:00 top
28681 powerpul  15   0 22964  17M 13232 S     0.9  4.5   1:44 galeon-bin
    1 root      15   0   472  436   424 S     0.0  0.1   0:05 init

$ uname -a
Linux abigail 2.4.18-19.8.0 #1 Thu Dec 12 04:37:40 EST 2002 i686 athlon i386 GNU/Linux

Re: mod_perl/dbi/apache query will not finish

From
Keith Keller
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, Jan 15, 2003 at 05:13:32AM +0000, Jason S. Friedman wrote:
> I send a query to the Postgres backend using a mod_perl script and DBI.
> I watch the trace log, it looks something like this:
> [snips]
> And that's it.  I can see that it wants to return 76 rows, but it stops at row28.  Sometimes, several minutes later,
itdoes return the remaining rows, but by then the browser has given up. 

What happens when you run the script from the commandline, or
in a CGI-but-not-mod_perl environment?  In particular, if running
the script from a command prompt exhibits the same behaviour,
I'd suspect some code that you're using that's changed in
DBI versions.  (That's probably a good place to start, since
you mentioned that the queries run fine from psql.)

- --keith

- --
kkeller@speakeasy.net
public key:  http://wombat.san-francisco.ca.us/kkeller/kkeller.asc
alt.os.linux.slackware FAQ:  http://wombat.san-francisco.ca.us/cgi-bin/fom
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAj4lAC8ACgkQhVcNCxZ5ID8ijACdF4Pj4QGr6Xxr9+N/uGVV7Sbb
xcQAnRrfJySCPo8A7sjAonQNh72db0JK
=FPVE
-----END PGP SIGNATURE-----