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).
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.
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.
in Java Stack Servers, you can control the number of connections in the advanced configurations:
The values between square brackets are the defaults.
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.
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.
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.
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
Now lets look at what happens in each stack when you start up your Application Server and start serving requests.
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.
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.
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.
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
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
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?