I have looked in to DB connection related issues in Mendix a lot. I dont have resources that I can share, but I can probably share my experience.
- DB connections are made when application need to make a Database call (obvious)
- When the DB connection is made it will be active until the connection is released
- Then the connection will move to idle state (sometimes the connection might also be in “idle in transaction” state)
- Based on the settings we do with Mendix, these connections are released by some jobs (refer https://docs.mendix.com/refguide/tricky-custom-runtime-settings#4-1-connection-pooling)
- When the connection is closed it goes to available connections pool, then it can be reused when needed
Now about the problem
- When lot of connections are made by your application with in short period of time and not released, then request has to wait for new connection, if it wait for the set time then the connection time out happens
- Connection may not be released, if the database calls are long (due to complex queries), or if the system is too busy due to all connections being active and doing something.
- Sometimes, this will also lead to java memory issues, because lot of requests waiting consumes lot of memory
- When application is running out of memory, you can see that in your logs clearly, because it does not reach the maximum in a second. You will see logs that reports the number of connections active and it keeps decreasing to 0. If the connections are 0 for a while, then you will get the issue, which is also something you see in Metrics.
- If application is running in Mx Cloud, you can ask the support to provide the database query log and check which query is taking too much time and work on that piece of functionality to fix the issue
- Mostly the prescribed 50 connections are enough, if need more, then you must also increase your INFRA. But this is not advisable most of the time. Again DEV team is right people to think about this.
Hope this helps
This is intended. The number of connections can also be threads that are idle. They are kept open on purpose so its faster to make an actual connection if needed.
To identify possible problems, you need to also check the metrics below that show the amount of queries, memory usage etc. If a lot of time are high all the time then there could be a problem , or your environment needs to be upgraded.