Database-Connection Pooling

Reference Post Link: https://www.cnblogs.com/xdp-gacl/p/4002804.html

One: The disadvantage of not getting connections directly to the database through connection pooling

Each time a user requests a link to the database, the database creation connection typically consumes a relatively large amount of resources and is created longer. Assuming that the site 100,000 visits a day, the database server needs to create 100,000 connections, greatly wasting the resources of the database, and it is very easy to cause database server memory overflow, extension machine. As shown in the following:

Two: Using connection Pooling Optimizer Performance 2.1: What is a database connection pool

Database connection pool is a kind of limited critical expensive resource, which is especially prominent in multi-user Web application, and the management of database connection can significantly affect the scalability and robustness of the whole application, and affect the performance of the program. Database connection pool is formally addressed to this issue .

The database connection pool is responsible for allocating, managing, and freeing the database connection, which allows the application to reuse an existing database connection instead of re-establishing one . as shown in the following:

When the database connection pool is initialized, a certain number of database connections are created in the connection pool, and the number of database connections is set by the minimum number of database connections. Regardless of whether these database connections are used, The connection pool will always be guaranteed to have at least so many connections. The maximum number of database connections for the connection pool limits the maximum number of connections that this pool can occupy, and these requests are added to the wait queue when the number of connections requested by the application to the connection pool exceeds the maximum number of connections.

The minimum number of connections and the maximum number of connections for a database connection pool are set to take into account the following factors:

1. Minimum connections: Is the database connection that the connection pool keeps, so if the application has a small amount of database connections, there will be a lot of wasted database connection resources.
2. Maximum connections: Is the maximum number of connections the connection pool can request, and if the database connection request exceeds the number of times, subsequent database connection requests are added to the wait queue, which can affect future database operations
3. If the minimum number of connections differs greatly from the maximum number of connections: Then the first connection request will take profit, and then the connection request that exceeds the minimum number of connections is equivalent to establishing a new database connection. However, these database connections that are larger than the minimum number of connections are not released immediately after they are used. He will be placed in the connection pool waiting for reuse or after a space timeout is released.

Three: Open source database connection pool

Many Web servers (Weblogic, WebSphere, Tomcat) now provide the implementation of Datasoruce, which is the implementation of the connection pool. usually we put the implementation of DataSource, according to its English meaning is called the data source, the data source contains the database connection pool implementation.
There are also open source organizations that provide a separate implementation of the data source:

• DBCP Database Connection Pool
• C3P0 Database Connection Pool

After using the database connection pool, in the actual development of the project there is no need to write code to connect to the database, directly from the data source to get the database connection.

3.1. DBCP Data source

DBCP is an open source connection pool implementation under the Apache Software fund, and to use the DBCP data source, the application should add the following two jar files to the system:

• Commons-dbcp.jar: Implementation of connection pooling
• Commons-pool.jar: Dependency libraries for connection pooling implementations

The connection pool for Tomcat is implemented with this connection pool. The database connection pool can be used either in combination with the application server or independently by the application.

3.2. Join the DBCP connection pool in the application

1. Importing related JAR Packages
Commons-dbcp-1.2.2.jar, Commons-pool.jar
2, in the class directory to add dbcp configuration file: dbcpconfig.properties

The configuration information for the dbcpconfig.properties is as follows:

#连接设置driverClassName =com.mysql.jdbc.driverurl=jdbc:mysql://localhost:3306/jdbcstudyusername=rootpassword=xdp# <!--Initialize connection-->initialsize=10# maximum number of connections maxactive=50#<!--maximum idle connection-->maxidle=20#<!--minimum idle connection-->minidle=5 #<!--Timeout Wait time in milliseconds of 6000 milliseconds/1000 equals 60 seconds-->maxwait=60000#jdbc the Connection property property that accompanies the driver when the connection is established must be in such a format: [property name =property;] #注意: "User" The two attributes with "password" are explicitly passed, so there is no need to include them here. connectionproperties=useunicode=true;characterencoding=utf8# Specifies the auto-commit (Auto-commit) state of the connection created by the connection pool. Defaultautocommit=true#driver default Specifies the read-only (read-only) state of the connection created by the connection pool. #如果没有设置该值, the "setreadonly" method will not be called. (Some drivers do not support read-only mode, such as Informix) defaultreadonly= #driver default Specifies the transaction level (transactionisolation) of the connection created by the connection pool. #可用值为下列之一: (Details visible Javadoc. ) none,read_uncommitted, read_committed, Repeatable_read, serializabledefaulttransactionisolation=read_uncommitted

3.3. C3P0 Data source

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. C3P0 data sources are used more in project development.

The difference between C3P0 and DBCP

1. DBCP does not automatically reclaim idle connection functions
2. C3P0 has automatic recycle idle connection function

3.4. Join the C3P0 connection pool in the application

1. Importing related JAR Packages
C3p0-0.9.2-pre1.jar, Mchange-commons-0.2.jar, if you are working with an Oracle database, you also need to import C3p0-oracle-thin-extras-0.9.2-pre1.jar
2, in the class directory to add c3p0 configuration file: C3p0-config.xml

The configuration information for the C3p0-config.xml is as follows:

　

<?xml version= "1.0" encoding= "UTF-8"?><!--C3p0-config.xml must be located under the classpath of the private static Combopooleddatasource DS;    static{try {ds = new Combopooleddatasource ("MySQL");    } catch (Exception e) {throw new Exceptionininitializererror (e); }}--><c3p0-config> <!--c3p0 Default (default) configuration, if in code "Combopooleddatasource ds = new Combopooleddatasource (); "This means using the default (default) configuration information for C3P0 to create the data source-<default-config> <property name=" Driverclass "&GT;COM.MYSQL.J Dbc.        driver</property> <property name= "Jdbcurl" >jdbc:mysql://localhost:3306/jdbcstudy</property>                <property name= "User" >root</property> <property name= "password" >XDP</property> <property name= "acquireincrement" >5</property> <property name= "Initialpoolsize" >10</prope rty> <property name= "minpoolsize" >5</property> <property name= "Maxpoolsize" >20</prop erty&Gt </default-config> <!--C3p0 's named configuration If in code "Combopooleddatasource ds = new Combopooleddatasource (" MySQL ");" This means that using the name is MySQL configuration information to create the data source--<named-config name= "MySQL" > <property name= "Driverclass" >c om.mysql.jdbc.driver</property> <property name= "Jdbcurl" >jdbc:mysql://localhost:3306/jdbcstudy</ property> <property name= "user" >root</property> <property name= "password" >xdp</prope rty> <property name= "acquireincrement" >5</property> <property name= "Initialpoolsiz E ">10</property> <property name=" minpoolsize ">5</property> <property name=" Maxpoolsi Ze ">20</property> </named-config></c3p0-config>

3.5 Druid Database Connection pool usage

Java mode configuration

DataSource = new Druiddatasource ();        Datasource.setdriverclassname ("Com.mysql.jdbc.Driver");        Datasource.setusername ("root");        Datasource.setpassword ("11111111");        Datasource.seturl ("Jdbc:mysql://127.0.0.1:3306/jspdemo");        Datasource.setinitialsize (5);        Datasource.setminidle (1);        Datasource.setmaxactive (ten);        Enable the Monitoring statistics function datasource.setfilters ("stat");
For MySQL datasource.setpoolpreparedstatements (false);

Spring Configuration

<bean id= "DataSource" class= "Com.alibaba.druid.pool.DruidDataSource" init-method= "Init" destroy-method= "Close"             >       <!--basic property URL, user, password----      <property name= "url" value= "${jdbc_url}"/>       <property name= "use Rname "value=" ${jdbc_user} "/>       <property name=" password "value=" ${jdbc_pa             ssWOrd} "/>       <!--configuration initialization size, MIN, max--and       <property name= "InitialSize" value= "1"/>       <property name= "MinI Dle "value=" 1 "/>       <property name=" maxactive "value=" '/>  &nbsp ' ;    <!--configuration Get connection Wait time-out-      <property name= "Maxwait" value= "60000"/> &NBsp;     <!--How long does the configuration interval take to detect idle connections that need to be closed, in milliseconds-and       <property name= "Timebetweenevictionrunsmillis" value= "60000"/>       & lt;!  --Configure the minimum time for a connection to survive in the pool, in milliseconds-      <property name= "Minevictableidletimemillis" Value= "300000"/>       <property name= "validationquery" value= "select ' X '"/& Gt       <property name= "Testwhileidle" value= "true"/>     &n             Bsp <property name= "Testonborrow" value= "false"/>       <property name= "Testonr Eturn "value=" false "/>       <!--open Pscache, and specify the size of Pscache on each connection-- & nbsp;    <property name= "poolpreparedstatements" value= "true"/>       <property name= ' maxpoolpreparedstatementperconnectionsize ' value= '/>    &nbsp  ;  <!--Configuration Monitoring statistics interception filters-      <property name= "Filters" value= "stat"/> </bean>

　　

　　

Database-Connection Pooling