打开一个数据库连接是一个占用大量资源和耗费时间的操作。 连接池通过复用激活的数据库连接来提高Web程序的性能，而不是对每个请求都创建一个新的数据库连接。
连接池管理器维持放置在池内的打开的数据库连接。当一个新的连接请求发过来时，池管理器检查连接池是否含有任意的未使用的连接，如果有就返回该连接。 如果当前池内所有的连接都处在繁忙状态，但连接池的最大容量未达到的话就创建一个新的连接加入连接池。 当池内连接达到容量时，所有新的连接请求会排队等候，直到池里有可用连接或者连接超时。
Connection pooling behavior is controlled by the connection string parameters. The following are four parameters that control most of the connection pooling behavior:
Connect Timeout - controls the wait period in seconds when a new connection is requested, if this timeout expires, an exception will be thrown. Default is 15 seconds. Max Pool Size - specifies the maximum size of your connection pool. Default is 100. Most Web sites do not use more than 40 connections under the heaviest load but it depends on how long your database operations take to complete. Min Pool Size - initial number of connections that will be added to the pool upon its creation. Default is zero; however, you may chose to set this to a small number such as 5 if your application needs consistent response times even after it was idle for hours. In this case the first user requests won't have to wait for those database connections to establish. Pooling - controls if your connection pooling on or off. Default as you may've guessed is true. Read on to see when you may use Pooling=false setting.
连接共用的行为由连接字符串控制。下列四个参数控制了大部分的连接共用行为。 Connect Timeout -控制当新数据库连接被请求时的连接等待时间（秒）。如果连接超时则抛出一个异常。默认15秒。 Max Pool Size-指定你的连接池包含的最大连接数。默认100. 在高负载情况下大部分网站使用连接数不超过40个，但也取决于你的数据库操作要花多长时间去完成。 Pooling-控制你的连接池开关。默认如你所想是打开的。 接着往下读，看什么时候你可以关闭连接池。
Common Problems and Resolutions
Connection pooling problems are almost always caused by a "connection leak" - a condition where your application does not close its database connections correctly and consistently. When you "leak" connections, they remain open until the garbage collector (GC) closes them for you by calling their Dispose method. Unlike old ADO, ADO.NET requires you to manually close your database connections as soon as you're done with them. If you think of relying on connection objects to go out of scope, think again. It may take hours until GC collects them. In the mean time your app may be dead in the water, greeting your users or support personnel with something like this:
Exception: System.InvalidOperationException Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Source: System.Data at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() ...
Exception: System.InvalidOperationException Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Source: System.Data
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open()
常见问题和解决方案
连接公用的问题通常由“连接溢出”导致：你的应用程序没有正确且一贯的关闭它的数据库连接。当你“溢出”连接时，它们仍然保持打开，直到通过调用它们的Dispose方法后，GC才会为你关闭它们。 不像老的ADO，ADO.NET需要你使用完这些链接后手工的关闭它们。如果你考虑依靠连接对象超出（对象有效）范围，再想想，这样会花几个小时直到GC回收它们。 同时你的应用程序可能会“死在水里”，然后用像下面的东西一样去欢迎你的用户或支持你的人事部门： Exception: System.InvalidOperationException Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Source: System.Data at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() ...
Closing your connections
When you intend to close your database connection, you want to make sure that you are really closing it. The following code looks fine yet causes a connection leak:
SqlConnection conn = new SqlConnection(myConnectionString);
conn.Open();
doSomething();
conn.Close();
If doSomething() throws an exception - conn will never get explicitly closed. Here is how this can be corrected:
SqlConnection conn = new SqlConnection(myConnectionString);
try
{
conn.Open();
doSomething(conn);
}
finally
{
conn.Close();
}
or
using (SqlConnection conn = new SqlConnection(myConnectionString))
{
conn.Open();
doSomething(conn);
}
Did you notice that in the first example we called conn.Close() explicitly while in the second one we make the compiler generate an (implicit) call to conn.Dispose() immediately following the using block? The C# using block guarantees that the Dispose method is called on the subject of the using clause immediately after the block ends. Close and Dispose methods of Connection object are equivalent. Neither one gives you any specific advantages over the other.
When returning a connection from a class method - make sure you cache it locally and call its Close method. The following code will leak a connection:
OleDbCommand cmd new OleDbCommand(myUpdateQuery, getConnection());
intres = cmd.ExecuteNonQuery();
getConnection().Close(); // The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.
If you use SqlDataReader, OleDbDataReader, etc., close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them.
Last but not the least, never Close or Dispose your connection or any other managed object in the class destructor or your Finalize method. This not only has no value in closing your connections but also interferes with the garbage collector and may cause errors. For more information see http://msdn.microsoft.com/library/en-us/cpguide/html/cpconprogrammingessentialsforgarbagecollection.asp
关闭你的连接 当你想关闭你的数据库连接时，你想要确认你确实关闭了它，那么下面的代码看起来很好却仍然会产生一个连接溢出： SqlConnection conn = new SqlConnection(myConnectionString);
conn.Open();
doSomething();
conn.Close(); 下面的是解决方案： SqlConnection conn = new SqlConnection(myConnectionString);
try
{
conn.Open();
doSomething(conn);
}
finally
{
conn.Close();
}
or
using (SqlConnection conn = new SqlConnection(myConnectionString))
{
conn.Open();
doSomething(conn);
}
Connection的Close和Dispose方法是相等的。任何一个都不会给你超过另一个的特定的优势。
当从类方法里返回一个连接时，确保你在本地缓存了它，并且调用了关闭方法。下面的代码会溢出一个连接：
OleDbCommand cmd new OleDbCommand(myUpdateQuery, getConnection());
intres = cmd.ExecuteNonQuery();
getConnection().Close(); // The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.
如果你用SqlDataReader, OleDbDataReader, etc., 关闭它们。甚至即使连接自身会去做把戏，多花点精力去关闭你的data reader 对象当你用它们的时候。
最后但不是最重要的，永远不要在类的析构函数或你的Finalize方法里Close或Dispose你的连接。这不仅没有价值，并且妨碍了GC并且可能导致错误
Testing your changes
The only way to know the effect of your changes on connection pooling behavior is to load-test your application. If you have existing unit tests - use them. Running your unit tests repeatedly in a loop may create a fair bit of stress on application. If you don't, use the Web load testing tool. There are plenty of commercial load testing tools on the market. If you prefer freeware, consider OpenSTA available at www.opensta.org. All you need to setup your load test is to install the tool, bring up your Web application and click your way through. OpenSTA will record your HTTP requests into test scenarios that you can run as part of your load test.
Knowing that your application crashes under the load doesn't often help to locate the problem. If the app crashes fairly quickly, all you may need to do is run several load tests - one for each module and see which one has a problem. However, if it takes hours to crash you will have to take a closer look.
测试更改 知道你在连接共享行为上的更改效果的方法只有一个：load-test 负载测试你的应用程序。 如果你有一个现有的单元测试，使用它们。在一个循环重复运行你的单元测试，这样可能在程序上产生公平点的压力 a fair bit of stress。如果没有单元测试，用Web负载测试工具。 在市场上有大量的商业负载测试工具，如果你偏爱免费工具，考虑 OpenSTA（可以在www.opensta.org得到）。你需要的设置时安装测试工具，打开你的Web程序，点击你的方法（click your way through）。 OpenSTA将会在测试脚本中记录你的HTTP请求，你可以当做你的负载测试的一部分去运行。
知道你的程序在负载下崩溃并不太能帮助你定位问题。如果程序相当快的崩溃，你所需要的做的是运行几个复制测试，每个模块一个测试，看哪个测试有问题。然而你必须紧盯着，测试会耗上几个小时。
Monitoring connection pooling behavior
Most of the time you just need to know if your application manages to stay within the size of its connection pool. If the load doesn't change, but the number of connections constantly creep even after the initial "warm-up" period, you are most likely dealing with a connection leak. The easiest way to monitor the number of database connections is by using the Performance Monitor available under Administrative tools on most Windows installations. If you are running SQL Server, add SQL Server General Statistics -> User Connections performance counter (The counter is available on the SQL Server machine so you may need to put its name or IP address into the Select Counters From Computer box). The other way to monitor the number of database connections is by querying your DBMS. For example, on SQL Server run:
EXEC SP_WHO
Or on Oracle, run:
SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL
.NET CLR Data performance counters
In documentation you may run into .Net CLR Data performance counters. They are great if you know what they can and cannot do. Keep in mind that they do not always reset properly. The following KB article sheds some light on the problem but in my opinion does not cover all the issues: http://support.microsoft.com/default.aspx?scid=kb;en-us;314429. Another thing to keep in mind is that IIS unloads app domains under stress so don't be surprised when your number of database connections has dropped to zero while your min pool size is five!
监控连接池行为 大多数时间你只需知道你的程序数据库连接请求是否保持在连接池最大连接数。 如果加载没有变化，但连接数量在程序启动完成后仍然不断攀升，你很可能得面对连接移除。 最简单的方式是用 Performance Monitor(under Administrative tools on most Windows installations) 监控数据库的连接数，如果你晕行的是SqlServer add SQL Server General Statistics -> User Connections performance counter (The counter is available on the SQL Server machine so you may need to put its name or IP address into the Select Counters From Computer box).
另一种检测数据库连接的方式是通过查询你的DBMS。例如:EXEC SP_WHO
.NET CLR数据执行计数器 在文档中你可能偶然会遇到.NET CLR Data performance counters。如果你知道它们能做什么，不能做什么，就会发现它们很棒。记住它们并不经常能正确的复位。 The following KB article sheds some light on the problem but in my opinion does not cover all the issues: http://support.microsoft.com/default.aspx?scid=kb;en-us;314429.
记住另一件事情：IIS在负载压力下会写在程序域，所以不要惊讶你的数据库连接降到0，但你的数据库连接池最少连接为5.
Short term fixes
What if you discovered the connection pooling issue in production and you cannot take it offline to troubleshoot? Turn pooling off. Even though your app will take a performance hit, it shouldn't crash! Your memory footprint will also grow. What if it doesn't crash all that often, and you don't want to take a performance hit? Try this: 如果你没有在产品中发现连接池问题，并且你不能把它断线，那如何解决问题？关闭连接池。即使你的程序会产生不良影响，至少它不应该崩溃！你的内存占用也会增加。如果他没有经常的崩溃，并且你不想影响程序性能怎么办？
conn = new SqlConnection();
try
{
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;"; // Notice Connection Timeout set to only two seconds!
conn.Open();
}
catch(Exception)
{
if (conn.State != ConnectionState.Closed) conn.Close();
conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Pooling=false;Connect Timeout=45;";
conn.Open();
If I fail to open a pooled connection within two seconds, I am trying to open a non-pooled connection. This introduces a two second delay when no pooled connections are available, but if your connection leak doesn't show most of the time, this is a good steam valve. 如果我在2秒内无法打开一个池内连接，那就尝试打开一个池外连接。当使用池外连接时 会有2秒延迟，但是如果你的连接移除不在大部分时间显示出来的话，这无疑是一个好的方式。
Conclusion
In this article you've learned that the most common cause of connection pooling issues is database connections that are left open or not closed properly. You've learned that when you type "conn.Close()", you almost always want to put that in the "Finally" block. You also learned not to interfere with the class destructor unless you use unmanaged resources. You've learned how to monitor your connection pool and diagnose a potential problem. You also learned how to keep a system with a connection leak in production if you really have to, until the problem is resolved. I hope this article has helped you resolve your connection pooling issue. However, there is more to connection pooling that is not covered in this article. Check out Bill Vaughn's "Swimming in the .NET connection pool" at http://www.winnetmag.com/Article/ArticleID/38356/38356.html.
About the Author
Dmitri Khanine is senior web developer and architect working for a major Canadian Bank. His 10+ years of experience are mostly in backend and middle tier development of enterprise Web applications on Microsoft as well as J2EE platforms. Industry experience includes Banking, Finance, Automotive and software consulting. Dmitri's areas of interest and expertise include rapid enterprise application development, MVC frameworks and code generation.
http://www.codeguru.com/csharp/.net/net_asp/article.php/c19395
70
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
