Exploration of Database Connection Pool

1. Introduction

Database connection is a key, limited, and expensive resource, which is particularly prominent in the Web applications of multiple users. Database connection management can significantly affect the scalability and robustness of the entire application, and affect the performance indicators of the program. The database connection pool is designed to address this issue. The database connection pool is responsible for allocating, managing, and releasing database connections. It allows applications to repeat an existing database connection instead of reestablishing one; release the database connection with idle time exceeding the maximum idle time to avoid database connection omissions caused by the absence of the database connection. This technology can significantly improve the performance of database operations.

2. Implementation Strategy

1) Add database connection pool Configuration

You need to add some configurations of the database connection pool, such as the memory allocation size, failure processing function, and monitoring retries.

2) Add Database Server Configuration

When adding a database server configuration, you need to add services configured with the database IP address, port, username, password, dbname, charset, and maximum connections, minimum connections, connection timeout, and write timeout, read timeout and other parameters.

3) initialize the database connection pool

After the database server configuration is loaded, the connection pool of the database server is initialized, and the connections with the minimum number of connections are created in the pool, waiting for access.

4) Access Connection

Extract a connection from the database connection pool for use

5) connect back

Put the used database connection back to the database connection pool for future use. You need to add a parameter to indicate whether the connection is damaged. If the connection is damaged, mark it, it will not be provided to the next connection.

6) Regular scheduling in the connection pool

A major feature of the database connection pool is that it can greatly improve the performance, which is achieved through regular scheduling. If there is no connection pool, you need to create a connection for each query. After the query is complete, you need to release the connection so that the non-stop creation and release consume resources, the connection pool is characterized by unified scheduling of all connections in the pool, allocating resources well to achieve high performance.

Let's talk about the regular scheduling policy of the connection pool. Generally, this is the case. Maintain two values. The current connection number current_conn and the connection pool should have the number of connections need_conn. current_conn are generated based on the connections used by the client, need_conn is usually 125% of currrent_conn (this value is configurable), that is to say, the number of connections in the database connection pool should be 125% of the current number of connections, so the purpose is to buffer, prevents the number of connections between two scheduling tasks from increasing rapidly.

However, need_conn is also subject to the maximum number of connections and the minimum number of connections, that is, the maximum number of connections of need_conn cannot exceed the maximum number of connections (to ensure that the connection pool does not occupy too many resources ), the minimum number of connections cannot be lower than the minimum number of connections (to ensure efficient connection pool and reasonable use of resources ).

When scheduled in the database connection pool, you need to delete abnormal connections in the connection pool. In addition, you can create or release connections so that the number of connections in the database connection pool is the same as that in need_conn.

7) Risks and Solutions of Database Connection Pool

In practice, this problem is often encountered, because of connection fluctuations caused by connection failure. For example, the scheduled connection pool is 3 s, the minimum connection count is 20, and the maximum connection count is 50. The current number of connections is 10. At this time, the actual number of connections in the connection pool is 20, and the current number of connections increases to 30 in 3 S. At this time, no scheduled scheduling is conducted, and the actual number of connections in the connection pool is still 20, if the number of connections is smaller than the number of required connections, the connection will fail.

The solution to the above problems is: (1) increase the minimum number of connections to prevent data fluctuations from resulting in insufficient connections; (2) reduce the time interval of regular scheduling, in this way, the regular scheduling frequency of the database connection pool is increased, and the consumption of more resources is not necessarily a good benefit. (3) increase the buffer quota of the connection pool and increase 125% to a higher value. This can cope with the sudden increase of connections, but the defect is that more connections need to be created in the connection pool, consume more resources. These three solutions depend on the fluctuations in the number of connections, analyze the number of connections table, and select a more reasonable solution.

References

Http://baike.baidu.com/view/84055.htm