Tomcat JDBC Pool Usage Instructions

Maven Dependency

<dependency> <groupId>org.apache.tomcat</groupId> <artifactid>tomcat-jdbc</artifactid > <version>8.5.9</version></dependency>

The latest version is 9.0, 8.5.9 stable version is recommended

Common configuration

There are many connection pool configuration items, the following list is only recommended configuration, the project group can be increased or decreased according to their own situation

<bean id= "Dataspource"  class= "Org.apache.tomcat.jdbc.pool.DataSource"  destroy-method= "Close" >    <property name= "Driverclassname"  value= "${spring.db.driverClassName}"  />    <property name= "url"  value= "${spring.db.url}"  />     <property name= "username"  value= "${spring.db.username}"  />     <property name= "Password"  value= "${spring.db.password}"  />     <!--  The number of initial connections that were created when the connector started. The default is  10 -->    <property name= "InitialSize"  value= "${ Spring.db.initialSize} " />    <!--  Maximum number of connections   default 100 -->     <property name= "maxactive"  value= "${spring.db.maxactive}"  />     <!--  Maximum number of idle connections   default 100 -->    <property name= "MAxidle " value=" ${spring.db.maxidle} " />    <!--  Minimum number of connections, General and InitialSize consistent can be  . The default value is taken from  initialsize -->    <property name= "MinIdle"  value= "${ Spring.db.minIdle} " />    <!--connection pool When the connection is exhausted, the new request waits, the time-out returns an exception, and the milliseconds   default 30000-->     <property name= "maxwait"  value= "${spring.db.maxwait}"  />     <!--  Whether the Idle object eviction process is checked at the same time when the connection enters the idle state, by default false, dependent validationquery -->     <property name= "Testwhileidle"  value= "${spring.db.testwhileidle}"  />     <!--  An SQL statement used to verify that a connection is valid before it is returned to the caller must be a SELECT statement with at least one row of results  -->     <property name= "Validationquery"  value= "${spring.db.validationquery}"  />     <!--  Connection Verification time-out, Unit: SEC  -->    <property name= " Validationquerytimeout "&Nbsp;value= "${spring.db.validationquerytimeout}"  />    <!--  Check the time interval for connection availability, Units: milliseconds   default 30000 -->    <property name= "Validationinterval"  value= " ${spring.db.validationinterval} " />    <!--  Idle object eviction check interval per millisecond, default 5000 - ->    <property name= "Timebetweenevictionrunsmillis"  value= "${ Spring.db.timeBetweenEvictionRunsMillis} " />    <!--  The minimum time to remain idle in the pool before a connection is evicted by an idle object, in milliseconds, by default 60000 -->    <property name= " Minevictableidletimemillis " value=" ${spring.db.minevictableidletimemillis} " />     <!--  Connection pool created by default auto-commit status, default to JDBC default  -->    <property name= " Defaultautocommit " value=" ${spring.db.defaultautocommit} " />    <!--whether to use  JMX  Register Connection pool, default: true-->    &Lt;property name= "jmxenabled"  value= "${spring.db.jmxenabled}"  />    < !--  Configuration Increase query time-out setting (also configurable for slow query automatic logging, see document)  -->    <property name= " Jdbcinterceptors " value=" Querytimeoutinterceptor (Querytimeout=${spring.db.querytimeout}); Slowqueryreport (Threshold=${spring.db.threshold}) " /></bean>

Recommended parameter values: (only for items that are not very high, the project group is adjusted according to the situation)

spring.db.driverclassname=com.microsoft.sqlserver.jdbc.sqlserverdriverspring.db.url=jdbc:sqlserver:// 10.8.1.112:1433;databasename=xxx;sendstringparametersasunicode=falsespring.db.username=testspring.db.password= testspring.db.initialsize=5spring.db.maxactive=50spring.db.maxidle=30spring.db.minidle=5spring.db.maxwait= 10000spring.db.testwhileidle=truespring.db.validationquery=select 1spring.db.validationquerytimeout= 1spring.db.validationinterval=30000spring.db.timebetweenevictionrunsmillis= 10000spring.db.minevictableidletimemillis=60000spring.db.defaultautocommit=truespring.db.jmxenabled=true# Slow query timeout threshold, in seconds spring.db.querytimeout=5# slow query statements automatically record thresholds, note unit milliseconds spring.db.threshold=3000

Parameter description

Property name	Description	Default Value
Driverclassname	User name	-
Url	Password	-
Username	To establish a connection URL	-
Password	Database-driven Full class name	-
InitialSize	Number of initial connections created at connector startup	10
Maxactive	Maximum number of connections, typically the maximum number of database concurrency for general access, and is recommended for gradual tuning based on late JMX monitoring	100
Maxidle	The maximum number of idle connections, more difficult to grasp a parameter, many connection pools have also been removed this property (such as Druid), access peaks compared to the centralized system such as attendance can be set a small point of time to save most of the connection resources, too small may also cause frequent connection creation close also affect performance, Recommended general system not less than 50% of maxactive	100
Minidle	Minimum number of connections, generally consistent with initialsize	10
Maxwait	When the connection pool is exhausted, the new request waits, the time-out returns an exception, the unit milliseconds	Default 30000
Testwhileidle	Whether the connection enters the idle state is checked at the same time by the Idle object eviction process, the recommended calibration method , dependent validationquery	False
Validationquery	An SQL statement used to verify that a connection is valid before it is returned to the caller must be a SELECT statement with at least one row of results	-
Validationquerytimeout	Timeout for connection validation, in seconds, note: The pool itself does not cause the query to time out, relying entirely on the JDBC driver to force the query to time out	-
Validationinterval	Tomcatjdbc Unique Properties, check the time interval for connection availability, prevent Testonborrow and testonreturn from being checked too frequently when true, unit milliseconds	30000
Timebetweenevictionrunsmillis	Idle object eviction check interval, per millisecond	5000
Minevictableidletimemillis	The minimum time, in milliseconds, that a connection is kept idle in the pool before the eviction process is evicted by the idle object	60000
Defaultautocommit	Connection pool created by default autocommit state (JDBC default means that the Setautocommit method is not called by default)	JDBC Default value
Jmxenabled	Whether to register a connection pool with JMX	True
Jdbcinterceptors	Tomcatjdbc Unique Properties, querytimeoutinterceptor (query timeout Interceptor, attribute QueryTimeout, unit seconds, default 1 seconds), Slowqueryreport (slow query record, Attribute threshold time-out record threshold unit milliseconds, default 1000), multiple with interceptors; delimited, example: Querytimeoutinterceptor (querytimeout=5); Slowqueryreport (threshold=3000) Note: statement.setquerytimeout (seconds) is called automatically when a new statement is created. The pool itself does not allow the query to time out, relying solely on the JDBC driver to force the query to time out, and for more detailed information, see the official documentation	-
Testonborrow	Whether the connection is called, depends on the validationquery, has a certain impact on performance, is not recommended to use	False
Testonreturn	Whether the connection returns to the pool is checked, depends on the validationquery, has a certain impact on performance, is not recommended to use	False
removeabandoned	Whether to clear a connection that has exceeded the removeabandonedtimeout setting, which can be used to troubleshoot issues that are not committed by the transaction (formal environment is used with caution, has some impact on performance), deprecated, and can be replaced with QueryTimeout interceptors	False
Removeabandonedtimeout	The time that the invalid connection was cleared, combined with removeabandoned per second	60
Defaultreadonly	Whether connection pooling creates a connection is read-only, it should be explained that setting true only tells the database that the connection is read-only, makes it easier for the database to do some optimizations (such as not scheduling a database lock), and does not fail to perform the update operation. But the protection of data consistency is not strong (this is similar to spring's read-only transaction)	JDBC Default

This article is from the "Star Technology circulation" blog, please be sure to keep this source http://snowtiger.blog.51cto.com/12931578/1966602

Tomcat JDBC Pool Usage Instructions