Question
Anthem
US
Last activity: 13 Jul 2018 4:23 EDT
SQLServerExceptions and Productions JVM crash down.
Hi Team,
Please find our environment stack below.
PRPC : 731, Tomcat : 8.5.13 and SQL Server
Out of 30 JVM's only few of them crashes down with the below mentioned errors.Nothing suspicious in the DB and Windows Event logs.
PFB for the Tomcat Connection pool settings.
<Resource maxWaitMillis="10000" maxIdle="30" maxTotal="100" password="ABCD" username="ABCD" url="jdbc:sqlserver://ABCD\PEGA;databaseName=ABCD;SelectMethod=cursor;SendStringParametersAsUnicode=false" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" type="javax.sql.DataSource" auth="Container" factory="dbencryption.EncryptedDataSourceFactory" name="jdbc/PegaRULES"/>
Any suggestions please.
PRPC Log:
Hi Team,
Please find our environment stack below.
PRPC : 731, Tomcat : 8.5.13 and SQL Server
Out of 30 JVM's only few of them crashes down with the below mentioned errors.Nothing suspicious in the DB and Windows Event logs.
PFB for the Tomcat Connection pool settings.
<Resource maxWaitMillis="10000" maxIdle="30" maxTotal="100" password="ABCD" username="ABCD" url="jdbc:sqlserver://ABCD\PEGA;databaseName=ABCD;SelectMethod=cursor;SendStringParametersAsUnicode=false" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" type="javax.sql.DataSource" auth="Container" factory="dbencryption.EncryptedDataSourceFactory" name="jdbc/PegaRULES"/>
Any suggestions please.
PRPC Log:
7/2/18 1:52:17.000 PM 2018-07-02 13:52:17,612 [.140.80-8887-exec-12] [TABTHREAD1] [ ] [mCSNCCSuper:01.01.01] ( internal.access.Lister) ERROR gbdcompass.corp.agp.ads|xx.xx.xx.xx AF28038 - There was a problem with the database when getting a list:com.pega.pegarules.pub.database.ConnectionException: Database-General Problem encountered when getting connection for database pegadata 0 The connection is closed.DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed. | SQL Code: 0 | SQL State: null at com.pega.pegarules.data.internal.access.ExceptionInformation.createAppropriateExceptionDueToDBFailure(ExceptionInformation.java:348) ~[prprivate.jar:?] at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:333) ~[prprivate.jar:?] at com.pega.pegarules.data.internal.store.rdbms.JdbcConnectionManagerImpl.getConnectionFromDataSourceIdentity(JdbcConnectionManagerImpl.java:1032) ~[prprivate.jar:?] at com.pega.pegarules.data.internal.store.rdbms.JdbcConnectionManagerImpl.getDataStoreConnection(JdbcConnectionManagerImpl.java:3431) ~[prprivate.jar:?] at com.pega.pegarules.data.internal.store.rdbms.JdbcConnectionManagerImpl.getDataStoreConnection(JdbcConnectionManagerImpl.java:1291) ~[prprivate.jar:?] at com.pega.pegarules.data.internal.store.rdbms.JdbcConnectionManagerImpl.getDataStoreConnection(JdbcConnectionManagerImpl.java:1190) ~[prprivate.jar:?] at com.pega.pegarules.data.internal.store.ClassicConnectionBroker.getDataStoreConnection(ClassicConnectionBroker.java:103) ~[prprivate.jar:?] at com.pega.pegarules.data.internal.store.ConnectionBrokerRouter.getDataStoreConnection(ConnectionBrokerRouter.java:71) ~[prprivate.jar:?] at com.pega.pegarules.data.internal.store.AutoCommitTransaction.getDataStoreConnection(AutoCommitTransaction.java:316) ~[prprivate.jar:?] at com.pega.pegarules.data.internal.access.RDBPageResultPackager.getDataStoreConnection(RDBPag
Tomcat logs :
Tomcat below first error in logs shown SQL connection is closed while reading PR_ENGINE_JARS file from PEGA_RULES data source.
SQLException from datasource while checking for package = org/apache/http/client/( PR_ENGINE_JARS)
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
Anthem
US
Hi Ujjwal,
Could you please provide additional details.Do you recommend any additional resource connection pool parameters ? Any idea about the market standard values ?
Anthem
US
Hi Ujjwal,
Max pool size has been set to 100 as you can find in my initial post.Also maximum number of requestors would be not more than 80 and the number of DB connections would be not more than 20 at any point of time.I dont see a need to increase the connection pool further.
Any additional recommendations please.Also find our environment configuration below.
PRPC731,TOMCAT8 and MSSQL
I agree no one can provide some magic attributes or settings which can solve all the problems.But I am looking for PRPC standard/recommended Tomcat attributes and MSSQL DB settings which might help resolve the Sqlserver DB connection closed errors.
Pegasystems Inc.
PL
Hi,
The configuration depends on the way of how many connections is used by your application as well of data size revived from the DB. This means that properties like:
minActive="50"
maxActive="600"
minIdle="10"
maxIdle="300"
maxWait="60000"
initialSize="50"
can be tuned up, basis on the observation.
However, you can always add some properties which help manage the connection pool. So, the parameters are:
validationQuery="select 1 as number" - The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row.
testOnBorrow="true" - If true connections will be validated before being returned from the pool. If the validation fails, the connection is destroyed, and a new connection will be retrieved from the pool (and validated).
And this properties should be placed into server.xml in the <Resource name="jdbc/PegaRULES"
And two more attributes like:
Hi,
The configuration depends on the way of how many connections is used by your application as well of data size revived from the DB. This means that properties like:
minActive="50"
maxActive="600"
minIdle="10"
maxIdle="300"
maxWait="60000"
initialSize="50"
can be tuned up, basis on the observation.
However, you can always add some properties which help manage the connection pool. So, the parameters are:
validationQuery="select 1 as number" - The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row.
testOnBorrow="true" - If true connections will be validated before being returned from the pool. If the validation fails, the connection is destroyed, and a new connection will be retrieved from the pool (and validated).
And this properties should be placed into server.xml in the <Resource name="jdbc/PegaRULES"
And two more attributes like:
org.apache.tomcat.jdbc.pool.interceptor.ConnectionState - Caches the connection for the following attributes autoCommit, readOnly, transactionIsolation and catalog. It is a performance enhancement to avoid roundtrip to the database when getters are called or setters are called with an already set value.
org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer - Keeps track of all statements created using createStatement, prepareStatement or prepareCall and closes these statements when the connection is returned to the pool.
which suppose to be added into context.xml
<Resource name="jdbc/PegaRULESReadOnly"
....
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState; org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;"
/>
And in most of the times, those attributes help to tomcat to better manage the connection pool.
Thanks,
Lukasz