connection pooling

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

if I have an asp.net application where the requirement is that it should
handle a peak of 5000 simultaneous user connections, how should I configure
the connection pool? Should I create the connection pool to have a size of
5000 out of the gate? Is there a way to tune the connection pool
programmatically at run time?

What is the resource impact if I set the pool size to 5000?

thanks in advance.
 
The only definitive answer you will find will be through testing. Use a
web stress/performance tool (Application Center Test may get you
started, but there are more sophisticated tools) that allows you to
simulate 5000 simultaneous connetions, and test with various settings of
the connection pool, while monitoring performance counters to see
resource usage.
 
The only definitive answer you will find will be through testing. Use a
web stress/performance tool (Application Center Test may get you
started, but there are more sophisticated tools) that allows you to
simulate 5000 simultaneous connetions, and test with various settings of
the connection pool, while monitoring performance counters to see
resource usage.
Click to expand...

Yes, we have developed in-house tools to stress the application. We are
running out of connections in the pool. We could tune to accomodate the 5000
connection goal, but what would that cost us in that 80% of the time when we
only have 10 connections at a time? I am talking about impact on the insides
of ado.net and other things that are not easy to monitor (internal table
lookups, management overhead, transient memory usage, etc)

Would it make more sense to do without connection pooling for this type of
load?
 
A web server has a finite capacity. That is, when a page is loaded it opens as many connections as it needs at the same time. Due to the single-threaded nature of these application (pages), a single connection is usually enough. Generally, the page opens the connection just in time and closes it as soon as it's no longer needed. This assumes that the connectionstring does not change for each page. Using this approach, the connection pool reuses the connection for each instance of the page as needed--it's rarely closed and released by the pooling mechanism.
If you're finding that the number of connections in the pool (and I assume you have a single pool--although there could be more than one), is growing over time, there is something wrong.
a.. You're doing more processing in the instance than the server can handle before another instance of the page is loaded with an additional request.
b.. You're not closing the connections so when your instance ends, the connection remains open in the pool and unavailable for other instances.
c.. You're not committing or rolling back the transaction used by the connection which has the same result--the pool overflows.
d.. You have TSQL debugging enabled. This is an issue that causes the pools to overflow.
The first two issues are fairly common. The first can be solved by tuning the application to be more efficient. This might mean fetching fewer rows, improving the performance of the stored procedures, adding or removing indexes, or simply beefing up the hardware and RAM. The second is typically caused by sloppy connection management--especially when you're using DataReaders. If you don't make sure the Connection associated with the DataReader is closed, you're pooched.

We've seen a lot of heavily used web sites use 20-50 connections with a powerful hardware system. This single system can handle several hundred users with ease if the code is written efficiently. Once you exceed the capacity of the hardware, things fall apart quite rapidly.

We've discussed this many times before and I've written several whitepapers over the years on this subject. See www.betav.com for this content.
hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi Bill,

Thanks for the explanation, I think it makes a lot of sense. I just have two
general questions:

1. When (if ever) would you consider turning connection pooling off?

2. Would it help in some cases if the application where to be architected to
use multiple connection pools, each pool dedicated to a process? For example,
would it make sense to dedicate a pool to a critical but low volume process,
so it won't be affected by a high-volume/less critical process overloading
its connection pool?
 
William said:
* You have TSQL debugging enabled. This is an issue that causes
the pools to overflow.
Click to expand...

Could you expand on this? I just had TSQL debugging turned on, though I
am not seeing any problems as of yet.

Thanks
 
1) In a Windows Forms application connection pooling is not nearly as
interesting (or useful). In an ASP application, it's pretty much essential.
There are few situations that call for manual connection management.

2) Yes, it's often suggested that you build a separate pool for each
application function that conflicts or competes with other functions. So the
answer to "2" is yes...


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
We've seen some threads that discuss a problem that can cause the pool to
overfill when TSQL debugging is enabled.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Just wanted to mention that connection pooling can be just as useful with
Windows forms apps. Large enterprises using Windows forms clients and remote
server components can also benefit. It's not the type of applicaiton, it's
the architecture that determines whether you need connection pooling.
 
Of course. If the Windows Forms application is using JIT connection
approach, a connection pool might be interesting, but unless you need to run
(many) connections on separate threads it's not nearly as important as for
ASP applications. I think it's probably better for applications that don't
have to scale past a 500 or so users to simply open the connection and hold
it for the life of the application. Sure if you expect to post update
traffic while the inbound connection is open you might want to open another
connection--but then I would want to see what would lead you to that
approach in the first place. Perhaps it would be better to do server-side
processing instead...
Yup... it's the architecture.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
If the Windows Forms application is just the presentation tier (meaning you
have a stateless middle-tier serving many clients) then it could also
benefit from conenction pooling. I think in your statement about Windows
Forms you are assuming that the client application is making the connection
directly to the database like with a traditional client/sever architecture.
I just wanted to point out that the use of connection pooling has nothing to
do with whether it is an ASP app or not, it has everything to do with your
*server* architecture. Yes this usually means IIS hosted server applications
like ASP/ASP.NET, but it could also be a server architecture using
Enterprise Services, DCOM or .NET Remoting.
 
Sure. When I'm talking about Windows forms applications opening a
connection, I'm talking about opening connections to a database. Sure if the
application is making use of a web service, DCOM or Remoting, then the
connection is not opened by the application at all--but by the remote data
provider. Then, connection pooling is interesting. What hairs are you trying
to split?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top