Question

How to reduce database connections?

1

Whenever a user logs into my app, about 20 database connections are added to my environment. Apparently, it is causing a problem because my environment always operates at its max level of 50 db connections, which, in turn, use up all freeable DB memory, leading to high swap usage, long queries, up to frequent database disconnects. Yet, only 4 users are currently accessing the app, on an environment that is supposed to serve up to 100… When accessing the details of the db connections locally, I can confirm that the app opens a whole bunch of connections with queries not necessary related with the opened pages. But all of them have state=’idle’. I’m not familiar with databases, but I also find it odd that most of these connections remain even after the user has logged out. Are these symptoms of a problem I should try and investigate? If so, what can I do to troubleshoot/mitigate it?

asked 2022-02-21

Patrick Prasquier

3 answers

Ronald Catersels · Answer 1 · 2022-02-21

The database memory is your problem not how many connections you use. It seems there is so little memory left that it constantly is swapping to harddisk (which is real slow). Sollution would be to give your database more memory.

Regards,

Ronald

Xiwen Cheng · Answer 2 · 2022-02-22

Alternative to Ronald’s suggestion, which is not possible apparently in basic, you can look into reducing your data set. The more data is in your database, the more memory it requires to perform reasonably.

depending on what/how much data, you can consider to create some at runtime (use non-persistent objects) to avoid DB. Of course this could create a new set of problems.

Your app should work just fine with 100 users if the dataset is small/modest with the basic package.

Steve Hawkins · Answer 3 · 2022-02-23

It's rather a lot of imbricated dataviews/listviews/listeners, which I don't know how to optimize.

This is the place to optimize in my opinion. Nothing has the ability to generate parallel queries (xas requests) like complex assemblies of data containers and their interaction with widgets.

Our first efforts at designing screens and search pages was maybe typical for newbies as we were, involving nested data containers and listviews with groups of search fields and filter dropdowns with other listening widgets. Ha ha. We quickly spiked our resources with just a small number of users.

Then we turned to querying against what we call ‘display objects’ – non-persistable entities representing the flattened datasets after necessary joins (resolved associations). This helped performance because it removed some nesting, all the data being returned in one big fetch.

Then that solution ran out of gas for our purposes and we turned to OQL queries to generate JSON to populate a custom datagrid widget using ag-Grid (this was before DataGrid 2). For the job of querying and slinging about large datasets this solution has held up the best and we still use it.

We frequently bounce up against 600 connections with 200 users in the system. But it’s better than using 600 connections for 100. You will need 32GB of RAM to handle 600 connections by the way.