Re: too many idle processes

On Tue, May 21, 2002 at 04:51:44PM +0900, Soon-Son Kwon wrote:
> Hello: I am running pgsql on debian GNU/Linux woody box
> to run "sourceforge" program. (http://www.freesoftware.fsf.org/debian-sf/)
>
> Here is part of the /etc/postgresql/postgresql.conf:
>
> ---
> debug_level = 0
> log_connections = on
> log_pid = on
> log_timestamp = on
> syslog = 2
> # if syslog is 0, turn silent_mode off!
> silent_mode = off
> syslog_facility = LOCAL0
> trace_notify = off
> max_connections = 64
> # shared_buffers must be at least twice max_connections, and not less than 16
> shared_buffers = 128
> # TCP/IP access is allowed by default, but the default access given in
> # pg_hba.conf will permit it only from localhost, not other machines.
> tcpip_socket = 1
> ---
>
> But when I run "ps aux | grep sourceforge" to see if how many pgsql
> processes are running, the numver reaches the maximum connections very quickly
> even if only few users accesses it.
> Access to the pgsql(7.2.1) is done by php(4.1.2) and web server
> is apache(1.3.24).
This is a connection pool for the PHP/Apache webserver. Connecting
from php to the database via pg_connect() consumes a lot of time
(it opens in every php script a new connect), You can increase the
application speed using pg_pconnect().
You can adjust the number ox max persistent connects somewhere in the
php.ini
BTW: You can't shut down the database while these connects are open,
You should shut down Your webserver first (or at least execute
"apachectl graceful" to drop the persistent connections).

> The program uses "mysql_pconnect()" function but it seems something
> prevents reusing the existing pgsql socket connection, hence the pgsql always
> spawns a new process which then reaches the maximum connection possible.
IMHO mysql is another database - another party?!

> 1. Could anyone please give me a clue how to deal with this situation?
>
> Recalling my previous MySQL experience, I had similar problem but
> could cope with it by setting "wait_timeout" variable small which
> kills idle process if it has been idle for that specified time.
Try to edit the php.ini, but keep a look on the connects. If You
request a connect, but all are busy, You have to deal in Your
application (but this could happen anyway with hundreds of
connects on a busy site ;-)

> 2. Is there such configuration variable in pgsql?
I don't know a configuartion directive for postgres.
--
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.