www.webdeveloper.com
Results 1 to 8 of 8

Thread: Persistent database connections

  1. #1
    Join Date
    Sep 2006
    Location
    Copenhagen, Denmark
    Posts
    1,253

    Persistent database connections

    Hi guys

    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.

    From the PHP documentation I get

    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.
    Last edited by Dok; 12-23-2008 at 04:28 AM.
    #define question (2B || !2B)
    HTMLElement and W3C Event Handling in IE
    My JavaScript Library

    Don't PM me about answers to questions. If I don't reply in a thread it's because:
    • You didn't read the message posting guidelines
    • Your code is too unstructured and/or formatted poorly - correcting it is too time consuming
    • I simply don't know the answer

  2. #2
    Join Date
    Sep 2006
    Location
    Copenhagen, Denmark
    Posts
    1,253
    This should probably have been asked in the SQL forum...sorry about that. Could a mod please move the thread?
    #define question (2B || !2B)
    HTMLElement and W3C Event Handling in IE
    My JavaScript Library

    Don't PM me about answers to questions. If I don't reply in a thread it's because:
    • You didn't read the message posting guidelines
    • Your code is too unstructured and/or formatted poorly - correcting it is too time consuming
    • I simply don't know the answer

  3. #3
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.
    Last edited by chazzy; 12-23-2008 at 07:35 AM.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  4. #4
    Join Date
    Sep 2006
    Location
    Copenhagen, Denmark
    Posts
    1,253
    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.
    #define question (2B || !2B)
    HTMLElement and W3C Event Handling in IE
    My JavaScript Library

    Don't PM me about answers to questions. If I don't reply in a thread it's because:
    • You didn't read the message posting guidelines
    • Your code is too unstructured and/or formatted poorly - correcting it is too time consuming
    • I simply don't know the answer

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,912
    Start with the Persistant Database Connections section of the manual.
    "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

    eBookworm.us

  6. #6
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Read the first comment

    http://us2.php.net/function.pg-pconnect

    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

    Edit: Found this, on the postgres mailing lists: http://archives.postgresql.org/pgsql...8/msg01406.php
    Last edited by chazzy; 12-24-2008 at 12:04 PM.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  7. #7
    Join Date
    Sep 2006
    Location
    Copenhagen, Denmark
    Posts
    1,253

    resolved

    i'm not sure why you're defending pconnects
    Not defending...just confused.

    Excellent links - looks like I have some reading to do. Thanks for the help
    Last edited by Dok; 12-25-2008 at 06:24 AM.
    #define question (2B || !2B)
    HTMLElement and W3C Event Handling in IE
    My JavaScript Library

    Don't PM me about answers to questions. If I don't reply in a thread it's because:
    • You didn't read the message posting guidelines
    • Your code is too unstructured and/or formatted poorly - correcting it is too time consuming
    • I simply don't know the answer

  8. #8
    Join Date
    Feb 2006
    Posts
    198
    persistent connections in mod perl work quite well, if that option is available, and gives great performance

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles