Analysis of database connection pool (II.)

On a blog, mainly simple introduction of the ordinary database connection process and the cost of resources, and simply talk about the next connection pool, this article mainly to look at the use of database connection pool and its optimal configuration.

Total directory:
-1.数据库连接过程是怎样的？
-2.连接所占用的资源有哪些？
-3.连接池简介
-4. Use of connection pooling
-5. Optimal Connection Pool configuration options

Today mainly look at 4 and 5.

4. Use of connection pooling

这里我使用的是c3p0数据库连接池
A brief introduction c3p0:c3p0 is an open source JDBC connection pool that implements the data source and Jndi bindings and supports the standard extensions of the JDBC3 specification and JDBC2. The open source projects that currently use it are hibernate,spring and so on.

There are many types of connection pools, and there are different scenarios for each, so choosing the right connection pool is also a good thing ~

The comparison of data that is not using connection pooling and using connection pooling is placed first.
Loop Connected 10 times, the picture is the 10th set of data for the test.

do not use connection pooling

Using connection pooling
Red for Connection Pool section configuration information

From the picture we can clearly see that the connection pool speed is much faster than the ordinary connection, many here is not referring to the difference in how many milliseconds, but multiples of the difference.

Java Connection Pool code:

ImportCom.mchange.v2.c3p0.ComboPooledDataSource;ImportJava.awt.color.ProfileDataException;ImportJava.beans.PropertyVetoException;ImportJava.sql.Connection;ImportJava.sql.ResultSet;ImportJava.sql.SQLException;ImportJava.util.ResourceBundle;/** * Created by WWH on 15-6-10. * * Public  class dbpollclass {    Private StaticDbpollclass Dbpoll;PrivateCombopooleddatasource Dbsource;//Static code block, we start by executing the constructor load configuration information    Static{Dbpoll =NewDbpollclass (); } Public Dbpollclass(){//Set configuration information        Try{Dbsource =NewCombopooleddatasource (); 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 (Ten); Dbsource.setmaxstatements ( -); Dbsource.setmaxidletime ( -); }Catch(Propertyvetoexception e) {Throw NewRuntimeException (e); }    }//Get Connected     Public Final StaticDbpollclassgetinstance(){returnDbpoll; } Public FinalConnectiongetconnection(){Try{returnDbsource.getconnection (); }Catch(SQLException e) {Throw NewRuntimeException ("Unable to get connection", e); }    } Public Static void Main(string[] args)throwsSQLException { for(inti =0; I <Ten; i++) {LongBeginTime = System.currenttimemillis (); Connection con =NULL;Try{//Get idle connectioncon = dbpoll.getinstance (). getconnection ();//Execute SQL statement and return query results                //resultset rs = con.createstatement (). ExecuteQuery ("SELECT * from UserInfo");                //Use PreparedStatement without using statementPreparedStatement PST = con.preparestatement ("SELECT * from UserInfo"); ResultSet rs = Pst.executequery ();//Output query Results                 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 connectionCon.close (); }            }LongEndtime = System.currenttimemillis (); System.out.println ((i+1) +"Time is:"+ (endtime-begintime)); }    }}

From the code we can and clearly see the use of the connection pool is very simple, the code I will connect the configuration of the pool and other information written to the constructor, in fact, in reality, we generally write in the C3p0-config.xml, database connection pool configuration file, and then put in the SRC directory.
The use of connection pooling is essentially the same as for a normal JDBC connection database. Parameters are similar
The connection of the connection pool is not to create a connection, but to find an idle connection from the middle of the database connection pool, con.close () is not disconnected, but is returned to the connection pool.
And note that we will not only return connection resources, but also return the resources of PreparedStatement and resultset.
We also need to develop a good habit of using preparedstatement instead of statement, because PreparedStatement contains some compiled SQL statements that can improve execution speed.

5. Optimal connection pool configuration options

The use and use of two words is very different, our goal is not only to use the thread pool or database connection pool, but by using them to maximize the server power and efficiency to achieve optimal.
This will be based on our own server configuration information to select the parameters ~.
First look at what parameters we set in the code.

Setting the initial connection pool size
Dbsource.setinitialpoolsize (1);

Set the minimum number of connections in the connection pool
Dbsource.setminpoolsize (2);

Set the maximum number of connections in a connection pool
Dbsource.setmaxpoolsize (10);

To control the number of PreparedStatement.
Dbsource.setmaxstatements (50);

Maximum idle time, unused connection in 60 seconds is discarded, set to 0 will never discard
Dbsource.setmaxidletime (60);
The above are common and key parameters.

about the most critical parameter settings
1. Minimum number of connections
The connection pool maintains a database connection. The size of the minimum number of connections we have to according to the actual use of continuous testing to determine, if the setting is large there will be a lot of idle connections, wasting resources.

2. Maximum number of connections
Connection pool connection upper limit, 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, we set the time-out is for the newly created connection, when the use of the minimum number of connections outside the connection, they will not be immediately released, Instead, it stays maxidletime time, and if it is not connected again at maxidletime time, it is released. If the maximum number of connections is exceeded, the new connection is added to the wait queue.

3. Maximum idle time maxidletime, this must adjust the setting according to the actual situation.

If the minimum number of connections and the maximum number of connections varies greatly, the first connection to the most profitable, the connection is very fast, and then to a slightly slower, because to create a connection.

We generally set the size of the pool, such as the thread pool, first to determine whether CPU-intensive or IO-intensive, if it is CPU-intensive, then if the thread pool settings should be almost the same as the number of CPU cores, because at this time a large number of calculations, if the number of threads is set too large, then the time spent on thread switching is , if it is IO-intensive, then the number of threads is greater than the number of CPUs, because threads can block at Io, so it is more efficient to switch other threads to continue execution when blocking.

Then access to the database is IO-intensive, because the internal is also created a separate thread connection, so similar to the thread pool, the thread pool IO-intensive thread count formula is:
Number of threads = number of CPU cores/(1-blocking factor), the blocking factor should also be analyzed according to our specific situation.
The average number of threads is several times the number of cores.

I simply introduced here, said above is based on their existing knowledge and query information to describe, and not necessarily all right, I hope you take a look at the eyes, if any part of the wrong, but also hope to point out, thank you ~

Analysis of database connection pool (II.)