Analysis of Database Connection Pool (2), analysis of Database Connection Pool

Analysis of Database Connection Pool (2), analysis of Database Connection Pool

In the previous blog, I briefly introduced the connection process and resources consumed by common databases, and briefly discussed the connection pool, in this article, we will mainly look at the use of the database connection pool and its optimal configuration.

Total directory:
-1. What is the database connection process?
-2. What resources are used by connections?
-3. Connection Pool Introduction
-4. Use of the Connection Pool
-5. Optimal connection pool configuration Selection

Today we will mainly look at 4 and 5.

4. Use of the Connection Pool

Here I am using the c3p0 database connection pool
C3p0: C3P0 is an open source JDBC connection pool. It implements data source and JNDI binding and supports standard extensions of JDBC3 and JDBC2. Currently, open-source projects using it include Hibernate and Spring.

There are also many types of connection pools, and each type has different application scenarios. Therefore, selecting a suitable connection pool is also one aspect ~

First, compare the data that does not use the connection pool and uses the connection pool.
Recyclically connect 10 times, and the image shows 10th groups of data tested.

Do not use connection pool

Use connection pool
Red indicates the configuration information of the connection pool.

From the picture, we can clearly see that the connection pool speed is much faster than that of normal connections. Here Many do not refer to the difference between the two in milliseconds, but the difference in multiples.

Java connection pool code:

Import com. mchange. v2.c3p0. comboPooledDataSource; import java. awt. color. profileDataException; import java. beans. propertyVetoException; import java. SQL. connection; import java. SQL. resultSet; import java. SQL. SQLException; import java. util. resourceBundle;/*** Created by wwh on 15-6-10. */public class DBPollClass {private static DBPollClass dbPoll; private ComboPooledDataSource dbSource; // static code block, which is constructed at the beginning. Function load configuration information static {dbPoll = new DBPollClass ();} public DBPollClass () {// set configuration information try {dbSource = new ComboPooledDataSource (); dbSource. setUser ("root"); dbSource. setPassword ("123456789"); dbSource. setJdbcUrl ("jdbc: mysql: // 127.0.0.1: 3306/XL_db? User = root & password = 123456789 & useUnicode = true "); dbSource. setDriverClass ("com. mysql. jdbc. driver "); dbSource. setInitialPoolSize (1); dbSource. setMinPoolSize (2); dbSource. setMaxPoolSize (10); dbSource. setMaxStatements (50); dbSource. setMaxIdleTime (60);} catch (PropertyVetoException e) {throw new RuntimeException (e) ;}// get the connection public final static DBPollClass getInstance () {return dbPoll;} public final C Onnection getConnection () {try {return dbSource. getConnection ();} catch (SQLException e) {throw new RuntimeException ("unable to get connection", e) ;}} public static void main (String [] args) throws SQLException {for (int I = 0; I <10; I ++) {long begintime = System. currentTimeMillis (); Connection con = null; try {// get idle Connection con = dbPoll. getInstance (). getConnection (); // execute the SQL statement and return the query result // ResultSet rs = con. createSta Tement(cmd.exe cuteQuery ("SELECT * from UserInfo"); // use PreparedStatement instead of Statement PreparedStatement pst = con. prepareStatement ("SELECT * from UserInfo"); ResultSet rs = pst.exe cuteQuery (); // output the query result while (rs. next () {System. out. println (rs. getObject (1) + "" + rs. getObject (2) + "" + rs. getObject (3) + "" + rs. getObject (4) ;}} catch (Exception e) {e. printStackTrace ();} finally {if (con! = Null) {// return idle connection con. close () ;}long endtime = System. currentTimeMillis (); System. out. println (I + 1) + "time is:" + (endtime-begintime ));}}}

From the code we can and clearly see that the use of the connection pool is very simple, in the code I will be the connection pool configuration and other information written to the constructor, in fact, in reality we use the general written in c3p0-config.xml, in the configuration file of the database connection pool, put it in the src directory.
The procedure for connecting a connection pool to a database is basically the same as that for connecting to a common JDBC database. The parameters are similar.
However, some meanings are different. The connection pool does not create a connection, but finds an idle connection from the database connection pool. con. close () is not a disconnect, but is returned to the connection pool.
Note that we should not only return connection resources, but alsoPreparedstatement and ResultSet Resources
We also need to develop a good habit of using Preparedstatement instead of statement, because Preparedstatement contains some compiled SQL statements, which can improve the execution speed.

5. Optimal connection pool configuration Selection

The use and use of the two words are completely different. Our goal is not only to use the thread pool or database connection pool, but to use them to maximize the power and efficiency of the server to achieve the best.
In this case, we need to select parameters based on our server configuration information ~.
First, let's take a look at the parameters we set in the code.

Set the size of the initial connection pool
DbSource. setInitialPoolSize (1 );

Sets the minimum number of connections in the connection pool.
DbSource. setMinPoolSize (2 );

Set the maximum number of connections in the connection pool
DbSource. setMaxPoolSize (10 );

Used to control the number of Preparedstatement
DbSource. setMaxStatements (50 );

Maximum idle time. If no connection is used within 60 seconds, it is lost. If it is set to 0, it will never be discarded.
DbSource. setMaxIdleTime (60 );
These are common and key parameters.

Key parameter settings
1. Minimum number of connections
The database connection that is maintained by the connection pool. We need to continuously test the minimum number of connections based on actual usage. If it is set to a large value, there will be many idle connections, wasting resources.

2. Maximum number of connections
The maximum number of connections in the connection pool. Generally, when the connection value is greater than the minimum number of connections, the connection pool will create a new connection to accept the connection. The set timeout time is for the newly created connection, when connections outside the minimum number of connections are used, they are not immediately released, but stay at MaxIdleTime. If the connection is not connected again at MaxIdleTime, the connection is released. If the maximum number of connections is exceeded, the new connection is added to the waiting queue.

3. MaxIdleTime: specifies the maximum idle time. This parameter must be set based on actual conditions.

If the minimum number of connections differs greatly from the maximum number of connections, the first connection is the most profitable, and the connection speed is very fast, and the next connection will be slightly slower, because the connection needs to be created.

We usually set the size of the pool, such as the thread pool. First, we need to determine whether it is CPU-intensive or I/O-intensive. If it is CPU-intensive, if we set the thread pool to be almost the same as the number of CPU cores, because a large amount of computing at this time, if the number of threads is set to be too large, the time consumed by thread switching is the main cost. If it is IO-intensive, the number of threads must be greater than the number of CPUs, because the thread may be blocked at IO, it is highly efficient to switch other threads to continue execution during blocking.

The access to the database is IO-intensive, because separate threads are also created internally to connect to the database. Therefore, similar to the thread pool, the formula for calculating the number of IO-intensive threads in the thread pool is as follows:
Number of threads = number of CPU cores/(1-blocking coefficient). The blocking coefficient should also be analyzed based on our actual situation.
Generally, the number of threads is several times the number of cores.

I will give a brief introduction here. The above description is based on my existing knowledge and materials, not all of which are correct. I hope you can take a closer look at it, if any part is wrong, I 'd like to point it out. Thank you ~