Connection Pooling in OutSystems Platform

Certified
Certified

What is connection pooling and why do we use it

Connection pools work as an intermediary between the application and the Database that manages Database connections. Instead of an application opening a connection to the database and closing it when it is done with it, it will instead request a connection from the Pool and return it when it is done.


This allows us to minimize the performance penalties associated with opening and closing database connections since a new connection will only be opened if none is available and an existing connection from the Pool to the database will only be closed after a set idle period.


Each different database connection (to a different server, using a different connection pool or to a different catalog/schema) will have its own connection pool (i.e.: all connections in a connection pool must be interchangeable).

How does OutSystems implement connection pooling

OutSystems Platform uses existing implementations of the connection pool mechanisms. For .NET, it uses the ADO.NET connection pooling mechanisms. In the Java stacks, it uses Oracle UCP.


Controlling the number of connections

.NET

in .NET stack servers, you can control the number of connections by using the adequate parameters in the advanced configuration section of the Configuration Tool’s Database tab. You can control the following parameters:

  • Max Pool Size (Default 100)

  • Min Pool Size (Default 1)

more connection string options can be found here.

Java

in Java Stack Servers, you can control the number of connections in the advanced configurations:

The values between square brackets are the defaults.




How many connections can I expect

.NET

In .NET, there will exist one connection pool per DB per eSpace. Therefore you should expect to have at least the minimum number of connections configured, for each eSpace, for each frontend.


As an example, assuming that you have a minimum of 1 connection configured (the default), 10 eSpaces deployed and 2 frontends, you should expect a minimum of 20 connections to each of the Main and Session databases.


Java

In Java you will have one connection pool for all eSpaces. Therefore you should expect to have at least the minimum number of connections configured, for each frontend.


Taking the previous example, if you have a minimum of 10 connections configured (the default in Java), 10 eSpaces deployed and 2 frontends, you should expect to have a minimum of 20 connections to each of the Main and Session databases.


Maximum number of connections

Under load, as the number of parallel requests to the server increases, so will the number of database connections necessary.


Your Maximum number of connections to the DB is calculated in the same way, but replacing the minimum connections with maximum connections in the configuration. So for a similar example where you have the default of 100 DB connections per connection pool, 10 eSpaces deployed and 2 frontends, your totals would be 2000 connections in .NET and 200 for Java.



Real World Example

As a real world example, let’s take a Large Production environment with 2 frontends and 200 eSpaces and let’s have a look at the minimum and maximum values that can be expected in both .NET and Java stacks. We’ll assume default values on the configuration pool for each stack and that the Session and Main catalog/schemas are located in the same Instance of the database



 

.NET

JAVA

Frontends

2

2

eSpaces

200

200

Min. Connections

1

10

Max Connections

100

100



Now lets look at what happens in each stack when you start up your Application Server and start serving requests.


.NET

in .NET the eSpaces are not automatically loaded: instead they are loaded on first access. This means that initially you’ll have only a handful of eSpaces loading up and therefore very few DB connections. Let’s assume however that all eSpaces load up eventually: you have 200 eSpaces per frontend, and each of these will have one connection created for each of the databases it connects to (at minimum these will be the OutSystems Main Catalog and the Session Database). This adds up to 400 per frontend or 800 connections in total.


Now lets imagine one of these eSpaces is in high demand and has a peak concurrent users value of 50 in each frontend. Your connection usage now increases from 800 to 996. Why? 50 concurrent users require 50 connections to the session database and another 50 to the main catalog for the application to satisfy the request. 1 was already established on each frontend for each database so we’ll need an additional 49 per frontend per database (196).


Imagine now that this eSpace became even more popular and now receives 150 requests concurrently in each frontend: since this value exceeds the configured maximum for the connection pool, only the first 100 will be answered at first; the other 50 on each frontend will wait for connections to be available (if the wait is too long they will of course timeout). Throughout this peak in connections your total of connections to the database will be the base 800 kept open plus 99 per database (session and main) per frontend (396 connections) for a total of 1196 connections.


JAVA

In both JBoss and WebLogic Application servers all eSpaces are loaded when the App. Server starts. This means that after the initialization is finished, you will have a total of 40 connections to the database. The minimum (10) per database (again, session and main) per frontend.


As in the .NET scenario, let’s imagine one of the eSpaces grows in demand and receives 50 concurrent connections in each frontend: since each of the connection pools for the two databases is shared among all eSpaces on the application server, it’s likely that some of the initial 10 connections are already consumed by other eSpaces. So lets imagine that 5 of these are already taken: this means we’ll need an additional 45 per connection pool per frontend (180), raising your total connections to 220.


If you go even further and imagine that the eSpace now receives 150 requests on each frontend (and keep the assumption that you have 5 connections taken by other eSpaces), you’ll have a situation where the first 95 requests per frontend will be answered right away (creating 190 connections per frontend) and the remaining 55 will have to wait for a connection to be freed by the pool. During this, your total connections will be 400.



Good stuff,

BPT activities use connections from "Runtime Applications" or "OutSystems Services" connection pool?


Thanks,
Tiago.
A bit of both: Scheduler service will check metadata tables on DB for activities to run (using one "OutSystems Services" connection for several activities at a time) and then will run the activity on IIS, which will use a connection from the "Runtime Applications" Pool.

Timers work the same way: a Scheduler Service polling thread checks which timers need to run (using a connection from the "OutSystems Services" Pool) and then the code is run in IIS (using a "Runtime Applications" connection).


Hi João

How can I configure min and max pool sizes for external database connections ?

For external database connections the pool is per application module on IIS? Or all applications will share the same pool connections?

It can be configured at Service Center > Administration > Database Connections, but, at this point, there are no explicity indication about min and max pool sizes.

Note: We are using a plugin to connect to Progress Open Edge database using an ODBC driver. In this case, there are needed to do any change on the plugin?

Thanks and best regards.


Hello Juliano.

You can configure the min and max connection pool size for DB Connections in the DB Connections' Connection string.

You should check what the connection pool min/max size parameters are for the DB driver/plugin combination you're using since this will be a driver configuration, not an OutSystems configuration (OutSystems just passes along the connection string parameters).

Thank you João for your post.

I am trying to install outsystem on Redhat using weblogic as web server, with running configurationtool.sh and in crating tanent step I getting  the following Issue:

java.sql.SQLException: Cannot get Connection from Datasource: java.lang.NullPointerException With errorcode : 45257

outsystems.runtimeservices.db.jdbcpool.JDBCPoolAux.

should I create Datasource table adpater in the weblogic for outsystems? if yes what is the value of the JNDI Name for outsystems app?


Attached full error message

Hi,

in the connection pool for .NET what is the default configuration for the time a connection can be idle before being removed from the pool?


Regards,

Carlos



Hi,

Is the explanation from june 2015 still valid with the current platform version or has the architecture changed in between. Can I still calculate the necessary number of session in the same way?

Hello. 

How can I monitor the behavior of the JDBC pool on application servers? How many active, how many inactive sessions at any given time?

Kurt Vandevelde wrote:

Hi,

Is the explanation from june 2015 still valid with the current platform version or has the architecture changed in between. Can I still calculate the necessary number of session in the same way?

Hi, Kurt.

Were you able get an answer to your question?

I came across official documentation describing what João mentioned in his first post:

https://success.outsystems.com/Support/Enterprise_Customers/Maintenance_and_Operations/Connection_pools_in_the_OutSystems_platform

However, it explicitly says its for OutSystems 10. Was wondering if it also applies to OutSystems 11?


Also, does anyone knows how is the behaviour when using .NET platform server with an Oracle database (as OS main database)?


Assuming that in OS11 the same still applies for .NET, can anyone comment on the following:

Application with 1 front end server, 2 end user modules, and 6 lower layer modules. 8 modules in total. Lower layer  modules are consumed by the 2 end user modules by exposing database entities and server actions. All user requests come from the 2 end user modules.

When all modules are loaded, we will have 16 database connections (2 for each module).

I make a request to end user module A, which in turn will fetch results from an entity that is stored in one of the lower layer modules, lets call it core module B. Which connections will the platform use?

1) The two connections that belong to end user module A, since this was the one that received the request

2) Connections from both end user module A and core module B

3) Other option...



Thanks,

Tiago


Hello Tiago,


>> it explicitly says its for OutSystems 10. Was wondering if it also applies to OutSystems 11?

the above still applies in general: in .NET you will have one connection minimum per DB connection, per eSpace. What changed in O11 was that there is an additional connection pool included in all eSpaces: the Log DB connection.

In O11 we no longer have a log service, instead we have each eSpace responsible for writing its own logs to the DB. In order to do this and since the Log DB is now a separate DB, we end up with an additional DB connection per eSpace.

So each eSpace now has three connection pools by default: Main DB, Session DB and Log DB, all with a minimum of 1 connection.

Main and Session DB connection pools will grow proportionally to simultaneous user load, Log DB will grow according to how many different types of logs are being written (if the error, log and general logs are being dumped to DB at the same time, you will have three connections in the Log DB connection pool for that eSpace).


>> Also, does anyone knows how is the behavior when using .NET platform server with an Oracle database (as OS main database)?

The behavior is the same for any of the supported DB types, so .NET with Oracle DB will work the same way.


>> Assuming that in OS11 the same still applies for .NET, can anyone comment on the following: (...)

  • If the lower layer modules are referenced, the connection will be created from the end-user module that consumed it;
  • If they are service modules, the connection will be created from the lower layer module.


Hope this answers your questions.

Hello, João.

Thanks for your quick response.

Can you please share your thoughts also on the following approach:

Move lower layer modules that do not act as entry points (not have service actions, BPT, timers or end user requests) to a separate application pool with minimum pool size set to zero, since connections associated with these modules are likely to "never" be used.


Best,

Tiago


Hi Tiago,


I think you're confusing Connection Pool with Application Pool. The connection pool settings are per Database/user/connection string combination, and will be the same across all application pools.


This will unfortunately make it hard for you to set a minimum of 0 for a set of eSpaces since the configuration is common to all of them.


Hi, João.


Apologies, what I wanted to say is that we could have an application pool with a minimum number of database connections set to zero. From your reply I conclude that is not possible since the number of connections is shared among all application pools.

I can't help to think that we might have several database connections that are virtually never used since they belong to modules that are not entry modules. In your opinion, is this something that we should be worried about?

Thanks!

well, this all depends on how many eSpaces you have vs how many eSpaces fall under this category vs the number of concurrent users on your eSpaces with pages/web services, but I'd say that generally the number of DB connections that are idle in this way is a small percentage of the total.


Just remembered this: if your eSpace does not have any screens or exposed web references, it probably won't even be loaded into memory, since there aren't any user accesses to trigger IIS first load.

Thanks, João.

Your posts were very insightful!

Hi, João

Recently my production outsystems Version 10.0.823.0  on premise server often encountered this ALARM in the outsystems scheduler service. When this occurs, the email queue process becomes pending. I would need to ask the server administrator to reboot and the issue resolve temporarily. Not sure whether this is related to connection pooling.

Can help how to resolve this?

OutSystems Scheduler Service Status: Warning: LightEvents Fetcher () is in status Processing for 32 seconds which exceeds the 27 seconds warning threshold (90% of the 30 seconds timer timeout definition). 

OutSystems Log Service Status: Warning: Bulk Extension Logger is in status Flushing messages for 17 seconds which exceeds the 10 seconds warning threshold. 


In the windows server event log, it shows the following error 

Log Name:      Application

Source:        OutSystems Deployment Controller Service

Date:          7/16/2020 4:00:15 PM

Event ID:      0

Task Category: None

Level:         Warning

Keywords:      Classic

User:          N/A

Computer:      169

Description:

Error opening connection to the database: Connection request timed out

Retrying...


   at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)

   at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)

   at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword)

   at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.BaseTransactionService.GetConnection()

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.BaseTransactionService.CreateConnection(Int32 numRetries)

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.BaseTransactionService.CreateConnection(Int32 numRetries)

   at OutSystems.HubEdition.DatabaseProvider.Oracle.TransactionService.TransactionService.CreateConnection(Int32 numRetries)

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.GenericTransactionManager.BuildTransactionInfo()

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.GenericTransactionManager.AddToPoolAndReserve()

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.GenericTransactionManager.GetCommitableTransaction()

   at OutSystems.Internal.Db.DatabaseAccessProvider`1.GetCommitableTransaction()

   at OutSystems.HubEdition.RuntimePlatform.SoftwareUnitsReport.IsStoredDataValid(Int32 eSpaceId, Int32 eSpaceVersionId, String hmacKey)

   at OutSystems.Activation.Logic.Validations.ValidationSoftwareUnits.ShallRefreshObjectFeatureUsage(ObjectKey key, IDataReader reader)

   at OutSystems.Activation.Logic.Validations.AbstractHashedValidation.RecalculateStateInner(Transaction trans, Boolean ignoreCache)

   at OutSystems.Activation.Logic.Validations.ValidationSoftwareUnits.RecalculateStateInner(Transaction trans, Boolean refreshCache)

   at OutSystems.Activation.Logic.Validations.AbstractIntegerValuedValidation.RecalculateStateInner(Boolean ignoreCache)

   at OutSystems.Activation.Logic.Validations.Validation.RecalculateState(Boolean ignoreCache)

   at OutSystems.Activation.Logic.Validations.Validation.RecalculateState()

   at OutSystems.Activation.Logic.Server.RecalculateFeatureState(IValidation validation)

   at OutSystems.Activation.Logic.Server.RecalculateFeatureState(String featureId)

   at OutSystems.Activation.Logic.RefreshWorker.#t7b(String featureIdToRefresh)

   at OutSystems.Activation.Logic.RefreshWorker.#9r.#eac()

   at OutSystems.HubEdition.ServerCommon.SandboxUtils.ExecuteForSingleSandbox(String sandboxToExecute, Action action)

   at OutSystems.Activation.Logic.RefreshWorker.#r7b(String featureId, AutoResetEvent autoResetEvent)

   at OutSystems.Activation.Logic.RefreshWorker.#Br.#dac()

   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

   at System.Threading.ThreadHelper.ThreadStart()


Event Xml:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

  <System>

    <Provider Name="OutSystems Deployment Controller Service" />

    <EventID Qualifiers="0">0</EventID>

    <Level>3</Level>

    <Task>0</Task>

    <Keywords>0x80000000000000</Keywords>

    <TimeCreated SystemTime="2020-07-16T08:00:15.000000000Z" />

    <EventRecordID>5646741</EventRecordID>

    <Channel>Application</Channel>

    <Computer>169</Computer>

    <Security />

  </System>

  <EventData>

    <Data>Error opening connection to the database: Connection request timed out

Retrying...


   at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)

   at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)

   at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword)

   at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.BaseTransactionService.GetConnection()

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.BaseTransactionService.CreateConnection(Int32 numRetries)

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.BaseTransactionService.CreateConnection(Int32 numRetries)

   at OutSystems.HubEdition.DatabaseProvider.Oracle.TransactionService.TransactionService.CreateConnection(Int32 numRetries)

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.GenericTransactionManager.BuildTransactionInfo()

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.GenericTransactionManager.AddToPoolAndReserve()

   at OutSystems.HubEdition.Extensibility.Data.TransactionService.GenericTransactionManager.GetCommitableTransaction()

   at OutSystems.Internal.Db.DatabaseAccessProvider`1.GetCommitableTransaction()

   at OutSystems.HubEdition.RuntimePlatform.SoftwareUnitsReport.IsStoredDataValid(Int32 eSpaceId, Int32 eSpaceVersionId, String hmacKey)

   at OutSystems.Activation.Logic.Validations.ValidationSoftwareUnits.ShallRefreshObjectFeatureUsage(ObjectKey key, IDataReader reader)

   at OutSystems.Activation.Logic.Validations.AbstractHashedValidation.RecalculateStateInner(Transaction trans, Boolean ignoreCache)

   at OutSystems.Activation.Logic.Validations.ValidationSoftwareUnits.RecalculateStateInner(Transaction trans, Boolean refreshCache)

   at OutSystems.Activation.Logic.Validations.AbstractIntegerValuedValidation.RecalculateStateInner(Boolean ignoreCache)

   at OutSystems.Activation.Logic.Validations.Validation.RecalculateState(Boolean ignoreCache)

   at OutSystems.Activation.Logic.Validations.Validation.RecalculateState()

   at OutSystems.Activation.Logic.Server.RecalculateFeatureState(IValidation validation)

   at OutSystems.Activation.Logic.Server.RecalculateFeatureState(String featureId)

   at OutSystems.Activation.Logic.RefreshWorker.#t7b(String featureIdToRefresh)

   at OutSystems.Activation.Logic.RefreshWorker.#9r.#eac()

   at OutSystems.HubEdition.ServerCommon.SandboxUtils.ExecuteForSingleSandbox(String sandboxToExecute, Action action)

   at OutSystems.Activation.Logic.RefreshWorker.#r7b(String featureId, AutoResetEvent autoResetEvent)

   at OutSystems.Activation.Logic.RefreshWorker.#Br.#dac()

   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

   at System.Threading.ThreadHelper.ThreadStart()

</Data>

  </EventData>

</Event>


Log Name:      Application

Source:        OutSystems Platform

Date:          7/16/2020 4:00:20 PM

Event ID:      0

Task Category: None

Level:         Warning

Keywords:      Classic

User:          N/A

Computer:      169

Description:

Front-end Server 169 OutSystems Log Service Status: Warning: Bulk Request Logger is in status Flushing messages for 14 seconds which exceeds the 10 seconds warning threshold.


Message generated by eSpace ServiceCenter.

Event Xml:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

  <System>

    <Provider Name="OutSystems Platform" />

    <EventID Qualifiers="0">0</EventID>

    <Level>3</Level>

    <Task>0</Task>

    <Keywords>0x80000000000000</Keywords>

    <TimeCreated SystemTime="2020-07-16T08:00:20.000000000Z" />

    <EventRecordID>5646744</EventRecordID>

    <Channel>Application</Channel>

    <Computer>169</Computer>

    <Security />

  </System>

  <EventData>

    <Data>Front-end Server 169 OutSystems Log Service Status: Warning: Bulk Request Logger is in status Flushing messages for 14 seconds which exceeds the 10 seconds warning threshold.


Message generated by eSpace ServiceCenter.</Data>

  </EventData>

</Event>

Hi João,

good job,it's very helpful.

Hi Ronny,

I'd say look at your Database instance, because it looks like it is refusing new connections for OutSystems Services. It could be the case that the OutSystems Platform (applications  + services) is requesting more connections overall than the Database has available. If this is the case you should look into increasing the capacity of your database. 


Hope this helps!

Hi João

I will ask my production DBA to look at the outsystems platform database instances and increase the database capacity.

Thanks for the advice