PostgreSQL Administration - Postgresql, number of connections and life time

Author

Postgresql, number of connections and life time

Juan Miguel

2006-01-28, 7:24 am

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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane

2006-01-28, 11:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Milen A. Radev

2006-01-28, 11:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links