Explain.Net database connection pool in detail

Time:2022-7-29

Generally, one or two database connection configurations will be used in our project. The database connection configuration of Tongcheng Yilong will be consolidated into a unified configuration center, which will be uniformly configured and maintained by the DBA. The business party will get the connection object through a string configuration.

The DBA can switch the backup database to the business party without invasion to the business party, and then the DBA requires that the old connection pool must be emptied immediately. Then the question comes: can dotnet empty the connection pool immediately? Note that I use emptying instead of releasing the connection.

If some students don’t know the purpose of DBA’s doing this requirement, let me say a few words:
When the application no longer uses the old connection, theoretically your connection pool should be completely emptied, because simply releasing the connection will only make the connection in the connection pool in the sleep state, and still maintain the physical connection for a short time. This short time is actually an unnecessary occupation, affecting the throughput of the old connection database.

Pre knowledge background

Before answering this question, let’s first study.Net database connection pool.

1. Background of.Net database connection pool

Database connection is a time-consuming behavior. Most applications only use one to several database connections. In order to minimize the cost of opening connections, ado.net uses an optimization technology called connection pooling.

2. Performance of.Net database connection pool

The database connection pool reduces the number of new connections that must be opened, and the pool program maintains the physical connections to the database.

Manage connections by maintaining a set of active connection objects for each specific connection configuration.

Whenever an application attempts to open a connection, the pool program will find an available connection in the pool, and if there is one, it will return it to the caller;
When the application closes the connection object, the pool program returns the connection object to the pool (sleep), and this connection can be reused in the next open call.

Look at the blackboard. Here are the key points of this time:

3. How does.Net form a database connection pool?

Only the same connection configuration can be pooled. Net maintains different connection pools for different configurations.

The same configuration is limited to:
The process is the same
The connection string is the same
The key sequence of the connection string is the same.
(the keywords provided by the same connection in different order will be divided into different pools).

The number of available connections in the connection pool is determined by the connection string max pool size.

In an application, there are the following codes:


using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=Northwind"))  
    {  
        connection.Open();
        // Pool A is created.  
    }  
  
using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=pubs"))  
    {  
        connection.Open();
        // Pool B is created because the connection strings differ.  
    }  
  
using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=Northwind"))  
    {  
        connection.Open();
        // The connection string matches pool A.  
    }

Three connection objects are created above, but only two database connection pools are formed.

Or the above code. If there are two identical applications, in theory, four database connection pools will be formed.

4. When will the connections in the connection pool be removed?

The connection in the connection pool is idle for 4-8 minutes, and the pool program will remove this connection.

When the application goes offline, the connection pool is directly emptied.

How does.Net empty the connection pool?

With the aboveKnowledge background

Let’s review the requirements of DBAs. When switching the original connection configuration, empty the connection pool.

I found it in the official documents
.net provides
Clearallpools and clearpool static methods are used to empty the connection pool.

  • Clearallpools: clear all connection pools related to this dbprovider
  • Clearpool (dbconnection conn) empties the connection pool associated with this connection object

Obviously, we will use the clearpool (dbconnection conn) method this time.

It’s not my style to talk without practice and verification.

Skyhammer pressure test /queryapi generates a connection pool containing a large number of connection objects;
When appropriate, /clearpoolapi empties the connection pool.

public class MySqlController : Controller
    {
        // GET: MySql
        [Route("query")]
        public string Index()
        {
            var s = "User ID=teinfra_neo_netreplay;Password=123456;DataBase=teinfra_neo_netreplay;Server=10.100.41.196;Port=3980;Min Pool Size=1;Max Pool Size=28;CharSet=utf8;";
            using (var conn = new MySqlConnection(s))
            {
                var comm = conn.CreateCommand();
                comm.CommandText = "select count(*) from usertest;";
                conn.Open();
                var ret = comm.ExecuteScalar();

                comm.CommandText = "select count(*) from information_schema.PROCESSLIST WHERE HOST like  '10.22.12.245%';";
                var len = comm.ExecuteScalar();
                Return $"query result: {return}. By the way, check the number of connection objects in the current connection pool: {len}";
            };
        }

        [Route("clearpool")]
        public string Switch()
        {
            var s = "User ID=teinfra_neo_netreplay;Password=123456;DataBase=teinfra_neo_netreplay;Server=10.100.41.196;Port=3980;Min Pool Size=1;Max Pool Size=28;CharSet=utf8;";
            using (var conn = new MySqlConnection(s))
            {
                conn.Open();
                MySqlConnection.ClearPool(conn);
            };

            using (var conn = new MySqlConnection(s))
            {
                conn.Open();
                var comm = conn.CreateCommand();
                comm.CommandText = "select count(*) from information_schema.PROCESSLIST WHERE HOST like  '10.22.12.245%';";
                var len = comm.ExecuteScalar();
                Return $"the connection pool has been emptied before, and there are {v1} connection objects in the connection pool queried this time";
            }

        }
    }

1. After pressure measuring tool

2. MySQL database comparison

MySQL connection number query command, (host is the web server IP):

select * from information_schema.PROCESSLIST WHERE HOST like ‘10.22.12.245%’;

3. Call /clearpoolapi to empty the connection pool

Bingo, the theory of emptying connection pool has been verified.

Dry goods narration

This is the deep pit I recently climbed in Tongcheng Yilong,
I understand it from this practice Net database connection pool, and master the query and positioning method of dbprovider for the number of.Net connections.

Transformation of ancestral code Net data acquisition component SDK does improve the throughput of the original database.

I hope that the design consideration and theory + demonstration of this article will be helpful to readers. Thank you again for your interest and attention.

This is the end of this article about the detailed explanation of.Net database connection pool, and more related Net database connection pool content, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!

Recommended Today

JS generate guid method

JS generate guid method https://blog.csdn.net/Alive_tree/article/details/87942348 Globally unique identification(GUID) is an algorithm generatedBinaryCount Reg128 bitsNumber ofidentifier , GUID is mainly used in networks or systems with multiple nodes and computers. Ideally, any computational geometry computer cluster will not generate two identical guids, and the total number of guids is2^128In theory, it is difficult to make two […]