Question
Pegasystems Inc.
JP
Last activity: 18 Feb 2021 20:40 EST
Commons DBCP vs Tomcat JDBC Pool
Hi,
There was a pretty big change made in Tomcat as to JDBC connection pool facility. Tomcat has updated its default connection pooling library to Tomcat JDBC Pool ("tomcat-jdbc-pool") in Tomcat 8. Tomcat 7 and earlier version used Commons DBCP ("commons-dbcp"). For example, Tomcat JDBC Pool uses "maxTotal" instead of "maxActive" to limit the number of connections. In order to enable this new feature Tomcat JDBC Pool, you need to add factory attribute in your context.xml. In summary,
- If you are using Tomcat 7, you can use maxActive & maxWait.
- If you are using Tomcat 8, you should use maxTotal & maxWaitMillis.
- If you are using Tomcat 8, you should also add factory attribute as follows: factory="org.apache.tomcat.jdbc.pool.DataSourceFactor"
Question:
Is my understanding above correct? If yes, here is my proposal to installation guide creation team:
Hi,
There was a pretty big change made in Tomcat as to JDBC connection pool facility. Tomcat has updated its default connection pooling library to Tomcat JDBC Pool ("tomcat-jdbc-pool") in Tomcat 8. Tomcat 7 and earlier version used Commons DBCP ("commons-dbcp"). For example, Tomcat JDBC Pool uses "maxTotal" instead of "maxActive" to limit the number of connections. In order to enable this new feature Tomcat JDBC Pool, you need to add factory attribute in your context.xml. In summary,
- If you are using Tomcat 7, you can use maxActive & maxWait.
- If you are using Tomcat 8, you should use maxTotal & maxWaitMillis.
- If you are using Tomcat 8, you should also add factory attribute as follows: factory="org.apache.tomcat.jdbc.pool.DataSourceFactor"
Question:
Is my understanding above correct? If yes, here is my proposal to installation guide creation team:
The guide should state above as a note because it is pretty important (if you misuse wrong parameters, for example misusing maxActive in Tomcat 8, then your setting will be ignored). Currently 7.4 installation guide talks about only maxActive instead of maxTotal, and 8.3 guide does not state the importance of factory attribute - I have already seen a couple of customers misusing incorrect parameters. I am suspecting that this confusion may even cause another severe error (https://collaborate.pega.com/question/anyone-managed-successfully-install-82).
[7.4 installation guide]
Isn't this wrong?
[8.3 installation guide]
It will be better to write some explanation of the importance of factory attribute in Tomcat 8x or people may just copy old settings from prior version environment and forget it.
Let me know if my suggestion makes sense or not.
Thanks,
***Edited by Moderator: Lochan to update SR details***
Accepted Solution
Updated: 2 Sep 2020 19:56 EDT
Pegasystems Inc.
JP
My original post was wrong. I will correct it in this reply.
1. "Tomcat has updated its default connection pooling library to Tomcat JDBC Pool ("tomcat-jdbc-pool") in Tomcat 8."
This is incorrect. Tomcat 8+ uses DBCP2 (https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP_2)_Configurations)
2. "Tomcat JDBC Pool uses "maxTotal" instead of "maxActive" to limit the number of connections."
This is also incorrect. "maxTotal" and "maxWaitMillis" are meant for DBCP 2. Tomcat JDBC Pool still uses "maxActive" and "maxWait" just like old DBCP (1.x).
Once again, let me summarize to the best of my knowledge:
My original post was wrong. I will correct it in this reply.
1. "Tomcat has updated its default connection pooling library to Tomcat JDBC Pool ("tomcat-jdbc-pool") in Tomcat 8."
This is incorrect. Tomcat 8+ uses DBCP2 (https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP_2)_Configurations)
2. "Tomcat JDBC Pool uses "maxTotal" instead of "maxActive" to limit the number of connections."
This is also incorrect. "maxTotal" and "maxWaitMillis" are meant for DBCP 2. Tomcat JDBC Pool still uses "maxActive" and "maxWait" just like old DBCP (1.x).
Once again, let me summarize to the best of my knowledge:
| No | Name | Jar | Package | Tomcat version | Pega recommendation |
|---|---|---|---|---|---|
| 1 | Commons DBCP (1.x) | tomcat-dbcp.jar | org.apache.commons.dbcp.BasicDataSource | 7x | No |
| 2 | Tomcat JDBC Pool | tomcat-jdbc.jar | org.apache.tomcat.jdbc.pool.BasicDataSource | included from 7.0.19 but not used unless you explicitly specify it | Not really |
| 3 | Commons DBCP 2 (2.x) | tomcat-dbcp.jar | org.apache.commons.dbcp.dbcp2.BasicDataSource | 8x, 9x | Yes |
1. Commons DBCP (1.x) is developed by Apache Commons project. It is single threaded and performance is slow. This is the default in Tomcat 7x. This is not recommended to use any more. As for parameter, "maxActive", "maxIdle", and "maxWait" are used. "maxTotal" and "maxWaitMillis" can't be used.
2. Tomcat JDBC Pool is developed by Tomcat project and meant to be an alternative for Commons DBCP. This has been included in its binary since Tomcat 7.0.19 but never be used unless you add factory=“org.apache.tomcat.jdbc.pool.DataSourceFactory” in the context.xml. As for parameter, "maxActive", "maxIdle", and "maxWait" are used just like old DBCP. "maxTotal" and "maxWaitMillis" can't be used. What is problematic in Tomcat JDBC Pool is, it does NOT validate the connection in the pool by default. That means if the connection is already stale and invalid, it fails. To avoid this, you'll have to add a set of validation parameters such as "testOnBorrow" or "validationQuery" (see the end of this post).
3. Commons DBCP 2 (2.x) is upgraded from Commons DBCP (1.x). This is the default from Tomcat 8, so it is available without adding factory attribute or anything as long as you install Tomcat 8. New parameter, "maxTotal" and "maxWaitMillis" are used. "maxActive", and "maxWait" can't be used.
Pega stance:
Only Pega 8.3 installation guide instructs to use Tomcat JDBC Pool and it caused confusion. If Tomcat JDBC Pool is used, which is okay, but then you always need to consider adding a set of validation parameters, which were not included in the guide. Also, "maxTotal" and "maxWaitMillis" were mistakenly used. Tomcat JDBC Pool uses "maxActive" and "maxWait".
Pega 8.4 installation guide fixed these issues and now it is based on DBCP 2 (2.x). There is no need to add factory attribute in the context.xml, or complex validation parameters. It is now very simple!
FYI, below are the examples of validation parameters. These have to be considered if you want to use Tomcat JDBC Pool.
- testOnBorrow
- testOnConnect
- testOnReturn
- testWhileIdle
- validationQuery
- validationInterval
- removeAbandoned
- removeAbandonedTimeout
Please let me know if I am wrong with anything.
Thanks,
Updated: 2 Sep 2020 19:35 EDT
Pegasystems Inc.
US
Agreed completely! In fact, we have seen some system instability due to high load if not using tomcat connection pool. All Pegacloud environments use tomcat connection pool as well as it is the default setup for Pega container deployment. Suggest contact documentation team.
Pegasystems Inc.
JP
Thanks Kevin, I raised an "SR-D43040: JDBC pool setting instruction is incorrect in installation guide" and it was acknowledged as a documentation bug. Support said it will be updated. Thank you for your help!
Computershare
US
Kevin,
1. I'm on 7.2.2 Tomcat 9. Are aware of any issues/restrictions for using these new settings on that combination?
2. How can I monitor my connection pool to see if it is sized correctly? I have not found any documentation to help me set up the pool based on the number of users, and processes.
Thanks
John
Accepted Solution
Updated: 2 Sep 2020 19:56 EDT
Pegasystems Inc.
JP
My original post was wrong. I will correct it in this reply.
1. "Tomcat has updated its default connection pooling library to Tomcat JDBC Pool ("tomcat-jdbc-pool") in Tomcat 8."
This is incorrect. Tomcat 8+ uses DBCP2 (https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP_2)_Configurations)
2. "Tomcat JDBC Pool uses "maxTotal" instead of "maxActive" to limit the number of connections."
This is also incorrect. "maxTotal" and "maxWaitMillis" are meant for DBCP 2. Tomcat JDBC Pool still uses "maxActive" and "maxWait" just like old DBCP (1.x).
Once again, let me summarize to the best of my knowledge:
My original post was wrong. I will correct it in this reply.
1. "Tomcat has updated its default connection pooling library to Tomcat JDBC Pool ("tomcat-jdbc-pool") in Tomcat 8."
This is incorrect. Tomcat 8+ uses DBCP2 (https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP_2)_Configurations)
2. "Tomcat JDBC Pool uses "maxTotal" instead of "maxActive" to limit the number of connections."
This is also incorrect. "maxTotal" and "maxWaitMillis" are meant for DBCP 2. Tomcat JDBC Pool still uses "maxActive" and "maxWait" just like old DBCP (1.x).
Once again, let me summarize to the best of my knowledge:
| No | Name | Jar | Package | Tomcat version | Pega recommendation |
|---|---|---|---|---|---|
| 1 | Commons DBCP (1.x) | tomcat-dbcp.jar | org.apache.commons.dbcp.BasicDataSource | 7x | No |
| 2 | Tomcat JDBC Pool | tomcat-jdbc.jar | org.apache.tomcat.jdbc.pool.BasicDataSource | included from 7.0.19 but not used unless you explicitly specify it | Not really |
| 3 | Commons DBCP 2 (2.x) | tomcat-dbcp.jar | org.apache.commons.dbcp.dbcp2.BasicDataSource | 8x, 9x | Yes |
1. Commons DBCP (1.x) is developed by Apache Commons project. It is single threaded and performance is slow. This is the default in Tomcat 7x. This is not recommended to use any more. As for parameter, "maxActive", "maxIdle", and "maxWait" are used. "maxTotal" and "maxWaitMillis" can't be used.
2. Tomcat JDBC Pool is developed by Tomcat project and meant to be an alternative for Commons DBCP. This has been included in its binary since Tomcat 7.0.19 but never be used unless you add factory=“org.apache.tomcat.jdbc.pool.DataSourceFactory” in the context.xml. As for parameter, "maxActive", "maxIdle", and "maxWait" are used just like old DBCP. "maxTotal" and "maxWaitMillis" can't be used. What is problematic in Tomcat JDBC Pool is, it does NOT validate the connection in the pool by default. That means if the connection is already stale and invalid, it fails. To avoid this, you'll have to add a set of validation parameters such as "testOnBorrow" or "validationQuery" (see the end of this post).
3. Commons DBCP 2 (2.x) is upgraded from Commons DBCP (1.x). This is the default from Tomcat 8, so it is available without adding factory attribute or anything as long as you install Tomcat 8. New parameter, "maxTotal" and "maxWaitMillis" are used. "maxActive", and "maxWait" can't be used.
Pega stance:
Only Pega 8.3 installation guide instructs to use Tomcat JDBC Pool and it caused confusion. If Tomcat JDBC Pool is used, which is okay, but then you always need to consider adding a set of validation parameters, which were not included in the guide. Also, "maxTotal" and "maxWaitMillis" were mistakenly used. Tomcat JDBC Pool uses "maxActive" and "maxWait".
Pega 8.4 installation guide fixed these issues and now it is based on DBCP 2 (2.x). There is no need to add factory attribute in the context.xml, or complex validation parameters. It is now very simple!
FYI, below are the examples of validation parameters. These have to be considered if you want to use Tomcat JDBC Pool.
- testOnBorrow
- testOnConnect
- testOnReturn
- testWhileIdle
- validationQuery
- validationInterval
- removeAbandoned
- removeAbandonedTimeout
Please let me know if I am wrong with anything.
Thanks,