Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-65901

'tomcat' JDBC pool settings contain incomplete settings causing confusion

    Details

    • Story Points:
      1
    • Fix Priority:
      3

      Description

      Liferay comes with default settings for supported JDBC connection pools in portal.properties, including Tomcat JDBC pool. These stock settings currently are:

          jdbc.default.maxActive=100
          jdbc.default.minIdle=10
          jdbc.default.removeAbandonedTimeout=3600
      
          jdbc.default.jdbcInterceptors=org.apache.tomcat.jdbc.pool.interceptor.ConnectionState
          jdbc.default.jmxEnabled=true
          jdbc.default.fairQueue=true
          jdbc.default.timeBetweenEvictionRunsMillis=30000
          jdbc.default.useEquals=false
      

      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:

          jdbc.default.maxActive=100
          jdbc.default.minIdle=10
      
          # 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. 
          jdbc.default.removeAbandoned=true
          jdbc.default.removeAbandonedTimeout=3600
          # Enable logging only if you can live with its performace hit, see pool's documentation for details.
          #jdbc.default.logAbandoned=true
      
          jdbc.default.jdbcInterceptors=org.apache.tomcat.jdbc.pool.interceptor.ConnectionState
          jdbc.default.jmxEnabled=true
          jdbc.default.fairQueue=true
          jdbc.default.useEquals=false
      
          # 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.
          #jdbc.default.testWhileIdle=true
          #jdbc.default.timeBetweenEvictionRunsMillis=30000
          ## Make sure to tweak your test query based on the DB engine you are using. Some suggestions can be found for example here: https://stackoverflow.com/questions/3668506/efficient-sql-test-query-or-validation-query-that-will-work-across-all-or-most
          #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.
          #jdbc.default.testOnBorrow=true
          
      

      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

        Attachments

          Activity

            People

            • Votes:
              1 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Days since last comment:
                3 years, 13 weeks, 1 day ago

                Packages

                Version Package