Share via

ADO.NET The misunderstood “Connection Lifetime” managed pooler connection string keyword.

  • Article

I find that we have not done a good job of explaining the “knobs” used to tweak the managed connection pool (the one used by SqlClient and OracleClient), the worst knob definitely has to be the “Connection Lifetime” keyword. First of all, the name is bad. Really really bad. In Whidbey we are renaming it to Load Balance Timeout to help make it clearer, but we need to keep backward compatibility so Connection Lifetime will continue working. The second problem is that it does something really bad, it throws away connections that should have been pooled.

 

I have found that it is really hard to explain what this keyword does. Here is some pseudo code:

   On SqlConnection.Close

   Check if time the connection has been open is greater than Connection Lifetime if it is, throw the connection away

   Else Put connection on the pool

This is the _only_ time that we will check Connection Lifetime. We do not check the lifetime when the connection is idle in the pool and we certainly don't use this value to determine how long it can stay in there.

As you can see we are throwing away perfectly good connections, this will certainly affect performance since the next time we require a connection we will have to go to the server to get it, in effect you are telling us to turn pooling off after Connection Lifetime has been reached. This is a terrible idea for most applications and I would highly recommend not using this connection string keyword _unless_ you are connecting to a cluster _and_ you understand the trade offs.

This brings us to the design discussion, what exactly where we trying to solve when we came up with this knob? Let’s look at this scenario:

You have a cluster of servers with currently two servers up, we start running our middle tier applications against this server cluster and eventually we find that the load is too high, we add a third cluster and expect the load to be distributed. When we look at the load a few minutes later we see that the third server is sitting idle while the first two servers are overworked, what happened?

Pooling happened. The middle tier applications are caching the connections and never opening new ones. Connection Lifetime is the knob that allows this scenario to work, by throwing away perfectly good connections we will force the middle tier applications to create new connections, these new connections will be load balanced into the new server. It is up to the developer to fine tune how much performance they are willing to give up versus how important it is to load balance the application.

Summary: Do not use Connection Lifetime unless you are connected to a cluster and understand the trade offs.

Rambling out. Standard Disclaimer: This post is provided “AS IS” and confers no rights.

Comments

  • Anonymous
    September 20, 2004
    How easier would our lives be if there was a way to reset the pool. That way you would add another server to your cluster and then use an application request to reset the pool, which would simply mark all existing connections as "dirty", drop the idle ones and eventually (on close) drop connections that are in use.

    Standard Disclaimer: Even with this there will be users (calling themselves programmers) that will use it to shoot themselves in the foot (by calling this on each connection request/close) but they don't deserve any better anyways.
  • Anonymous
    September 20, 2004
    The comment has been removed
  • Anonymous
    September 20, 2004
    Are you guys going to offer those on the IDbConnection interface as well? I haven't used SqlConnection or OracleConnection in months, all my database work goes through interfaces (since the backend can be either SQL Server or Oracle).

    And once I'm complaining about missing features - having IDbCommand.CreateParameter that takes combination of IDataParameter properties would be nice as well, at least name and value would really help me out (even though name, value, type and possibly direction would be nice as well).
  • Anonymous
    September 20, 2004
    We could not modify IDbConnection even if we wanted to, it would be a breaking change. In this case we would not want to since this feature is specific to the SqlClient and Oracle managed providers. In whidbey we have added common classes take a look at Bob Beauchemin's excelent article http://msdn.microsoft.com/data/DataAccess/Whidbey/default.aspx?pull=/library/en-us/dnvs05/html/ado2featurematrix.asp#ado2featurematrix_topic1 (Warning, this is a beta release and the base class implementation is subject to change)

    Of course the Parameter support for this is still the weakest link as you have pointed out, and I don't really see it getting better in the whidbey release. Parameters are hard to deal with in a provider independent manner.
  • Anonymous
    September 20, 2004
    You're right, modifying an interface would break it. Maybe adding a new interface (IDbConnectionPool?) might work...

    As for the parameters - it's not really such a big deal, I just created few very simple helper functions, it would just be nice to have those in the framework.
  • Anonymous
    September 30, 2004
    Connection Timeout is also a parameter that confuses me - in a previous post (the Connection Pooling and the "Timeout expired" exception FAQ), you mention that the Timeout expired can happen only 2 ways - both of which involve the pool.

    Does this mean that the Connection Timeout only applies to a timeout waiting for a connection in the pool to become free - or isn't there a third way the exception can occur, which is if there is actually a timeout waiting for the real DB connection to the DB server to be fully created (perhaps because of a slow network, overloaded DB server, or whatever)? Did you leave that one out because its obvious, or would this case generate a differently worded exception?
  • Anonymous
    September 30, 2004
    Also, is there any truth to this, which I saw posted on http://forums.aspfree.com/archive/t-23744 ?

    "Our investigation revealed that the .NET SQLConnection Connection Pool is - at Connection Timeout (Default 4 hours) - doing something that seems to be statistical analysis - and during this analysis - no more connections can be retrieved from the pool. In our case we are using a connection from the pool 5 times a second. When the pool statistical analysis runs - it is analysing 5 * 60 * 60 * 4 (Hours) worth of statistical data. This process takes about 20 minutes. 20 minutes where the pool is unavailable !!! Solution - make the timeout about 30 seconds so that the analysis has a lot less data to analyse - and then the lag created by this analysis is one hellova lot shorter and has virtually no impact.

    i.e. Add ";Connection Lifetime=30" to your connection string. Timeout is now 30 seconds. "

  • Anonymous
    October 06, 2004
    Pete,
    Did I mention that the name Connection Lifetime was bad? yes a lot of people confuse it with connection timeout.

    Connection Timeout= min ammount of time we will retry to open new connection from server. Default = 15 seconds. Setting this also sets the Transaction timeout value incidentally.

    Connection Lifetime = how long before we throw away perfectly good connections that we are using.

    The "Timeout Expired the timeout period elapsed prior to obtaining a connection from the pool" exception is very client side specific, I would expect (but can be wrong) that an exception from the server to be more along the lines of "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." Until proven otherwise I am going to stand with the three reasons stated in my FAQ unless we are talking about a scenario with multiple thread aborts.

    I would be interested in seeing their investigation, it is very possible that they are running into a bug or are leaking connections themselves. Their "fix" is horrible. Any problem that they can mask by throwing away connections that are more than 30 seconds old is probably going to be pretty serious.
  • Anonymous
    October 10, 2004
    But there is no method called Connection Lifetime in ASO .net
  • Anonymous
    October 11, 2004
    Sriram,
    Connection Lifetime is a connection string keyword, as in you pass it in the connection string like this:
    SqlConnection("Data source=myserver;integrated security=sspi;Connection Lifetime=30")
  • Anonymous
    October 19, 2004
    I adjusted our "Connection String" settings and added the "Connection Lifetime=300" and was able to clear (lots of) records using a ColdFusion MX (XML / SOAP) request using CFHTTP and submitting the login string etc, with the added variable, and this did the trick.

    BTW - I had to look everywhere for the answer to this.

    Keywords: The timeout period elapsed prior to completion of the operation or the server is not responding. SOAP HEADER TIMEOUT Connection Timeout

    Thanks,

    Steve
  • Anonymous
    June 19, 2006
    Your article is prety nice. It's a pity that i didn't see it more later.
  • Anonymous
    May 31, 2008
    PingBack from http://laci.stocknewsdigest.info/netadonetseeingconnectionstring.html