Database connections limit reaches with smaller loads

1
Hi,  We have two applications running on cloud with the below plan details,  App 1: Name    M Memory    2.0 GiB Database Plan Cores    1 Database Plan Space    10.0 GiB + 0.0 GiB Extra Database Plan Memory    2.0 GiB File Storage    10.0 GiB Backup Storage    20.0 GiB Failover enabled    No   App 2: Name    Strato Memory    2.0 GiB Database Plan Cores    1 Database Plan Space    5.0 GiB + 0.0 GiB Extra Database Plan Memory    1.0 GiB File Storage    20.0 GiB Backup Storage    25.0 GiB Failover enabled    No   Both the applications have considerate(and almost similar) amount of complication to them and also integration with 4 external services. The problem I’m facing is that for App2, the database connections are reaching the default limit of 50 and the subsequent queries are taking very longer to execute even for similar loads as App1.  The difference I can see apart from the usual Database size is the Database Plan Memory. What is it used for? Will this be affecting the number of database connections in any way?  Also is there a way we can explicitly close database connections after we use retrieval from database?  
asked
3 answers
2

I will try explain this based on my recent performance issues experience. 

  1. Mendix defaults the number of DB connections to 50.
  1. Primarily there are 4 status for a DB connection in Postgresql.
  • Idle 
  • Active
  • Idle in transaction 
  • Idle in transaction aborted

 

https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/connection-handling-best-practice-with-postgresql/ba-p/790883

http://www.craigkerstiens.com/2017/09/18/postgres-connection-management/

Bit more details

  1. When you program something in mendix to retrieve data from database, a connection is needed. 
  2. A connection is made, as per your programmed your app, when it is available to be given.
    1. If that query is complicated and takes more time to execute, the connection holding time will be longer.
    2. Which means, any further requests made will be queued until a connection is free and available.
  3. If lot of requests are queued, then it might slow down your application, probably leads to java memory issues too sometimes. 
  4. We cannot manage DB connections. It is all auto managed by settings, which we can also tweak sometimes. But we must be careful about changing it. For example, changing the number of DB connections, will also have negative side effects.

 

Identifying

  1. Ask for database query logs from Mendix support (if you hosted in mendix cloud). For other environments, check for yourself how to get hold of it.
  2. Look for queries that is taking longer time to execute
  3. Based on those queries, check if you can optimize your retrieval or domain model to reduce the complexity
  4. Not to forget: if you are running on a small INFRA which cannot hold 50 connections (for eg) then the server will be running out of database memory. You can look in to the metrics and very well relate this.

 

So, its all related together. Hope this information helps. 

answered
1

The number of connections is probably set to the default value of 50 in these type of environments.

Have a look here to change the settings of the database connections: https://docs.mendix.com/refguide/tricky-custom-runtime-settings#4-the-number-of-database-connections

Forcing the closure of connections is not an option AFAIK. The amount of memory available to handle database actions can influence the pool filling up slower or more quickly. An additional thing to keep in mind is the complexity of pages, pages with lots of references (set) selectors will have a negative impact on the database connections usage.

answered
0

Hello,

I’m also seeing database connections hit high values with only 2 users testing in my app. (25connections out of max 50)
How can this problem be narrowed down? What tools besides the Metrics screen can I leverage to help find the culprit causing these spikes?

Side Question:
Looking at the database connections graph in Metrics, are connections re-used across users? or is it per user? and are these connections all active? or is it both active/inactive connections being shown..

Having 25 connections open with only 2 users, would easily hit max 50 if connections remain open and another 2 users login..
At a high point, our app sees 30-40max users signed in at a time.
 

answered