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
Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production
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 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.
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?
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):
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.