I would like some help figuring out when persistent connections are closed. On my server I can see a lot of inactive database conenctions. This is always the case even though I am certain no users are accessing the database.
I take it these are connections are initially created as persistent conenctions which are then never closed.
I am running Apache 2 with default configuration and PostGreSQL 8.3 also in a default configuration on Ubuntu 8.04 LTS.
When a request comes in from a client, it is handed off to one of the children that is not already serving another client. This means that when the same client makes a second request to the server, it may be served by a different child process than the first time. When opening a persistent connection, every following page requesting SQL services can reuse the same established connection to the SQL server.
Later on in the user contributed comments one user is writing
You are probably using a multi-process web server such as Apache. Since
database connections cannot be shared among different processes a new
one is created if the request happen to come to a different web server
I am running multiple databases so the conenction strings for different child processes will not be the same and hence new connections will be spawned from time to time.
So if this is true then how and when are persistent connections closed? I tried searching for a connection timeout setting in PostGreSQL but the only answers where concerned with authentication timeout.
So if PHP or PostGreSQL can't handle closing of persistent conenctions can I write a script somehow and run it as a cron job? I have some experience in using PostGreSQL along with its C library but I don't relly know where to start.
why should this be in the sql forum? it's a php question.
the use of persistent connections in php should be avoided when possible. i just ran into a similar issue at work (php + oci) where after about 30 or so hits, pages would become unresponsive. the previous developers used pconnects hoping that it would return an already active connection but the short of it is that php never really understands when a connection is done.
your best, and safest bet, in php, is to just create a new connection as needed.
Edit: Just to be clear (and make sure I answer your exact question) Postgres persistent db connections never close. They'll either timeout on the db server end, go missing due to child threads disappearing, or become completely unusable while still being open on the db side.
the fact of the matter is that people treat pconnects in php like they're pooled connected when in fact they're not. i'm not sure if it'll help you, but maybe you should search up a bit on pooling database connections in apache/php. i just took a look and briefly skimmed a few things, there are some suggestions that have some promise.
i'm not sure why you're defending pconnects, given that the issues you're describing are a direct result of using pconnects. the overhead of creating/terminating connections in a single script is not really that big in the scheme of what php actually is.
since you're using postgres, you may want to consider looking into pgpool