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
child process.
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.
Thanks for answering...I thought it was a PostGreSQL issue related to timeout.
the use of persistent connections in php should be avoided when possible
This is contrary to all other advise I have seen. Could you elaborate a little please? The PHP docs clearly states the advantages of using persistent connections and I have always used them.
your best, and safest bet, in php, is to just create a new connection as needed.
This would be one new connection for each HTTP request...thats a lot of overhead. Can you point me to some documentation which says that this is best practice?
Postgres persistent db connections never close.
Surely it must be possible to force a connection to close. It might not be part of the PHP API but the C library have functions for this...I'm just not sure where to look for timing issues.
If someone can point me to some in-depth documentation explaing persistent connections from PHP's point of view I'd appreciate it.
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
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
Reply With Quote
Bookmarks