Why does my Tomcat only open 8 JDBC connections
When setting up database connections in Tomcat 8 for some reason Tomcat is not doing what I configured in the context.xml file, resulting in my connections run out, resulting in app server side resource content (BLOCKED / WAITING) ... I always have 8 connections (show processlist in mariadb / mysql) after pool initialization. My configuration contains a minimum of 10 connections and a maximum of 100 connections.
I've tested different configurations, but this is no different, which is strange at least. The .xml context is used differently, it won't be able to connect to the database at all.
What's going on here? Why only 8 connections?
Software versions: - MySQL JDBC driver: latest (5.1.35) - Java 1.8.0_05
I also observed with my previous setup: Tomcat 7, Java 1.7, older MySQL JDBC drivers, MySQL instead of MariaDB. Therefore the issue is not directly related to the version.
Show exit from the process list (display 8 processes):
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+---------------+---------------+-------------------+---------+------+-----------+-----------------+----------+
| 71153 | root | localhost | vnitdatacollector | Query | 0 | init | show processlist| 0.000 |
| 73473 | vnit_datacoll | virt005:58585 | vnitdatacollector | Sleep | 0 | | NULL | 0.000 |
| 73474 | vnit_datacoll | virt005:58586 | vnitdatacollector | Sleep | 0 | | NULL | 0.000 |
| 73482 | vnit_datacoll | virt005:58606 | vnitdatacollector | Query | 0 | update | INSERT INTO ... | 0.000 |
| 73483 | vnit_datacoll | virt005:58607 | vnitdatacollector | Sleep | 0 | | NULL | 0.000 |
| 73485 | vnit_datacoll | virt005:58618 | vnitdatacollector | Query | 0 | query end | INSERT INTO ... | 0.000 |
| 73487 | vnit_datacoll | virt005:58624 | vnitdatacollector | Sleep | 0 | | NULL | 0.000 |
| 73488 | vnit_datacoll | virt005:58634 | vnitdatacollector | Sleep | 0 | | NULL | 0.000 |
| 73489 | vnit_datacoll | virt005:58637 | vnitdatacollector | Query | 7 | update | INSERT INTO ... | 0.000 |
+-------+---------------+---------------+-------------------+---------+------+-----------+-----------------+----------+
I have the following context.xml configuration:
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource
name="jdbc/dbaccess"
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="100"
minIdle="10"
maxWait="1000"
initialSize="10"
minEvictableIdleTimeMillis="5000"
testOnBorrow="true"
validationQuery="SELECT 1"
timeBetweenEvictionRunsMillis="5000"
testWhileIdle="true"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
username="some_user"
password="{the password}"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://dbserver:3306/dbaccess?useFastDateParsing=false&jdbcCompliantTruncation=false"
/>
<Resource
name="mail/emailconnection"
auth="Container"
type="javax.mail.Session"
mail.smtp.host="some.stmp.server"
/>
</Context>
After a short period, the following thread issue appears:
"Thread-495" #517 daemon prio=5 os_prio=0 tid=0x00007f678c040800 nid=0x1642 waiting on condition [0x00007f67848f4000]
java.lang.Thread.State: WAITING (parking)
at sun.misc.Unsafe.park(Native Method)
- parking to wait for <0x00000000f21933f0> (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2039)
at org.apache.tomcat.dbcp.pool2.impl.LinkedBlockingDeque.takeFirst(LinkedBlockingDeque.java:582)
at org.apache.tomcat.dbcp.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:439)
at org.apache.tomcat.dbcp.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:360)
at org.apache.tomcat.dbcp.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:118)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1412)
at com.hipersonik.util.ServiceLocator.getConnection(ServiceLocator.java:32)
source to share
Quoting from " Tomcat Expert: Setting jdbc-pool for high-concurrency ":
When Tomcat reads
type="javax.sql.DataSource"
, it will automatically configure its repackaged DBCP unless you specify a different factory. A factory object is what creates and configures the connection pool itself.
It turns out that the DBCP package is simply ignoring a number of settings. Adding the following line to the resource config context.xml
, getting a better response in the database:
<Resource
....
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
....
/>
Show list of processes in mysql and then immediately shows desired behavior.
source to share
By default (i.e. if you don't set the factory parameter of your resource) tomcat7 uses commons dbcp1.
Tomcat also provides an alternative pool implementation (tomcat jdbc connection pool) which you can use by setting factory = org.apache.tomcat.jdbc.pool.DataSourceFactory to your resource
Tomcat8 defaults to commons dbcp2 which has different names for some very important configuration options (see https://tomcat.apache.org/migration-8.html#Database_Connection_Pooling ) as dbcp1 (and tomcat jdbc as it has mostly same configuration options as commons dbcp1).
So, before tomcat8, you didn't have to pay attention to which connection pool you were using due to the compatibilty configuration. With tomcat8, you have to pay attention.
Tomcat connection pool documentation: https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html
Default documentation for dbcp2: https://tomcat.apache.org/tomcat-8.0-doc/jndi-resources-howto.html#JDBC_Data_Sources
source to share