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.
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 Proprietary information hidden.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****
Pegasystems Inc.
US
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.
Air Force AFRL
US
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?
Pegasystems Inc.
US
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"
Air Force AFRL
US
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.
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"
/>
PEG
GB
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>