Foreman Subscriptions page is extremely slow

Problem:

Hello Foreman community,
since a few releases I have the problem, that our Foreman web UI is extremely slow, when we try to have a look at our RHEL subscriptions.
My workaround until now was, to change the location from “any” to another location, but as we updated our Foreman/Katello now to the latest version (3.8.0/4.10), I wanted to have a more detailed look at this issue, because it was not solved by the new Puma version.

In the production.log I can see, that calling the URI /katello/api/v2/organizations/1 takes over 10 Minutes, until a result is delivered:

2023-11-13T07:24:17 [I|app|bda0fe75] Started GET “/katello/api/v2/organizations/1” for 10.248.47.218 at 2023-11-13 07:24:17 +0100
2023-11-13T07:24:17 [I|app|bda0fe75] Processing by Katello::Api::V2::OrganizationsController#show as JSON
2023-11-13T07:24:17 [I|app|bda0fe75] Parameters: {“api_version”=>“v2”, “id”=>“1”}
2023-11-13T07:38:46 [I|app|bda0fe75] Rendered /usr/share/gems/gems/katello-4.10.0/app/views/katello/api/v2/organizations/show.json.rabl (Duration: 869602.9ms | Allocations: 6381626)
2023-11-13T07:38:46 [I|app|bda0fe75] Completed 200 OK in 869620ms (Views: 869383.9ms | ActiveRecord: 226.1ms | Allocations: 6388409)

The Puma worker process, that is handling the request is stuck at 100% CPU utilization.
Unfortunately I don’t see any error messages in the logs (production.log, syslog, PostgreSQL), that give a hint, what the issue of the problem could be.

Can you please give me a hint, how I can analyze this issue?

Kind Regards,
Carsten

Expected outcome:
The Subscriptions page is loaded within seconds and not minutes

Foreman and Proxy versions:
Foreman 3.8.0, Katello 4.10

Distribution and version:
Oracle Linux 8.8

As I am no Ruby specialist, I searched for the “Allocations” information on the Internet and found out, that it seems to be an indication, that there have been over 6 million objects, that have been processed by the call. Is there a possibility to see, what these 6 million objects are (or where they come from)?

Hello Foreman community,
this morning I enabled the debug logging for our whole Foreman and saw in the production.log, that in the problem scenario, the rendered JSON contains nearly everything for the organization including the networks (we “only” have around 29k networks in Foreman, as we want to be able to deploy systems in our locations) and the locations (we have 172 of them in Foreman).

It seems to me, that the huge amount of networks are the cause for the performance issue.

Unfortunately I did not find the part in the code, where they are collected from the database to see, if the bottleneck comes from the DB or something else.
Can you please give me a hint, in which files I have to look for this?

Kind regards,
Carsten