Liferay comes with default settings for supported JDBC connection pools in portal.properties, including Tomcat JDBC pool. These stock settings currently are:
Out of these, I find removeAbandonedTimeout and timeBetweenEvictionRunsMillis particularly confusing. Since they are included in portal.properties, users might get a feeling that that particular feature is enabled and does not require any additional settings. But the opposite is true.
removeAbandonedTimeout=3600 has no effect on the pool unless removeAbandoned=true is also set (default to false). With current settings, users might think connections held by Liferay (or any another app using the pool) will be reclaimed to the pool by force after being busy for more than 1 hour, but they won't. I suggest we either remove the setting of removeAbandonedTimeout or also add removeAbandoned=true.
Same is true for timeBetweenEvictionRunsMillis=30000. It might seem like idle connections are periodically tested and re-created (if needed) in the pool every 30 seconds. But that is not true unless properties testWhileIdle=true and validationQuery=SELECT 1 (or validatorClassName=...) are also set. I suggest we either remove the setting of timeBetweenEvictionRunsMillis or also add testWhileIdle=true and validationQuery=SELECT 1. Unfortunately, the query needs to be tweaked based on DB server, e.g. for Oracle, you need to use SELECT 1 FROM dual, so we might need to provide only some instructions and leave it up to the user to enable the feature.
I think this would be a good set of baseline settings for Tomcat JDBC pool:
# Make sure connections used for 1 hour or more are removed from the pool and recreated. This will prevent connection leaks if some application using the pool will not correctly release the connections.
# Enable logging only if you can live with its performace hit, see pool's documentation for details.
# If your DB server is closing long-open connection which are not used, or if you simply want a robust pool which can handle unexpected DB server outages, enable automatic testing of idle connections while they are waiting in the pool.
## Make sure to tweak your test query based on the DB engine you are using. Some suggestions can be found for example here: https: #jdbc.default.validationQuery=SELECT 1
# The safest way to guarantee reliable database availability is to let the pool test every connection before Liferay borrows it from the pool. It comes with a small cost of running an extra query each time. Make sure to set 'jdbc.default.validationQuery' as well, see above for instructions. Make sure the query is fast and reliable.
All information was taken from the Tomcat JDBC pool docs, as linked from portal.properties: https://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html