SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Williamsport, PA
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy PDO/PostgreSQL hanging database connections

    My host emailed me this morning stating that one of my websites is leaving PostgreSQL connections open:

    We've found that an account under your reseller plan, user=*****, has been leaving an excessive number of PostgreSQL connections open and idle. We're talking in excess of 15-20 connections. Since each of these connections uses a fair amount of system resources (such as physical memory, RAM), this can cause quite a strain on the server and negatively affect other accounts (causing slowdowns, etc).
    They go on to threaten action against my account if I don't resolve the matter, so I'd like to get it resolved!

    In my code I have a single entry-point into the database:

    Code:
    $pdo = new PDO($config->db, $config->db_username, $config->db_password, array(PDO::ATTR_PERSISTENT => true));
    From what I understand PDO database connections are closed when your code exits, or when no more references to the PDO instance exist. There is no close() method. I'm using persistance, which I THOUGHT would be gentler on server resources, but I get the feeling this is why the connections are hanging.

    Does anybody know why this line of code would be causing so many unclosed connections to exist?

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2004
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    behavior seems correct

    i'm trying to connect to postgresql myself today.

    anyways by definition
    http://www.google.com/search?hl=en&h...stence&spell=1

    persistence means end to end connection until session is terminated.
    example http is a non persistence technology. user requests a page, apache serves the page, connection/user is forgotten about.


    desktop applications are persistence applications... as it can respond to events at all times.. well until the user closes the application.


    i'm not sure why someone would want a persistent connection.. would like to know.. but my guess would be if you have a series of queries your about to run.. example long mail list in a cron job or processing a long transaction.

    ---- edit
    also try and make your handle = null;

    i'm sure php devs made__destruct to close the db connection if not already closed.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Williamsport, PA
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From what I understand persistant database connections are used in PHP to get around the fact HTTP is a stateless protocol. Generally one would initialize a database connection, make some queries, and then that connection will die when the session terminates. For a busy site you would incur some overhead simply reconnecting and disconnecting from the database on every page request.

    Therefore PHP gives us persistant connections. Rather than die when the request ends, the database connection remains open. When a connection is requested for a database with an open connection, that connection is used instead of making a new one. This helps PHP programmers get around the fact that a Java construct like a "connection pool" can't really be implemented in PHP.

    I wish someone with experience using persistant connections would read this and respond so we both get cleared up on the subject

    Given what I said above, one would that that, if there is an open connection, a new one would NOT be opened, and that's where my code went wrong. When I removed the persistance option from my PDO instantiation code, the problem my host complained about went away. I would like some sort of explaination as to why so many connections were hanging!

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Williamsport, PA
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I just answered my own question... apparently this is a PostgreSQL issue...

    >>> Do the "persistent-connected" Postgres backends ever timeout or die?
    >> No. A backend will sit patiently for the client to send it another
    >> query or close the connection.

    >This does have an unfortunate denial-of-service implication, where
    >an attack can effectively suck up all available backends, and there's
    >no throttle, no timeout, no way of automatically dropping these....

    Well, if you are talking about an environment where you don't trust
    your users and they can badly effect each other then you almost
    certainly don't want to be using persistant connections with PHP/Apache.
    You are probably running multiple databases/logins which would make
    it inefficient anyway.

    >However, the more likely possibility is similar to the problem that
    >we see in PHP's persistant connections.... a normally benign connection
    >is inactive, and yet it isn't dropped. If you have two of these created
    >every day, and you only have 16 backends, after 8 days you have a
    lockout.

    To some extent, this could be combatted with the Apache
    MaxRequestsPerChild directive. But in reality I don't see the
    problem here. Persistant connections do exactly what the name
    suggests. If you don't trust your application logic to be right,
    or don't trust your users, then don't use (or allow use) of
    persistant connections.


Bookmarks

Posting Permissions

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