Ado. Net (5) You must know about the database connection pool

ArticleDirectory

• Summary
• Directory

Digress

Through the previous chapters, I do not know if you have some knowledge of ADO. net. Regardless of the quality of the article, it must be affirmed that I write every article with my heart. I have spent a lot of time on both typographical and content selection. I hope that through this series of articles, both novice and veteran will be able to gain some benefits in ADO. net.If you think it is helpful, I hope to get your recommendation and attention and let me know your affirmation of me. If you think that I am not writing well, I would like to listen to criticism and let us make progress together.

Summary

Today I want to talk about the database connection pool. To be honest, I mean the pears are huge. It is difficult to understand the connection pool compared with other chapters. It is not easy for me to use the most popular statement to understand it thoroughly. However, the connection pool is an important knowledge point, especially when multiple users are deployed.ProgramIs particularly important. Therefore, I should not only give a thorough explanation. Through this article, you will understand how the basic principles of the connection pool have used the connection pool to improve application performance.

Directory

• What is a connection pool?
• How the connection pool works
• Several important attributes of the Connection Pool
• Connection Pool exceptions and Handling Methods
• How to monitor connection status
• Basic principles for efficient use of connection pools

1. What is a connection pool?

In the previous article "you must know about ADO. Net (4) taste the connection object", I have already stressed that,Establishing a database connection is a very time-consuming task.The reason for this is that it takes several long steps to connect to the database server: establishing a physical channel (such as a socket or named pipe), shaking hands with the server for the first time, and analyzing the connection string information, the server authenticates the connection and runs the check to register the connection in the current transaction. No matter why such a mechanism exists, there is always a truth about it. Since it is so painful to create a new connection, why not reuse the existing connection?

In fact, ADO. NET has provided us with an optimization method named connection pool.The connection pool is a container that stores a certain number of physical connections to the database server.Therefore, when we need to connect to the database server, we only need to retrieve an idle connection from the pool (container) instead of creating a new connection. In this way, we can greatly reduce the overhead of connecting to the database, thus improving the performance of the application.

PS:I have made two pictures to describe the connection pool, but the company has installed monitoring software and cannot upload it. So I have to wait for the next time to upload it.

 

2. How the connection pool works 2.1 create a connection pool

It must be noted that,Connection pools are categorized.That is to say, the same application domain can have multiple connection pools of different types at the same time. How is the connection pool identified? In detail, it is identified by a combination of processes, application domains, connection strings, and Windows identities (when using integrated security. However, for the same application domain, it is generally identified only by the connection string. When a connection is opened, if the type signature of the connection does not match the existing connection pool type, a new connection pool is created. Otherwise, no new connection pool is created.

A typical instance for creating a connection:

 //  Create connection object 1  
  Using (Sqlconnection conn1 =

 New Sqlconnection ( "  Datasource = (local); Integrated Security = sspi; initial catalog = northwind " ))
{
Conn1.open ();
}


 //  Create connection object 2  
  Using (Sqlconnection conn2 =

 New Sqlconnection ( "  Datasource = (local); Integrated Security = sspi; initial catalog = pubs  " ))
{
Conn2.open ();
}


 //  Create connection object 3  
  Using (Sqlconnection conn3 =

 New Sqlconnection ( "  Datasource = (local); Integrated Security = sspi; initial catalog = northwind  " ))
{
Conn3.open ();
}

In the above instance, I created three sqlconnection objects, but only two connection pools are required for management. Careful friends may have already discoveredConn1 and conn3 have the same connection string, so you can share a connection pool. conn2 and conn1 are different from conn3. Therefore, you need to create a new connection pool.

2.2 allocate idle connections

When a user creates a connection request or calls an open connection object,The connection pool manager first needs to find a matched Connection Pool Based on the type signature of the connection request, and then tries its best to allocate an idle connection.The details are as follows:

• If an idle connection is available in the pool, the connection is returned.
• If all connections in the pool are used up, create a new connection and add it to the pool.
• If the maximum number of connections has been reached in the pool, the request enters the waiting queue until idle connections are available.

2.3 Remove invalid connections

Invalid connection, that is, the connection to the database server cannot be correctly connected.For the connection pool, the number of connections stored to the database server is limited.Therefore, if an invalid connection is not removed in time, the connection pool space will be wasted. In fact, you don't have to worry. The connection pool manager has handled these problems for us.If the connection is idle for a long time or the connection with the server is detected to be disconnected, the connection pool manager removes the connection from the pool.

2.4 recycle used connections

When we use a connection, we should close or release the connection in time so that the connection can be returned to the pool for reuse.We can use the close or dispose method of the connection object, or use the C # using statement to close the connection.

 

3. Several Important attributes

Connection Pool behaviors can be controlled through connection stringsIt mainly includes four important attributes:

• Connection Timeout:Connection Request timeout. The default value is 15 seconds, in seconds.
• Max pool size:The maximum number of connections in the connection pool. The default value is 100.
• Min pool size:The minimum number of connections in the connection pool. The default value is 0.
• Pooling:Whether to enable the connection pool.By default, ADO. Net enables the connection pool,Therefore, you need to manually set pooling to false to disable the connection pool.

Let's look at an instance to understand the attributes of the connection pool.CodeAs follows:

Sqlconnectionstringbuilder connstr = New Sqlconnectionstringbuilder ();
Connstr. datasource =@"  . \ Sqlexpress  " ;
Connstr. initialcatalog = "  Master  " ;
Connstr. integratedsecurity = True ;

Connstr. Pooling = True ; //  Enable connection pool  
 Connstr. minpoolsize = 0 ; //  Set the minimum number of connections to 0.  
 Connstr. maxpoolsize = 50 ; //  Set the maximum number of connections to 50  
 Connstr. connecttimeout = 10 ; //  Set the timeout value to 10 seconds.  
 
 Using (Sqlconnection conn = New Sqlconnection (connstr. connectionstring ))
{
; //  Todo  
 }

 

4. Connection Pool exceptions and Handling Methods

When a user opens a connection but does not close it correctly or in time, the "connection leakage" problem is often caused. The exposed connection remains open until the dispose method is called and the garbage collector (GC) closes and releases the connection. Unlike ado, ADO. Net needs to manually close the used connection.An important misunderstanding is that when the connection object is out of the local scope, the connection will be closed.In fact, when the scope is exceeded,Only Connection objects rather than connection resources are released.Okay. Let's take a look at an instance first.

 Using System;
 Using System. Collections. Generic;
Using System. LINQ;
 Using System. text;
 Using System. Data;
 Using System. Data. sqlclient;

 Namespace Connectionpool
{
 Class Program
{
 Static   Void Main ( String [] ARGs)
{
Sqlconnectionstringbuilder connstr = New Sqlconnectionstringbuilder ();
Connstr. datasource = @"  . \ Sqlexpress  " ;
Connstr. initialcatalog = "  Master  " ;
Connstr. integratedsecurity = True ;

Connstr. maxpoolsize = 5 ;//  Set the maximum connection pool to 5  
 Connstr. connecttimeout = 1 ; //  Set the timeout value to 1 second.  
 
Sqlconnection conn = Null ;
 For ( Int I = 1 ; I <= 100 ; ++ I)
{
Conn = New Sqlconnection (connstr. connectionstring );
 Try 
{
Conn. open ();
Console. writeline ( "  Connection {0} is linked  " , I );
}
 Catch (Exception ex)
{
Console. writeline ( "  \ N exception information: \ n {0} " , Ex. Message );
 Break ;
}
}

Console. Read ();

}
}
}

To make the result more obvious, I set the maximum number of connections to 5 and the timeout time to 1 second. After running, the following results will be obtained soon.

From the above results, we obviously know that the connection has encountered an exception. We know that the maximum number of connections in the connection pool is 5. When you create 6th connections, you need to wait until the connection times out because the number of connections in the connection pool has reached the maximum and there is no idle connection. When the timeout time is exceeded, the above connection exception occurs. Therefore,I must re-emphasize that the used connection should be closed and released correctly as soon as possible.

 

5. How to monitor the connection status of SQL Server (1) Through activity monitor

Step 1:Open the mssms manager and click"Activity monitor"Icon.

 

Step 2:In the activity monitor view, clickProcessTab.

Step 3:Run#4 connection pool exceptions and Handling MethodsAs shown in.

 

(2) Use T-SQL statements

Similarly, by executing system stored proceduresSp_who, We can also monitor the connection status.

 
ExecSp_who
 

The following results are displayed:

 

 

6. Basic principles for efficient use of the Connection Pool

Using a good connection pool will greatly improve application performance. On the contrary, improper use may lead to no harm. In general, the following principles should be observed:

• Request a connection at the latest time and release the connection at the earliest time.
• Close user-defined transactions before closing the connection.
• Make sure that there is at least one open connection in the connection pool.
• Try your best to avoid the generation of pool fragments. It mainly includes the pool fragments generated by integration security and the pool fragments generated by many databases.

Tip:Pool fragmentation is a common problem in many web applications. Applications may create a large number of pools that will be released after the process exits. In this way, a large number of connections will be opened, occupying a lot of memory, resulting in performance degradation.