Thread: Postgresql, number of connections and life time
Hello, First of all sorry about my bad command of english. I have two questions about postgres. First. I have a Linux box with postgres 7.4 branche, where web php applications queries the DB. When Apache launches a php file that connects to the DB, if I do "ps aux | grep postgrest", I can see a process "postgres user ...." running, that represents the session openned to the DB. But when the php program finishes, and closes the connection, this process does not end. Therefore the number of processes are growing while people visit the web. ¿ Why ? I see the php code, and there is a closing command before end. I see that sometimes I visit a page that connects to the DB and no new process is performed or remains. I also realise that after several hours (for example at midnight) those processes don't exist. What is happening ? Is it usual ? Is there a param to control the number of process launched and their life times? In PHP I don't use a pool of connections because, the problem is the same (there are several processes, and remain after connection closes). Second. I gain access this DB remotely by an encrypted connection with stunnel. Well, when I launch a connection to a DB, and passes a long time without traffic, I realise the connection is closed. The program that queries the DB does not realise the connection is broken and when it tries to query, it fails. I think that the origin of the problem is not stunnel, because the same problem presents to me when trying with a testing DB without stunnel. ¿ Why ? Has postgres a timer that breaks the connections when there is no traffic ? Has Linux this timmer ? Is the carrying data enterprise who closes the connection ? Is this a TCP/IP stack protocol question ? Thanks you very much.
Juan Miguel <juanmime@ono.com> writes: > I have a Linux box with postgres 7.4 branche, where web php applications > queries the DB. When Apache launches a php file that connects to the DB, > if I do "ps aux | grep postgrest", I can see a process "postgres user > ...." running, that represents the session openned to the DB. But when > the php program finishes, and closes the connection, this process does > not end. Therefore the number of processes are growing while people > visit the web. � Why ? Evidently the connection isn't really getting closed. I don't know enough about PHP to help you with why not, though. > I gain access this DB remotely by an encrypted connection with stunnel. > Well, when I launch a connection to a DB, and passes a long time without > traffic, I realise the connection is closed. The program that queries > the DB does not realise the connection is broken and when it tries to > query, it fails. I think that the origin of the problem is not stunnel, > because the same problem presents to me when trying with a testing DB > without stunnel. � Why ? Has postgres a timer that breaks the > connections when there is no traffic ? Has Linux this timmer ? No, and no, but a lot of router and firewall hardware has idle-timeouts for connections. Look at the settings for whatever boxes are between your two machines --- if you're lucky, you can increase the idle timeout. More recent versions of PG (I think 8.1 and up) allow you to fool with the TCP keepalive settings --- reducing those can be used to generate dummy traffic that will keep the firewall from thinking the connection is idle. regards, tom lane
Juan Miguel writes: [...] > First. > I have a Linux box with postgres 7.4 branche, where web php applications > queries the DB. When Apache launches a php file that connects to the DB, > if I do "ps aux | grep postgrest", I can see a process "postgres user > ...." running, that represents the session openned to the DB. But when > the php program finishes, and closes the connection, this process does > not end. Therefore the number of processes are growing while people > visit the web. ¿ Why ? I see the php code, and there is a closing > command before end. > I see that sometimes I visit a page that connects to the DB and no new > process is performed or remains. I also realise that after several hours > (for example at midnight) those processes don't exist. > What is happening ? Is it usual ? Is there a param to control the number > of process launched and their life times? > In PHP I don't use a pool of connections because, the problem is the > same (there are several processes, and remain after connection closes). [...] May be the PHP application uses persistent connections (http://www.php.net/manual/en/function.pg-pconnect.php)? -- Milen A. Radev