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/ )
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.
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,
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.