Hi!
I'm using a server with Redhat 7.2, Apache 1.3.22, Php 4.0.6 & PostgreSQL
7.1.3.
PROBLEM:
When I execute a long PostgreSQL query via PHP code (involves joining 12
tables), I can't access any other webpage of the server while is processing the
query & displaying the results (opening a new webpage causes that remains blank
until the classic "page not found" appears).
SYNTOMPS:
Doing a 'top' shows postmaster has 98% of the processor. The strange fact
is the frontend (psql) lets do another queries in the meanwhile, but Apache
won't display any other page of the server until the whole query and page
displays finishes. That behaviour is catastrophic for the rest of the users,
moreover when the query takes almost 1 minute.
MEASURES:
I've already applied many optimizations like vacuuming before, and adding
the indexes for the primaries & foreign keys involved, and the ones from the
article http://www.phpbuilder.com/columns/smith20010821.php3 (128MB in shm_max).
I'm not using persistent connections to the db, and I'm allowing 32
connections on postgresql.conf (max_connections) & php.ini (pgsql.max_links).
Any suggestion how to solve the "greedy behaviour" of the web server ?
Thanx in advance, Dinko