Shulou(Shulou.com)05/31 Report--

This article will explain in detail the principle and configuration of database connection pool dbcp. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. Brief introduction

DBCP (DataBase Connection Pool), database connection pool. Is a java connection pooling project on apache and a connection pooling component used by tomcat. Using dbcp alone requires three packages: common-dbcp.jar,common-pool.jar,common-collections.jar because establishing a database connection is a very time-consuming and resource-consuming behavior, so establish some connections with the database in advance through the connection pool and put them in memory. When the application needs to establish a database connection, just apply for one in the connection pool and put it back after use.

Dbcp provides database connection pool, which can call dbcp in spring,iBatis,hibernate to complete database connection. Frameworks generally provide the method of dbcp connection.

The jndi setting method of dbcp is also provided in tomcat, or you can not use dbcp in the framework. Using dbcp alone requires three packages: common-dbcp.jar,common-pool.jar,common-collections.jar

two。 Summary of connection pool

JDBC is a set of general Java language and a variety of database (file) communication standard API. Most JDBC implementations for database servers (such as Oracle, MySQL, etc.) are client-server communication based on TCP/IP connections.

When we need to perform a database operation, there are three steps:

1. Establish a database connection between the client and the server

two。 Perform some kind of database operation

3. Disconnect

If you take the above three steps for each processing, the application and the database server will spend a lot of time and resources on disconnecting and establishing the data connection. For some database systems, a database connection is a process, and a database connection usually takes up a lot of resources, such as sort area / Join area and so on. For systems with large concurrency, establishing a connection and then caching it for continuous use, and then releasing the connection until the end of the program, the system resources can be focused on the processing of database operations, thus greatly improving performance. Typically, the establishment and disconnection of data connections are delegated to a component or service that can pool database connections. DBCP, C3p0, Proxool and so on are commonly used open source connection pooling components.

It's as if Company An is in the suburbs, and there are no taxis near their company. If someone in Company A wants to run errands, he must call the rental company to order a car, and he will have to pay for reimbursement after using the car. In this way, everyone has to book, return and be reimbursed for a trip. A lot of employees' time is wasted on it. So Company A made a contract with the taxi company, and the taxi company gave them a fleet. If you want to use the car, you can go downstairs to find the motorcade at any time, and you don't have to check out even if you run out of it. Company A will book and check out with the taxi company. This fleet is like a connection pool, with the company (application) uniformly booking (establishing a connection) and returning (closing the connection) from the taxi company (database server).

Configuration parameters of 3.DBCP and the principle behind it

The configuration parameters of Commons-dbcp connection pool are many and complex, which are mainly divided into

Jdbc connection parameters (username, password, url, driverClassName, connectionProperties)

Transaction parameters (defaultAutoCommit, defaultReadOnly, defaultTransactionIsolation, defaultCatalog)

Connection pool parameters (see below for details)

Link viability test parameters in the connection pool (see below for details)

Preprocessing query pooling parameters (poolPreparedStatements, maxOpenPreparedStatements)

Discard parameters related to failed links (see below) and an underlying JDBC link parameter (accessToUnderlyingConnectionAllowed) that controls whether it can normally be wrapped under the access connection pool.

Among them, the Jdbc link parameters and transaction processing have little to do with the connection pool, and the preprocessing query pooling parameters are not described in detail in this paper. For detailed parameter configuration information on commons-dbcp, please refer to the official documentation.

4. Configuration of connection pool

Using the motorcade as an analogy, every time a taxi company provides a car to Company A, Company A will certainly have to pay a certain fee. At this time, it is very important to maintain the size of the fleet. When there are fewer vehicles in the project, the fleet must be reduced, otherwise many idle vehicles will have to pay the cost. When the project uses more vehicles, the fleet must be expanded, otherwise there are not enough cars. The motorcade is too busy. Company A can simply set two thresholds to dynamically adjust the number of idle vehicles in the fleet to meet the dynamic demand. One is the minimum number of idle vehicles (minimum idle connections (minIdle). When the remaining number of idle vehicles is less than this number, Company An asks the taxi company to join the new car. One is the maximum number of idle vehicles (maxIdle). When the remaining cars are greater than that number, the newly used cars are returned to the taxi company to reduce the number of fleets.

In addition, the taxi company may have to serve multiple customers, considering that the maximum number of cars that can be provided to Company A cannot exceed a certain number (maxActive). So when company A wants a taxi to apply for a new car, it first needs to see if the number of vehicles currently in use exceeds this maximum, and if it does not, then apply for a new car directly, otherwise it can make the applicant (the thread that executes the request in the application) wait (maxWait0 fails when the waiting time exceeds maxWait).

In the connection pool, these parameters are very important, and the official explanation is as follows, which are the values that we need to carefully consider when adjusting system performance.

Parameter

Default

Description

InitialSize

0

The initial number of connections that are created when the pool is started.

Since: 1.2

The number of connections initialized when the thread pool starts

MaxActive

eight

The maximum number of active connections that can be allocated from this pool at the same time, or non-positive for no limit.

The maximum number of active connections, if not a positive integer, is not limited.

MaxIdle

eight

The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit.

Maximum number of idle connections.

MinIdle

0

The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none.

Minimum number of idle connections.

MaxWait

Indefinitely

The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or-1 to wait indefinitely.

(when no connection is available) the maximum wait time in milliseconds for the connection pool to wait for a data connection to be available. If an exception is thrown after the timeout,-1 will wait indefinitely.

In fact, Dbcp relies on commons-pool to store connection objects. BasicDataSource uses GenericObjectPool by default to manage connection objects. In addition to the fact that the requesting thread will affect the connection instance in the pool during the request and return connection, another thread running an instance of type GenericObjectPool.Evictor (implements Runnable) will also affect the database connection in the pool.

5. Pay attention to use

1) if the maxIdle setting is too low, in a high-load system, the opening time of the connection is faster than the closing time, which will cause the number of idle in the connection pool to rise more than the maxIdle, resulting in frequent destruction and creation, similar to the Xmx setting in the jvm parameter.

2) minIdle, the closer this parameter is to maxIdle, the better the performance, because the creation and termination of connections require resource consumption, but not too large, because the number of connections less than minIdle will be created when the machine is idle, similar to the Xms setting in the jvm parameter.

3) PreparedStatements will keep open cursors in the database, which will cause a connection to exceed the database cursor limit, especially when maxOpenPreparedStatements is set to default (unlimited), and the application creates a large number of different PreparedStatements on a connection, to avoid this problem, maxOpenPreparedStatements should be set to less than the maximum number of cursors allowed to open on a connection.

4) poolPreparedStatements, after testing, the performance after opening is not as good as that turned off, or there is little difference, and there is no essential improvement.

5) two parameters that have a great impact on performance:

TestOnBorrow: this default is true, which has a great impact on performance, because it is expensive to validate each time an object is lent out from the pool, and the actual test shows that the performance gap is 7-10 times.

MaxIdle: if the value of this parameter is lower than maxActive, many time_ waiting threads will be generated under high load. According to our test results, this value had better be the same as maxActive or set to-1. Only in this way can the connection to the database be stable and use the inherent connection under high concurrency. Otherwise, dbcp will frequently create new connections and discard old ones. The operation of creating a connection is more expensive than authentication.

Because the maxIdle is set to a high value, the connections created by the application during the system peak will be maintained there. These connections seem a bit wasteful in their spare time. In many cases, database machines may support multiple applications, or there may be many application machines in a cluster. In order for these idle connections to be recycled, a configuration item must be set. This value, along with minEvictableIdleTimeMillis and numTestsPerEvictionRun, affects the behavior of evictor thread. The default value of timeBetweenEvictionRunsMillis is-1, which means it will not run. But if it sets a value that is not-1, then minEvictableIdleTimeMillis cannot be set too short, and the default value of 30 minutes is a good choice.

6. Sample configuration

Configuration of the 7.Mysql database:

Com.mysql.jdbc.Driver

Jdbc:mysql://localhost:3306/booksystem

Root

Mysqladmin

ten

two

About the principle and configuration of database connection pool dbcp is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.