Outsystems and jboss use oracle connection pool

Outsystems and jboss use oracle connection pool

  

Hi.

our stand in outssytems 9.1 + jboss 6.4.6 eap

We have some strange issue with one of our application in outsystems.

For some unknown reason application started to use to much oracle connections, i can see them in Oracle Developer, after publishing changes in app, it creates another 20-30 connection, and then another and so on, all previous  connections are in inactive status, till Oracle close connection pool, and we need to restart outsystems and jboss.

After some research was found ucp.jar in lib directory, which should be used for that, e.g. clean or reuse oracle connections of last published app. And if to copy ucp.jar to web-inf/lib of app jar file, all goes fine, for a some time, connections doesn't increase more than 60-100, but this is not a solution,

for now we have default oracle db connection param from the box and i don't know how to solve that problem.

Hi Pavel,

The Java stack is known for having some issues with the connection pools.

What's your platform version exactly? There are some versions that fix problems with the connection pools not being released after publishing espaces.

I had this problem in a 9.0.x version that was fixed in a higher version.

Hi João.

My ver is 9.1.300.2

Actually for now solution with copy "ucp.jar" works for now. And til now we dont have that problem. May it's caused by cleaning old versions of app

Hello Joao,
We are facing connection closed issue on production environment almost for all the applications running on OUtsystems.

Outsystems is running on Oracle weblogic server which is a Java platform.The database is Oracle 11gR2.

The connection is closed: The connection is closed
eSpaceVer: 41 (Id=1139, PubId=3867, CompiledWith=10.0.302.0)
 RequestUrl: https://www.dc.gov.ae/CaseRegistration/CaseRegistrationConfirmationPage.jsf (Method: GET)
 ClassLoader: weblogic.utils.classloaders.ChangeAwareClassLoader@58d82c98 finder: weblogic.utils.classloaders.CodeGenClassFinder@59e736eb annotation: CaseRegistration@CaseRegistration.war(1490562200)
 FilePath: /CaseRegistration/CaseRegistrationConfirmationPage.jsp
 ClientIp: 11.1.1.66 X-Forwarded-For: 31.218.180.130
 Locale: ar-AE
 DateFormat: dd-MM-yyyy
 PID: 20679 ('20679@dcoutsyspr1.dxbcourts.int', Started='10/23/18 10:42:13 PM', Priv=3306Mb, Virt=5911Mb)
 TID: 5752
 Thread Name: [ACTIVE] ExecuteThread: '60' for queue: 'weblogic.kernel.Default (self-tuning)'
 JRE: 25.71-b15

The connection is closed: The connection is closed
 java.sql.SQLException: The connection is closed: The connection is closed
 at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:473)
 at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:457)
 at oracle.ucp.jdbc.proxy.JDBCConnectionProxyFactory.invoke(JDBCConnectionProxyFactory.java:307)
 at oracle.ucp.jdbc.proxy.ConnectionProxyFactory.invoke(ConnectionProxyFactory.java:50)
 at com.sun.proxy.$Proxy189.getAutoCommit(Unknown Source)
 at outsystems.hubedition.databaseabstractionlayer.adoadapters.ConnectionUtils.closeConnection(Unknown Source)
 at outsystems.hubedition.databaseprovider.oracle.transactionservice.TransactionService.createConnection(Unknown Source)
 at outsystems.hubedition.extensibility.data.transactionservice.BaseTransactionService.createConnection(Unknown Source)
 at outsystems.hubedition.extensibility.data.transactionservice.GenericTransactionManager.getConnection(Unknown Source)
 at outsystems.hubedition.extensibility.data.transactionservice.GenericTransactionManager.buildTransactionInfo(Unknown Source)
 at outsystems.hubedition.extensibility.data.transactionservice.GenericTransactionManager.getRequestTransaction(Unknown Source)
 at outsystems.internal.db.DatabaseAccessProvider.getRequestTransaction(Unknown Source)
 at oscaseregistration.managedbean.flowmainflow.ScrnCaseRegistrationConfirmationPage$FuncssPreparation.queryApplicationInformation(Unknown Source)
 at oscaseregistration.managedbean.flowmainflow.ScrnCaseRegistrationConfirmationPage.preparation(Unknown Source)
 at oscaseregistration.managedbean.flowmainflow.ScrnCaseRegistrationConfirmationPage.pageLoad(Unknown Source)
 at outsystems.hubedition.webwidgets.uicomponent.utils.ComponentUtils.invokeBeanPageLoad(Unknown Source)
 at outsystems.hubedition.webwidgets.uicomponent.os_controls.PageComponent.encodeBegin(Unknown Source)
 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:928)
 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:933)
 at com.sun.faces.application.ViewHandlerImpl.doRenderView(ViewHandlerImpl.java:268)
 at com.sun.faces.application.ViewHandlerImpl.renderView(ViewHandlerImpl.java:198)
 at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:110)
 at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:100)
 at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
 at javax.faces.webapp.FacesServlet.service(FacesServlet.java:266)
 at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:280)
 at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:254)
 at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:136)
 at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:346)
 at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:25)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:79)
 at outsystems.hubedition.webwidgets.BaseRequestStartupFilter.doFilter(Unknown Source)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:79)
 at outsystems.hubedition.webwidgets.filters.HttpContextFilter.doFilter(Unknown Source)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:79)
 at outsystems.hubedition.webwidgets.filters.CacheControlFilter.doFilter(Unknown Source)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:79)
 at weblogic.servlet.internal.RequestEventsFilter.doFilter(RequestEventsFilter.java:27)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:79)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3436)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3402)
 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
 at weblogic.servlet.provider.WlsSubjectHandle.run(WlsSubjectHandle.java:57)
 at weblogic.servlet.internal.WebAppServletContext.doSecuredExecute(WebAppServletContext.java:2285)
 at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2201)
 at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2179)
 at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1572)
 at weblogic.servlet.provider.ContainerSupportProviderImpl$WlsRequestExecutor.run(ContainerSupportProviderImpl.java:255)
 at weblogic.work.ExecuteThread.execute(ExecuteThread.java:311)
 at weblogic.work.ExecuteThread.run(ExecuteThread.java:263)

Regards,
Aravindkumar A


Hi Annamalai.

try to pass /opt/outsystems/platform/fonfiguationtool.sh

doesn't skip DB configuration.

Hello Pavel,
What parameter should I  change  on DB configuration when we run  /opt/outsystems/platform/fonfiguationtool.sh.


Regards,
Aravindkumar A 

Hi Annamalai.

Actually i think you should speak with DBA, see how it uses connection and processes,

and adjust settings in configuration tool.

 

Hi Pavel,


Actually I'm the DBA here.

I'm not getting sessions or connections closed error in the database.


Regards,
Aravindkumar A

Hi Annamalai.

How much is "connections" and "processes" set in db ? and how much in platform config ?

does it a cluster ? did you operate some load-test ?

does all outsystems user in db not 'locked' ?

Hi Pavel,

How much is "connections" and "processes" set in db ? and how much in platform config ?

Processes - 3500

Sessions - 5328

Platform config -- how can we check it?

does it a cluster ? 

No, it is a single instance DB.

did you operate some load-test ?
No.

does all outsystems user in db not 'locked' ?

Sorry, I didn't get you the question.

Regards,
Aravindkumar A

show your <outsystems install dir>/platform/server.hsconf:

  • <JDBCPoolInactivityTimeout>
  • <JDBCPoolMaxPoolSize>
  • <JDBCPoolMinPoolSize>
  • <DBIntrospectionTimeout>
  • <DBTimeout>
  • <DBUpdateTimeout>

cluster? i mean outsystems

in DB >

SELECT username, 
       account_status
  FROM dba_users;

does outsystems users ok ?

Hi Pavel,

These are the current values from server.hsconf .

<JDBCPoolInactivityTimeout>600</JDBCPoolInactivityTimeout>
    <JDBCPoolMaxPoolSize>1000</JDBCPoolMaxPoolSize>
    <JDBCPoolMinPoolSize>132</JDBCPoolMinPoolSize>
    <DBIntrospectionTimeout>90</DBIntrospectionTimeout>
    <DBTimeout>120</DBTimeout>
    <DBUpdateTimeout>1200</DBUpdateTimeout>


does outsystems users ok ?

Yes, all the outsystems users open and active.


Regards,
Aravindkumar A

Hi Annamalai.

I don't have much experienced with oracle weblogic server, but, fast run on google shows much issues with threads and oracle db, like:

https://community.oracle.com/thread/1041104?start=0&tstart=0

may be this will help.

Course, in your configs i can't see any problems, all looks fine.