Abstract
Today I'm going to talk about database connection pools. To be honest, I said that the duck pear is very big. Because connection pools are relatively difficult to understand compared to other chapters. I want to explain it to you in the most popular sentences, but it is not an easy task. However, connection pools are also a very important knowledge point, especially when deploying multi-user programs. Therefore, I not only have to speak but also speak thoroughly. Through this article, you'll understand the fundamentals of connection pooling and how it's already leveraged to improve the performance of your application.
Table of contents
What is a connection pool?
How the connection pool works
Let's talk about a few very important properties of connection pools
Connection pool exceptions and handling methods
A way to monitor the status of your connection
Basic principles for efficient use of connection pools
1. What is a connection pool?
In the previous article, "ADO.NET You Must Know (4) Taste Connection Objects", I have emphasized that establishing a database connection is a very time-consuming (time-consuming) and labor-intensive (resource-consuming) thing. This is because connecting to a database server involves several lengthy processes: establishing a physical channel (such as a socket or named pipeline), making an initial handshake with the server, analyzing the connection string information, having the server authenticate the connection, running a check to register in the current transaction, and so on. Regardless of why there is such a mechanism, there is always a reason for existence. Since creating a new connection is so painful, why not reuse an existing one?
In fact, ADO.NET already gives us an optimization method called connection pooling. A connection pool is one such container: it houses a certain number of physical connections to the database server. Therefore, when we need to connect to the database server, we only need to take out a free connection from the pool (container) instead of creating a new one. This greatly reduces the overhead of connecting to the database, which improves the performance of the application.
PS:本来做了2张图片来描述连接池的，无奈公司装有监控软件，不能上传，所以只能等下次有时间上传了。
2. How the connection pool works
2.1 Create a connection pool
It should be noted that connection pools are class-sensitive. That is, the same application domain can have multiple different types of connection pools at the same time. So, how are connection pools identified? In detail, it is distinguished by the signature that forms the process, the application domain, the connection string, and the Windows identity (when using integrated security). However, for the same application domain, it is generally only identified by the connection string. When a connection is opened, a new connection pool is created if the type signature of the strip connection does not match the type of the existing connection pool. otherwise, no new connection pool is created.
A typical example of creating a connection:
In the above instance, I created three SqlConnection objects, but only two connection pools were needed to manage them. Careful friends, you may have already found that conn1 and conn3 have the same connection string, so they can share a connection pool, while conn2 and conn1 are different from conn3, so you need to create a new connection pool.
2.2 Allocating idle connections
When a user creates a connection request or calls the Open of a Connection object, the connection pool manager first needs to find a connection pool of the matching type based on the type signature of the connection request, and then try to allocate a free connection. The details are as follows:
If there is an available connection in the pool, return the connection.
If all connections in the pool are exhausted, create a new connection to add to the pool.
If the maximum number of connections in the pool has been reached, the request enters the waiting queue until there are free connections available.
2.3 Remove Invalid Connections
Invalid connections, that is, connections that do not connect correctly to the database server. For connection pools, the number of connections stored to the database server is limited. Therefore, if invalid connections are not removed in time, space in the connection pool will be wasted. In fact, you don't have to worry, the connection pool manager has taken care of these problems for us very well. If a connection is idle for an extended period of time, or if it detects that the connection to the server is disconnected, the connection pool manager removes the connection from the pool.
2.4 Recycle used connections
When we finish using a connection, we should close or release it in time so that the connection can be returned to the pool for reuse. We can close the connection through the Close or Dispose method of the Connection object, or through the using statement in C#.
3. Tell me about a few very important attributes
The behavior of the connection pool can be controlled by connection strings and mainly includes four important properties:
Connection Timeout: The time for the connection request to wait for a timeout. The default is 15 seconds in seconds.
Max Pool Size: The maximum number of connections in the connection pool. The default is 100.
Min Pool Size: The minimum number of connections in the connection pool. The default is 0.
Pooling: 是否启用连接池。ADO.NET默认是启用连接池的，因此，你需要手动设置Pooling=false来禁用连接池。
Let's take an example to understand the properties of the connection pool. The code is as follows:
4. Connection pool exceptions and handling methods
When a user opens a connection without closing it correctly or in a timely manner, it often triggers the "connection leak" issue. The leaked connection remains open until the Dispose method is called, and the Garbage Collector (GC) closes and releases the connection. Unlike ADO, ADO.NET need to manually close used connections. An important myth is that the connection is closed when the connected object is outside the local scope. In fact, when out of scope, only the connection object is released, not the connection resource. Well, let's take a look at an example first.
To make the results more obvious, I specially set the maximum number of connections to 5 and the timeout time is 1 second. After running, the following results are obtained quickly.
From the above results, it is clear that there is an abnormality in the connection. We already know that the maximum number of connections in the connection pool is 5, and when the 6th connection is created, it is necessary to wait for the connection until it times out because the maximum number of connections in the connection pool has reached the maximum number and there are no free connections. When the timeout time is exceeded, the connection exception mentioned above will appear. Therefore, I must emphasize once again that used connections should be properly closed and released as soon as possible.
5. Methods for monitoring the status of SQL Server connections
(1) Through the activity monitor
Step 1: Open the MSSMS Manager and click the "Activity Monitor" icon.
Step 2: In the Open Activity Monitor view, click on the Processes tab.
Step 3: Run the example in #4 Connection Pool Exceptions and Handling Methods, you can see the 5 open connections as shown in the figure below.
(2) Use T-SQL statements
Likewise, by executing the system stored procedure sp_who we can also monitor the state of the connection.
6. Basic principles for efficient use of connection pools
Making good use of the connection pool will greatly improve the performance of your application. On the contrary, if used incorrectly, it will do no harm. In general, the following principles should be followed:
Request a connection at the latest moment, release the connection at the earliest.
When you close the connection, close the relevant user-defined transaction first.
Ensure and maintain at least one open connection in the connection pool.
Try to avoid pool debris. This mainly includes pool fragmentation generated by integrated security and pool fragmentation generated by using many databases.
Tip: Pool fragmentation is a common problem in many web applications, and applications may create a large number of pools that are not released until the process exits. In this way, a large number of connections will be opened, taking up a lot of memory, resulting in reduced performance.
|
Posted on 9/17/2016 3:41:28 PM
|
|
|
|
