Connections are pooled but not reused

[Guest]

I have a distributed application that uses ADO.Net and it seems that connections aren't reused from the pool. For every connection I open I can see (using performance monitor) how the pool gets bigger. It doesn't matter if I am retrieving data or manipulating data

Does the pool grow until max pool size is reached before reusing connections

Regard
Johan Johansson

 

[Miha Markic [MVP C#]]

Hi Johan,

Huh, not. What provider are you using?
Is your connection string always the same?
What is it?
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I have a distributed application that uses ADO.Net and it seems that

connections aren't reused from the pool. For every connection I open I can
see (using performance monitor) how the pool gets bigger. It doesn't matter
if I am retrieving data or manipulating data.

 

[Guest]

I use the SQL Client provider and the SQL-class-library

My connectionsstring is indeed always the same. =

"Data source=localhost;Initial Catalogue=MyDB;UID=SomeValue;PWD=SomeOtherValue;connection reset=false;connection lifetime=5;min pool size=1;max pool size=500;

Regard
Johan Johansson

 

[Patrice]

You used 5 for the lifetime which is likely too small to reuse connections.
Try with the default value (ie. not having this in the connection string) to
confirm.

(IMO it's most often better to leave the defautl unless you want to meet
something particular).

Patrice

I use the SQL Client provider and the SQL-class-library.

My connectionsstring is indeed always the same. =

"Data source=localhost;Initial

Catalogue=MyDB;UID=SomeValue;PWD=SomeOtherValue;connection
reset=false;connection lifetime=5;min pool size=1;max pool size=500;"

 

[Angel Saenz-Badillos[MS]]

(IMO it's most often better to leave the default unless you want to meet

something particular).

This is the best advice on pooling I have seen <g> seriously unless you
absolutely have to _and_ know what you are doing you will get better
performance and scalability by using the defaults for the SqlClient and
Oracle Managed providers.

Connection Lifetime of five is a terrible choice even if you had to load
balance clusters, worse than not pooling at all imo. You are basically
telling us to throw away the connections after using them for five seconds.
Min Pool Size should almost never be used, here you are telling us to open a
connection for you under the covers when we go below 1. It is almost always
better to not pool connections and leave them always open than to use Min
pool size.

This scenario for a connection that is not enlisted in a distributed
transaction is going to look something like:
open connection, use for six seconds (on multiple uses)
on return to pool, check connection lifetime >5 do not return connection to
pool.
Min pool size not met, open connection,
no need for connection, stays in pool for 6 seconds
open connection, use and return to pool
on return to pool check connection lifetime >5, do not return connection to
pool
Min pool size not met, open connection.

etc
You have basically coded yourself out of pooling.

The reason I specified "for connections not enlisted in distributed
transaction" is that the behavior changes slightly for enlisted connections.
There is a subpool for these connections since we cannot throw them away
until the DTC is committed or rolled back. Basically they get moved into
this subpool and not reused unless it is under the same DTC until we get a
message from the resource manager and this changes the above behavior
slightly. I am very interested in seeing any problems related to using these
subpools so please let me know if you have any issues after fixing the
connection string.

Thank you,

 

[Damien Foggon]

I have a distributed application that uses ADO.Net and it seems that connections aren't reused from the pool. For every connection I open I can see (using performance monitor) how the pool gets bigger. It doesn't matter if I am retrieving data or manipulating data.

Does the pool grow until max pool size is reached before reusing connections?

Regards
Johan Johansson

Just a thought but you are closing the connections correctly after
you're finished with them?

I had this when using ExecuteReader() to return a SqlDataReader and the
problem was unless you specify CommandBehavior.CloseConnection the
connection to the database is never unused so the cound will just go up
and up until you get a "no connections available" error.

 

[Guest]

Yes I do close each connection when I am finished with it.

 

[Guest]

I didn't have these extra statement in my connection string at first. At that time the application stopped running after a short while; complaining that it couldn't open another connection since the pool was closed. It was not until after I added these statement that I got my application to run indefinitely

You see, this is about the connections in the pool not being reused. While it is running it will start new connections _every_ time. Slowly it will increase the pool to about 800 connections and there it will level off, ranging from about 600 to 900 connections in the pool. It seems to me that connections in the pool is terminated at about the same rate that new ones are added

I make a new connection every 5 seconds

Angel wrote
"The reason I specified "for connections not enlisted in distributed transaction" is that the behavior changes slightly for enlisted connections. There is a subpool for these connections since we cannot throw them away until the DTC is committed or rolled back. Basically they get moved into this subpool and not reused unless it is under the same DTC until we get a message from the resource manager and this changes the above behavior slightly.

I do use transactions while manipulating data, though not when retrieving it.

 

[Patrice]

What was the exact error message ? Is is that the pool is "closed" or "full"
? It looks like you are not releasing connection...

Patrice

I didn't have these extra statement in my connection string at first. At

that time the application stopped running after a short while; complaining
that it couldn't open another connection since the pool was closed. It was
not until after I added these statement that I got my application to run
indefinitely.

You see, this is about the connections in the pool not being reused.

While it is running it will start new connections _every_ time. Slowly it
will increase the pool to about 800 connections and there it will level off,
ranging from about 600 to 900 connections in the pool. It seems to me that
connections in the pool is terminated at about the same rate that new ones
are added.

I make a new connection every 5 seconds.

Angel wrote:
"The reason I specified "for connections not enlisted in distributed

transaction" is that the behavior changes slightly for enlisted connections.
There is a subpool for these connections since we cannot throw them away
until the DTC is committed or rolled back. Basically they get moved into
this subpool and not reused unless it is under the same DTC until we get a
message from the resource manager and this changes the above behavior
slightly."

I do use transactions while manipulating data, though not when retrieving

it.

 

[Guest]

Sorry, my mistake; should be "Full", not "Closed

Is there a distinction between Closing and Releasing a connection? I do close them all.

Should I implement the IDisposable interface? Would that really be appropriate in the Data Access Layer when doing transactions (ServicedComponent) further up in the Business Layer? What if I dispose the database and later on need to rollback


----- Patrice wrote: ----

What was the exact error message ? Is is that the pool is "closed" or "full
? It looks like you are not releasing connection..

Patric

 

[Angel Saenz-Badillos[MS]]

I agree with Patrice, it sounds like you are not closing or disposing the
connection. The behavior you are describing is consistent with connections
going out of scope in the open state and causing load on the GC until you
run out of Max Pool Size. The default max pool size of 100 is around 10x
what most heavy load applications should need and hitting this is almost
always a sign of a leak.

The most common problem is code like this:
open connection
execute //may or may not throw exception
close connection

In this code every time that execute throws an exception _for whatever
reason_ the connection will not close since the throw exits the current
scope. To fix this problem you can call close or dispose on a finalizer or
if you are coding in c# use the equivalent "using" construct.

try
open connection
execute //may or may not throw exception
finally
close connection
--------------------------
using connection {
execute
}// here we guarantee calling connection.Dispose()

Either of these approaches will guarantee that close or dispose is called on
the connection and plugs the leak.

If you are not running into this issue I would like to get more information
from you to set up a local repro, I am really interested in finding any
issues with pools of connections in distributed transactions.
Thank you,

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

Sorry, my mistake; should be "Full", not "Closed"

Is there a distinction between Closing and Releasing a connection? I do close them all.

Should I implement the IDisposable interface? Would that really be

appropriate in the Data Access Layer when doing transactions
(ServicedComponent) further up in the Business Layer? What if I dispose the
database and later on need to rollback?

 

[Angel Saenz-Badillos[MS]]

Major typo!! Please replace:

call close or dispose on a finalizer with
call close or dispose on a finally block

Do not ever for any reason call connection close on a finalizer. This is
guaranteed to cause stress issues.
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

I agree with Patrice, it sounds like you are not closing or disposing the
connection. The behavior you are describing is consistent with connections
going out of scope in the open state and causing load on the GC until you
run out of Max Pool Size. The default max pool size of 100 is around 10x
what most heavy load applications should need and hitting this is almost
always a sign of a leak.

The most common problem is code like this:
open connection
execute //may or may not throw exception
close connection

In this code every time that execute throws an exception _for whatever
reason_ the connection will not close since the throw exits the current
scope. To fix this problem you can call close or dispose on a finalizer or
if you are coding in c# use the equivalent "using" construct.

try
open connection
execute //may or may not throw exception
finally
close connection
--------------------------
using connection {
execute
}// here we guarantee calling connection.Dispose()

Either of these approaches will guarantee that close or dispose is called on
the connection and plugs the leak.

If you are not running into this issue I would like to get more information
from you to set up a local repro, I am really interested in finding any
issues with pools of connections in distributed transactions.
Thank you,

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

Sorry, my mistake; should be "Full", not "Closed"

Is there a distinction between Closing and Releasing a connection? I do close them all.

Should I implement the IDisposable interface? Would that really be

appropriate in the Data Access Layer when doing transactions
(ServicedComponent) further up in the Business Layer? What if I dispose the
database and later on need to rollback?

----- Patrice wrote: -----

What was the exact error message ? Is is that the pool is "closed"

or

"full"
? It looks like you are not releasing connection...

Patrice

 

[Guest]

I found the error; quite horrible actually

I have this Disconnet -method in my database factory, it's supposed to close the connection. The first two rows were
if ( m_Connection.State == ConnectionState.Open
return

Sigh..

Anyway. I am additionally disposing my connections in the database factory destructor and disposing my ServicedComponent-s when done with them

Thanks for your tips..!!

 

[Angel Saenz-Badillos[MS]]

Sorry about that, major typo on my previous email, sent a correction as soon
as I saw my post, but just in case you missed it:

Do not close or dispose (or touch) the connection (or any other managed
object) in your finalizer/destructor. I can almost guarantee that this is
what is causing the bulk of your problems.

I added a warning to connection close:
http://msdn.microsoft.com/library/d...datasqlclientsqlconnectionclassclosetopic.asp

CAUTION Do not call Close or Dispose on a Connection, a DataReader, or any
other managed object in the Finalize method of your class. In a finalizer,
you should only release unmanaged resources that your class owns directly.
If your class does not own any unmanaged resources, do not include a
Finalize method in your class definition. For more information, see
Programming for Garbage Collection.

Does this help?

Another very common problem with database factories is using ado.net objects
that are not thread safe in multiple threads.
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

I found the error; quite horrible actually.

I have this Disconnet -method in my database factory, it's supposed to

close the connection. The first two rows were:

if ( m_Connection.State == ConnectionState.Open )
return;

Sigh...

Anyway. I am additionally disposing my connections in the database factory

destructor and disposing my ServicedComponent-s when done with them.