Support Article

DB performance problems on login - login fails

SA-34199

Summary



Upon system startup the user sees "connection is closed" errors when building the application cache. Subsequently an attempt to log in to the database causes the thread to hang waiting for a response from the database


Error Messages



Connection is closed error

2017-02-22 13:16:44,178 [ch Thread t=008be2d8] [ STANDARD] [ ] [ PegaRULES:07.10] (che.appcentric.AppShortcutImpl) ERROR your_node Administrator@pega.com -
Failed to infer a shortcut from an AppCentric entry.
<redacted stack>
Caused by:
com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Connection is closed.
at com.ibm.ws.rsadapter.jdbc.WSJdbcWrapper.createClosedException(WSJdbcWrapper.java:122)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.activate(WSJdbcConnection.java:2901)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.getAutoCommit(WSJdbcConnection.java:2017)
at com.pega.pegarules.data.internal.access.DatabaseConnectionImpl.getAutoCommit(DatabaseConnectionImpl.java:806)
at com.pega.pegarules.data.internal.access.ConnectionManagerImpl.returnConnection(ConnectionManagerImpl.java:2221)
... 92 more

Thread hang on DB read

3XMTHREADINFO "WebSphere WLM Dispatch Thread t=008afe88" J9VMThread:0x000000500918E000, j9thread_t:0x00000050092BB4D0, java/lang/Thread:0x0000005190040B30, state:R, prio=5
3XMJAVALTHREAD (java/lang/ThreadgetId:0xD3, isDaemon:true)
3XMTHREADINFO1 (native thread ID:0x2F6A9A00, native priority:0x5, native policy:UNKNOWN, vmstate:R, vm thread flags:0x00000001)
3XMCPUTIME CPU usage total: 5.433000000 secs
3XMHEAPALLOC Heap bytes allocated since last GC cycle=474086728 (0x1C41FD48)
3XMTHREADINFO3 Java callstack:
4XESTACKTRACE at java/net/SocketInputStream.socketRead0(Native Method)
4XESTACKTRACE at java/net/SocketInputStream.read(SocketInputStream.java:164(Compiled Code))
4XESTACKTRACE at java/net/SocketInputStream.read(SocketInputStream.java:134(Compiled Code))
4XESTACKTRACE at com/ibm/db2/jcc/t4/y.b(y.java:211(Compiled Code))
4XESTACKTRACE at com/ibm/db2/jcc/t4/y.c(y.java:320(Compiled Code))
4XESTACKTRACE at com/ibm/db2/jcc/t4/y.c(y.java:433(Compiled Code))
4XESTACKTRACE at com/ibm/db2/jcc/t4/y.v(y.java:1201(Compiled Code))
4XESTACKTRACE at com/ibm/db2/jcc/t4/bb.b(bb.java:40)
4XESTACKTRACE at com/ibm/db2/jcc/t4/p.a(p.java:32)
4XESTACKTRACE at com/ibm/db2/jcc/t4/ub.i(ub.java:145)
4XESTACKTRACE at com/ibm/db2/jcc/am/wo.kb(wo.java:2163)
4XESTACKTRACE at com/ibm/db2/jcc/am/xo.wc(xo.java:3676(Compiled Code))
4XESTACKTRACE at com/ibm/db2/jcc/am/xo.b(xo.java:4483(Compiled Code))
4XESTACKTRACE at com/ibm/db2/jcc/am/xo.mc(xo.java:2852(Compiled Code))
4XESTACKTRACE at com/ibm/db2/jcc/am/xo.execute(xo.java:2827(Compiled Code))
5XESTACKTRACE (entered lock: com/ibm/db2/jcc/t4/b@0x000000518CD68128, entry count: 1)
4XESTACKTRACE at com/ibm/ws/rsadapter/jdbc/WSJdbcPreparedStatement.pmiExecute(WSJdbcPreparedStatement.java:958(Compiled Code))
4XESTACKTRACE at com/ibm/ws/rsadapter/jdbc/WSJdbcPreparedStatement.execute(WSJdbcPreparedStatement.java:625(Compiled Code))
4XESTACKTRACE at com/pega/pegarules/data/internal/access/DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:581(Compiled Code))
4XESTACKTRACE at com/pega/pegarules/data/internal/access/DatabaseImpl.list(DatabaseImpl.java:6139(Compiled Code))
4XESTACKTRACE at com/pega/pegarules/data/internal/access/DatabaseImpl.executeRDB(DatabaseImpl.java:6613)
4XESTACKTRACE at com/pega/pegarules/data/internal/access/DatabaseImpl.executeRDB(DatabaseImpl.java:6569)
4XESTACKTRACE at com/pega/pegarules/data/internal/access/DatabaseImpl.executeRDB(DatabaseImpl.java:6550)


Steps to Reproduce



Not Applicable


Root Cause



A defect or configuration issue in the operating environment

During login we are attempting to create the application cache and the query is running beyond the limit set for the connect pool property on the PegaRULES JDBC data source.
The reason that this is so is that the database is doing full index scans on all the indices and tables in the query.
This was discovered by looking at the execution plan for the query that timed out.

Resolution



Make the following change to the operating environment:

Update statistics on the entire database in order to allow the database optimizer to make the correct choices when accessing the indexes and tables.

Published February 24, 2017 - Updated March 1, 2017

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.