Question

Database Connections

We see a delta in the number of connections at the DB as compared with what the app server reports. Moreover, the number of idle connections do not appear to track with what is defined in app server config. It appears that a number of DB connections are established during startup of the app server which are not reported by the app server. That number of connections appears to remain fairly constant. Has anyone experienced similar behavior?

There are an initial N connections, as many as 40, at startup of Tomcat and the pega application. These initial connections are not reported by the app server. A small number of those N connections remain active at any given time (usually N/3 or less). When we describe active we are referring to an amount of traffic between the app server and the DB. Connections timeout eventually - a few almost every minute - to be immediately replaced by new sessions. The number of N connections is fairly constant (+/-5 maybe), but 2/3 of the connections are idle/unused/abandoned at any given time. The implication of this appears to be that pega is not closing some of these connections; idle but not closed.

  • Linux: Oracle Linux Server release 7.6
  • Tomcat 8.5
  • Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  • JDBC version supported by the driver 4.1
  • Connections defined in $TOMCAT/conf/context.xml
  • maxTotal="50"
    maxIdle="20"
    minIdle="15"
    initialSize="5"
  • App server metrics are sourced from Tomcat Mbeans

***Edited by Moderator Marissa to update platform capability tags; add SR Details****

Group Tags

Comments

Keep up to date on this post and subscribe to comments

April 10, 2019 - 6:23am

Pega depends on Tomcat connection pool implementation (in your case, it is DBCP it appears - which is the default Tomcat connection pool option). Look for Tomcat documentation on troubleshooting any connection leaks. You can also try tomcat connection pool (which is different from default DBCP) to see if the behavior is different: https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html. The key is to specify the factory attribute:

factory

factory is required, and the value should be org.apache.tomcat.jdbc.pool.DataSourceFactory

 

FYI, all Pegacloud environments uses the tomcat connection pool (instead of DBCP) due to its superior performance in general.

April 10, 2019 - 4:14pm
Response to KevinZheng_GCS

Thanks. This is very helpful.

We changed config to use the Tomcat connection pool. It does appear to improve performance as you pointed out. However, we are seeing the same number of open connections at Oracle. How many open connections does the database say it has after Tomcat is restarted in your case? Not what Tomcat says, from the DB view how many connections are open? Also, I'm assuming you are using Postgres instead of Oracle. Is that correct? Do you know if there are Oracle settings that could cause connections to remain open? 

Pega
September 20, 2019 - 12:27am
Response to KevinZheng_GCS

Hello.  Is this factory datasource attribute recommended for all Resources defined in context.xml?

September 20, 2019 - 7:40am
Response to coblm

Yes

April 10, 2019 - 9:27pm

In general you should  focus on the tomcat connection pool and allow backend database having enough available connections (e.g., in postgresql, the parameter is 'max_connections' whereas Oracle it is 'processes').  In setting these db parameters you should be generous as they tend to be hard limit (i.e., if your max_connections is set to 100 and you configure your connection pool to 300, anything over 100 will be refused by the db). We set max_connections to at least 1000 to avoid the situation that tomcat connection pool cannot establish actual db connections, especially for multi-node systems. There are a lot of parameters in the link I sent controlling actual connections in the pool - e.g., minIdle/maxIdle/macActive etc. A starting example of the connection pool should look like this (using Oracle as example see the validationQuery):

type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
minActive="30"
maxActive="200"
minIdle="10"
maxIdle="30"
maxWait="30000"
validationQuery="select 1 from dual"
testOnBorrow="true"
initialSize="30"

April 11, 2019 - 11:19am

From the Tomcat doc, the link you referenced, we tested with the following. It does include validationQuery as you suggest.  I was interested to see if the Abandoned settings would force a timeout and disconnect. However, there are no log entries and no change in the number of connections/TNS Listener processes on the Oracle side. I know we need to remove logAbandoned as it affects performance. I was hoping we might see something in testing. 

Do you have experience with the testWhileIdle setting? Wondering if setting this to false would not activate the connection. I'll test that.

I can modify some of the timeouts. However, I'd expect that we would see the number of TNS Listener processes reduce over time even if the timeouts were longer than needed. 

 <Resource name="jdbc/PegaRULES"
        auth="Container"
        type="javax.sql.DataSource"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        driverClassName="oracle.jdbc.OracleDriver"
        url="XXX"
        username=""
        password=""
        maxActive="15"
        maxIdle="10"
        minIdle="5"
        initialSize="5"
        maxWaitMillis="-1"
        validationQuery="select 1 from dual"
        testOnBorrow="true"
        testWhileIdle="true"
        testOnReturn="false"
        minEvictableIdleTimeMillis="30000"
        timeBetweenEvictionRunsMillis="300000"
        numTestsPerEvictionRun="5"
        removeAbandonedTimeout="60"
        removeAbandoned="true"
        logAbandoned="true"
    />

April 11, 2019 - 12:24pm

I'd started trying to put together a test class. Not identical to what you're seeing, but in the same direction. I'm not sure if you can find anything of re-use here.

This is connecting through a local JMX so I don't need credentials. You may do in your test-bed.

Inconjunction with: -

 

C:\workspace\POJO>more DBConnectionTest.properties
dbclass=
dburl=jdbc:oracle:thin:@localhost:1521:MYSID
dbusername=myuser
dbpassword=mypassword
dbmonitoreduser=PEGA722DATA
jmxdatasource=Catalina:type=DataSource,context=/prweb,host=localhost,class=javax.sql.DataSource,name=\"jdbc/PegaRULES\"
oldjmxdatasource=Catalina:type=DataSource,path=/prweb,host=localhost,class=javax.sql.DataSource,name=\"jdbc/PegaRULES\"
debugenabled=true

C:\workspace\POJO>