Обсуждение: too many idle processes
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). 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. 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. 2. Is there such configuration variable in pgsql? Thanks very much.... -- -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* (o_ **WTFM** (o_ (o_ //\ (/)_ (/)_ V_/_ http://kldp.org -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
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.
On Tue, 21 May 2002, 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 This setting here: > max_connections = 64 tells postgresql how many connections it can accept max. In Apache's httpd.conf file, the line MaxClients 90 says how many clients the web server can accept connections from. This is the maximum number of "child processes" apache will spawn. In php.ini we find the lines: pgsql.allow_persistent = On pgsql.max_persistent = 2 #max persistant links per process pgsql.max_links = 16 # max pers/non-pers links per process We need to make sure that PHP/Apache don't try to open more than postgresql is configured for. You probably should increase the max_connections setting in postgresql.conf first, to at least 128 or 256. then, you need to set MaxClients * pgsql.max_persistant to be < max_connections. Be sure and leave one or two spare connects for you to be able to get via a psql command line. note that if you are running >1 web server against one database server, you then have to have num_web_servers * MaxClients * pgsql.max_persistant < max_connections. This is a common problem with persistant connections, and the method for setting up a web server to handle persistant connects with php/apache is non-obvious. but it does work.
Thanks very much.
According to your description, my configuration
was broken.... My comments below....
--[snip]--
> > trace_notify = off
>
> This setting here:
>
> > max_connections = 64
>
> tells postgresql how many connections it can accept max.
>
> In Apache's httpd.conf file, the line
>
> MaxClients 90
For me, this was 512 because I just thought that higher value
can serve more clients.
> says how many clients the web server can accept connections from. This is
> the maximum number of "child processes" apache will spawn.
>
> In php.ini we find the lines:
>
> pgsql.allow_persistent = On
> pgsql.max_persistent = 2 #max persistant links per process
> pgsql.max_links = 16 # max pers/non-pers links per process
For me, the old values were
pgsql.max_persistent = 64
I thought this needs to be the same as /etc/postgresql/postgresql.conf
> We need to make sure that PHP/Apache don't try to open more than
> postgresql is configured for.
>
> You probably should increase the max_connections setting in
> postgresql.conf first, to at least 128 or 256.
>
> then, you need to set MaxClients * pgsql.max_persistant to be <
> max_connections. Be sure and leave one or two spare connects for you to
> be able to get via a psql command line.
>
> note that if you are running >1 web server against one database server,
> you then have to have num_web_servers * MaxClients * pgsql.max_persistant
> < max_connections.
I changed all the values to fit your explanation and waiting for
user feedback for any error...
Thanks again for your kind information...
--
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
(o_ **WTFM**
(o_ (o_ //\
(/)_ (/)_ V_/_ http://kldp.org
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*