Concept of Connection Pool

Concept of Connection Pool

1) The connection pool is a process

Multiple connections are stored and managed in one process. This process saves all connections. when we open the connection, if there are unused connections available, this connection is returned. If all connections in the pool are used up, a new connection is created and saved to the connection pool. However, when we close the connection, the connection pool does not close the connection, but returns the status marked as reusable in the connection pool, waiting for the connection to reconnect until the wait times out. When we open the connection again, We can reuse the previous connection. If no connection request is sent during this time (the connection is enabled), the database connection will be closed and the connection instance will be removed from the connection pool. If the connection in the pool reaches the maximum number of connections, the request enters the waiting queue until the idle connection is available. If the timeout period expires before the connection object can be obtained (determined by the connect timeout connection string attribute), an error occurs.

2) The process saves the connection and makes it active so that the connection can be reused.

When an application accesses the underlying data source, it accesses the data through the OS data access component or the SQL client data access provider. All access the database of the database server through the components provided by the system. A connection is established through these components. A connection pool is built on these components and exists in the application process. Therefore, the connection must be reused in this process. For example, if two winform programs are on different computers and they connect to a database server at the same time, the connection pool will not work because the connection pool is in a process. At this time, we can build a web service on the top of the database server, and the connection pool is used between the Web Service and the database server, so the pressure on the database server will be low.

 

If no connection pool exists

1) connect to the data source

-Establish a physical channel (such as a socket or named pipe)

-Initial handshake with the server

-Analyze the connection string Information

-The server authenticates the connection.

-Run the check to register in the current transaction (whether the connected environment is a Distributed Transaction)

In addition, it is not only a connection pool, but also a large number of objects used.Object poolFor the next use.

 

Function of Connection Pool

1) through the database connection pool, applications can reuse existing connections in the pool without repeatedly establishing new connections with the database.

2) using the connection pool can significantly improve application scalability, because a limited number of database connections can provide services for a large number of clients. For example, in a database application, tens of thousands of users may only need a small number of connections, because the client cannot concurrently access a large number of databases. Therefore, a small number of connections can support a large number of services to improve program scalability.

3) at the same time, the connection pool can also improve performance because it can save a lot of time to create a new connection.

4) The connection pool is transparent to applications, such as Conn. open () and conn. Close ().

 

Working Process of the Connection Pool

1) when the pooling parameter is true (default), the connection pool is enabled.

In the application execution environment, a storage area is created to store the required connections.

2) The two connection strings are stored in the same connection pool.

3) The two connection strings are stored in different connection pools.

For example, a winform program may need to connect to multiple databases, which may result in multiple connection pools in the application. From the server perspective, the number of connections is also large, an application may have multiple application domains, and the connection pool belongs to one application domain. An application domain can have multiple connection pools. A connection string corresponds to a connection pool.

4) when the connection is closed, the connection is returned to the connection pool for reuse (when the connection in the connection pool is not active for a long time or exceeds the specified life cycle), it will be removed. In the aggregation configuration, you can use connection lifetime to force load balancing between the running server and the online server. This situation can be detected only when you try to communicate with the server. If a connection is no longer connected to the server, it is marked as invalid. The object pool manager regularly scans the connection pool to find objects that have been released to the pool and marked as invalid. After the connection is found, the connections will be permanently removed .)

5) when the connection is dispose, the connection is permanently deleted.

6) when the last connection in the connection pool is closed, the connection pool is deleted.

Note:Set the connection pool to true at the beginning. The number of concurrent connections depends on the actual situation. When the connection pool is full, it cannot be connected.

For a connection, to become an available connection, the connection must not be used currently. It has a matched transaction context or is not associated with any transaction context, and has a valid link with the server.

 

Control the SQL server connection pool

1) connection string variables related to the SQL server connection pool

Parameters	Description	Default Value	Remarks
Connection lifetime	Connection Lifecycle	0	The default value is 0, indicating that the connection will never be automatically removed from the pool.
Connection Reset	Connection Reset	True
Enlist	Registration	True
Max pool size	Maximum pool size	100	Maximum number of connections in the connection pool
Min pool size	Minimum pool size	0	If the connection pool is created, a specified minimum number of connections will be created.
Pooling	Pool	True	Enable connection pool?

 

Control ole db connection pool

1) The default connection pool function is activated and used in the ole db connection

2) To disable the ole db connection pool, specify "ole db services =-4" in the connection string. The Code is as follows:

Dim cnnorthwind as new oledbconnection ()

Cnnorthwindcnnorthwind. connectionstringconnectionstring = _

"Provider = sqloledb ;"&_

"Data Source = London ;"&

"Integrated Security = sspi ;"&_

"Ole db services =-4 ;"&_

"Initial catalog = northwind ;"

 

Use connection pool

1) The connection is pooled by the exact match algorithm on the connection string.

This algorithm associates the connection pool with a connection string. When a new connection is enabled, if the connection string does not match the existing pool accurately, a new pool is created.

2) The pool mechanism is even sensitive to spaces between name-value pairs.

 

Close the connection (returning the connection to the pool)

1) Close the connection immediately

2) do not close the connection in the finaliz method of the class (this method is not automatically called ).

-It is executed only when garbage collection is performed.

-The connection pool may be full.

3) The connections explicitly opened before the dataadappter are not automatically closed (if the connection is displayed before the dataadappter is used, the connection must be closed after use, and the connection is automatically closed)

4) connections that are not explicitly closed will not be added or returned to the pool.

 

Resident of the Connection Pool

1) if minpoolsize is not specified in the connection string or is specified as zero, the connection in the pool will be closed after being inactive for a period of time in the pool.

2) If the specified minpoolsize is greater than zero, the connection pool will not be damaged until the appdomain is detached and the process ends.

3) maintenance of inactive or empty pools only requires minimal system overhead.

 

Clear connection pool

1) clearallpools

-Clear the connection pool of the given provider.

2) clearpool (sqlconnection)

-Clear the connection pool associated with a specific connection.

-If the connection is in use during the call, it will be marked accordingly. When the connection is closed, it will be discarded rather than returned to the pool.

 

Pool fragments

1) pool fragmentation is a common problem in the order of 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, thus affecting performance.

-Pool fragmentation caused by integration Security

-Because many databases generate pool fragments

 

Transaction support

1) The connection is retrieved from the pool and allocated based on the transaction context.

-Unless enlist = false is specified in the connection string, the connection pool ensures that the connection is registered in the current context.

-When the connection passes the registered system. when the transactions transaction is closed and returned to the pool, the connection will be retained so that the next time you use the same system. when a transactions transaction requests the connection pool, the same connection can be returned (if the connection is available ).

-If the connection is unavailable, a new connection is opened.

-If no connection is available for the transaction, the connection is automatically registered when the connection is opened.

2) When the connection is closed, it will be released back to the pool and placed into the corresponding sub-parts according to the transaction context.

-Therefore, you can close the connection even if the distributed transaction is still suspended without generating an error.

-In this way, you can submit or stop distributed transactions later.

-When the connection is returned to the pool, the creation time of the connection is compared with the current time. If the interval exceeds the value specified by connection lifetime (in seconds ), the connection is destroyed. In clustering configuration, you can use it to force load balancing between the running server and the online server. If the value is zero (0), the pool connection has the maximum timeout period.

 

Dedicated transaction pool

1) The connection pool is divided into multiple transaction proprietary pools and a pool corresponding to the connections that are not currently registered in the transaction.

2) For threads associated with a specific transaction context, the corresponding pool will be returned to the corresponding pool (the pool contains the connections registered in the transaction and included in the transaction).

3) This makes the use of registered connections a transparent process.

 

Use the pool object to manage the connection pool

1) as a distributed developer, we encourage you to disable the ole db resource pool and // or ODBC connection pool, and use the COM + Object pool as the technology for pooling database connections.

2) There are two main reasons:

-The pool size and threshold values can be clearly configured (in the COM + directory ).

-Improved performance. The performance of the pool object method is 50% higher than that of the local pool.

3) if you are using ole db. NET data provider, you may need to consider using the COM + Object pool to make full use of superior configuration and improved performance. If you develop a pool object for this purpose, you must disable the ole db resource pool and automatic transaction registration (for example, by adding a connection string, you can include "Ole" DB serviices =-4) in the connection string ). You must process transaction registration in your own pool object implementation.

4) for SQL Server. NET data providers, it uses the pool mechanism internally, so you no longer need to develop your own object pool mechanism (when using the provider ). Therefore, you can avoid complex tasks associated with manual transaction registration.

 

Monitoring connection pool

1) SQL Server event Probe

2) Performance Monitor

You can access the connection pool counter in the "Performance Monitor" under the ". Net CLR data" performance object. Accessible:

A) Number of current pool connections and non-pool connections

B) Number of connections associated with specific processes in all the current pools

C) Number of pools currently associated with a specific process

D) peak number of connections in all pools since the start of a specific process. Note: This counter is available only when it is associated with a specific process instance.

E) the total number of attempts to open the connection that failed for any reason

3) Programming Method

4) use sp_who or sp_who2 Stored Procedure

 

From http://www.cnblogs.com/lujiao_cs/archive/2011/07/07/2099943.html