Queries executing time is taking longer for all queries irrespective of any MF when concurrent users starts working parallel. Its in Mendix Cloud

0
Queries executing time is taking longer for all queries irrespective of any MF when concurrent users starts working parallel. Its in Mendix Cloud . Application gets down saying ERROR - ConnectionBus: Opening JDBC connection to Some(dbpf2fhqioaw8z0re.co6tarrr5izg.eu-central-1.rds.amazonaws.com:5432) failed with SQLState: null Error code: 0 Message: Cannot get a connection, pool error Timeout waiting for idle object Retrying...(1/4) Before the above error below issues can be seen in live logs. 5:45:34 PMAPPWARNINGConnectionBus_Queries: Query executed in 10 seconds and 193 milliseconds: SELECT "Table Name1", 5:45:35 PMAPPWARNINGConnectionBus_Queries: Query executed in 11 seconds and 749 milliseconds: SELECT "Table Name 3", 5:45:35 PMAPPWARNINGConnectionBus_Queries: Query executed in 10 seconds and 156 milliseconds: SELECT "Tablename2", 5:45:35 PMAPPWARNINGConnectionBus_Queries: Query executed in 10 seconds and 374 milliseconds: SELECT "Tablename5",   So my question here is why queries are taking longer time, even it is a simple query  (tested with 2-3 users start working  in parallel)? It is not because the MF are not optimized. This is happening usually when load increases. So is this because of the environment database plan memory being low? Find the details below: 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  
asked
3 answers
1

Hi Rajeeb,

The message “Query executed in x seconds and y milliseconds:” also includes the time spent waiting for a free connection to the database. If there are 50 concurrent connections and they are all busy processing a database query, the 51st query will take longer.

The cause might be the sizing/plan for the environment, but if the problem starts occurring when only 2 or 3 people are working, the underlying issue is probably something else. Before increasing the environment plan I would personally start by doing more investigation on microflow performance and page structure. If a page has many widgets or complex structures like multiple nested list views, it could be that simply showing a page already occupies several database connections, leaving less room for microflows to use the remaining resources.

Note: there is an addon available in the Mendix appstore that can assist in identifying the cause for performance issues, but it is not for free. If you are interested you should check out Mendix Application Performance Diagnostics ( https://appstore.home.mendix.com/link/app/6127/ )

answered
0

You can compare this to a highway with a number of lanes, everylane allows for a number of vehicles to pass (queries to be executed). If all lanes  (connections) are occupied congestion will occur. And queries will have to wait.

More connections (or more lanes) means more queries that can be executed parallel on the database but also more load on the CPU and Memory. A general rule of thumb is that per 1GB of database 50 connections would be a good setting.

When fixing these kind of issues it is best to first look at:

- Why does the congestion occur, are there any queries taking a very long time / heavy load on the database to complete. Can these be optimized?

- Can the connection pooling limit be increased with the current Database plan

- Increase database plan

More traffic means more congestion, but optimizing the traffic means you can do more with less resources.

EDIT:

When debugging these kind of scenario’s a few options are to look at the monitoring:

- https://docs.mendix.com/developerportal/operate/trends-v4#Trends-dbmxruntimepgstatactivity and try to pinpoint which actions are starting when the connections skyrocket.

- https://docs.mendix.com/refguide/tricky-custom-runtime-settings#3-1-database-settings-common-settings do a testing round with a very low LogMinDurationQuery  setting meaning, a lot of queries that take longer than x milliseconds will be logged. Analyze these queries, what is causing this.

- There is also other tooling such as APD (formerly APM) https://docs.mendix.com/addons/apd-addon/ for application perfomance monitoring / diagnostics. Getting insights via this add-on (or in any other way) on which queries 1) run often 2) take long to respond is very useful

With kind regards,

Stephan

 

answered
0

Performance issue like this depend on so many factors: how much data is in the database, what queries are being executed on the database, how are the microflows set up and many more.

That is why i find it hard to comment on a question like this, except for making the statement that this never happened to me before without either having misconfigured an on-premise installation, an overly complex application, a huge data set or general network failures.

Options is see are to involve a developer with specific experience in performance, a hosting specialist (in case of on premise) or create a Mendix support ticket.

answered