/api/hosts?include_permissions=true takes ~5 minutes to load after upgrade 3.19 → 5.0.0 (100 hosts, ~2.8s/host)

WerVa · September 10, 2026, 7:03am

Foreman version: 5.0.0
Katello version: 5.0.0
OS: Rocky Linux 9
Upgrade path: 3.19.x → 5.0.0 (sequential, via foreman-installer, no skipped versions)

Summary:
After upgrading from Foreman 3.19 to 5.0.0, the Hosts page (/new/hosts) hangs on “Loading…” for 4–5 minutes on every load. This was previously fast (a few seconds) before the upgrade.

Environment scale:
306 managed hosts total. The request below returns only 100 of them (default entries_per_page = 100, one page of results) — the slowness is not related to having few hosts, it’s per-page.

Installed Packages

ansible-collection-theforeman-foreman-5.12.0-1.el9.noarch
ansiblerole-foreman_scap_client-0.5.0-2.el9.noarch
candlepin-4.8.4-1.el9.noarch
candlepin-selinux-4.8.4-1.el9.noarch
dynflow-utils-2.0.1-2.el9.x86_64
foreman-5.0.0-1.el9.noarch
foreman-cli-5.0.0-1.el9.noarch
foreman-debug-5.0.0-1.el9.noarch
foreman-dynflow-sidekiq-5.0.0-1.el9.noarch
foreman-installer-5.0.0-1.el9.noarch
foreman-installer-katello-5.0.0-1.el9.noarch
foreman-obsolete-packages-1.19-1.el9.noarch
foreman-postgresql-5.0.0-1.el9.noarch
foreman-proxy-5.0.0-1.el9.noarch
foreman-redis-5.0.0-1.el9.noarch
foreman-release-5.0.0-1.el9.noarch
foreman-selinux-5.0.0-1.el9.noarch
foreman-service-5.0.0-1.el9.noarch
foreman-vmware-5.0.0-1.el9.noarch
katello-5.0.0-1.el9.noarch
katello-certs-tools-2.10.0-2.el9.noarch
katello-client-bootstrap-1.7.9-3.el9.noarch
katello-common-5.0.0-1.el9.noarch
katello-repos-5.0.0-1.el9.noarch
katello-selinux-5.2.0-2.el9.noarch
pulpcore-obsolete-packages-1.5.0-4.el9.noarch
pulpcore-selinux-2.2.0-2.el9.x86_64
puppet-foreman_scap_client-1.0.0-3.el9.noarch
python3.12-pulp-ansible-0.29.9-1.el9.noarch
python3.12-pulp-cli-0.39.1-4.el9.noarch
python3.12-pulp-container-2.27.11-1.el9.noarch
python3.12-pulp-deb-3.8.2-2.el9.noarch
python3.12-pulp-glue-0.39.1-3.el9.noarch
python3.12-pulp-python-3.27.6-1.el9.noarch
python3.12-pulp-rpm-3.35.3-3.el9.noarch
python3.12-pulpcore-3.105.19-1.el9.noarch
rubygem-dynflow-2.0.1-2.el9.noarch
rubygem-foreman-tasks-13.2.0-1.fm5_0.el9.noarch
rubygem-foreman_ansible-18.0.1-1.fm5_0.el9.noarch
rubygem-foreman_bootdisk-24.0.0-1.fm5_0.el9.noarch
rubygem-foreman_fog_proxmox-0.25.0-1.fm5_0.el9.noarch
rubygem-foreman_maintain-1.16.0-2.el9.noarch
rubygem-foreman_openscap-13.0.0-2.fm5_0.el9.noarch
rubygem-foreman_remote_execution-17.0.0-1.fm5_0.el9.noarch
rubygem-foreman_remote_execution-cockpit-17.0.0-1.fm5_0.el9.noarch
rubygem-foreman_snapshot_management-4.3.0-1.fm5_0.el9.noarch
rubygem-foreman_webhooks-6.0.0-2.fm5_0.el9.noarch
rubygem-hammer_cli-5.0.0-1.el9.noarch
rubygem-hammer_cli_foreman-5.0.0-1.el9.noarch
rubygem-hammer_cli_foreman_ansible-0.8.3-2.fm5_0.el9.noarch
rubygem-hammer_cli_foreman_openscap-0.2.5-2.fm5_0.el9.noarch
rubygem-hammer_cli_foreman_remote_execution-0.4.4-2.fm5_0.el9.noarch
rubygem-hammer_cli_foreman_tasks-0.0.25-2.fm5_0.el9.noarch
rubygem-hammer_cli_katello-5.0.2-1.el9.noarch
rubygem-katello-5.0.0-1.el9.noarch
rubygem-pulp_ansible_client-0.29.9-2.el9.noarch
rubygem-pulp_certguard_client-3.105.15-2.el9.noarch
rubygem-pulp_container_client-2.27.11-1.el9.noarch
rubygem-pulp_deb_client-3.8.2-2.el9.noarch
rubygem-pulp_file_client-3.105.15-2.el9.noarch
rubygem-pulp_ostree_client-2.6.1-2.el9.noarch
rubygem-pulp_python_client-3.27.6-2.el9.noarch
rubygem-pulp_rpm_client-3.35.3-2.el9.noarch
rubygem-pulpcore_client-3.105.15-2.el9.noarch
rubygem-smart_proxy_dynflow-1.0.0-2.fm5_0.el9.noarch
rubygem-smart_proxy_pulp-3.4.0-2.fm5_0.el9.noarch

Reproduction:

GET /api/hosts?include_permissions=true&search=

(default entries_per_page = 100, so this returns 100 hosts)

production.log output:

Completed 200 OK in 283736ms (Views: 168932.0ms | ActiveRecord: 114634.1ms | Allocations: 10772123)

So for ~100 hosts:

Total: 283.7s (~2.8s per host)
ActiveRecord (SQL): 114.6s
Views/rendering (RABL serialization): 168.9s
~10.77 million object allocations in a single request

Already ruled out:

Rails.cache (Redis) confirmed working correctly via manual write/read test — not a cache backend issue.
No blocked/waiting queries found in pg_stat_activity / pg_locks during the slow window.
strace on the Puma workers during the slow request shows continuous, evenly-distributed DB and Redis traffic across the full ~5 minute window (no single long stall) — rules out a single slow external call (e.g. to a VMware compute resource) as the sole cause.
Fully disabled foreman_openscap (removed the plugin loader file, confirmed removed from Foreman::Plugin.all, restarted foreman) — no improvement, response time unchanged.
db:migrate:status shows all migrations up.

lzap · September 10, 2026, 9:16am

Hey, that is a lot of commit to bisect. I am shooting in the dark with AI here, but if you can, apply this harmless patch that adds some logging statements around Taxonomy and enable debug mode:

gist.github.com

https://gist.github.com/lzap/a6095c67b2bf139e38960a519b40e35f

diagnostic-permissions-logging.patch

diff --git a/app/helpers/authorize_helper.rb b/app/helpers/authorize_helper.rb
index 000a8368f..252758cff 100644
--- a/app/helpers/authorize_helper.rb
+++ b/app/helpers/authorize_helper.rb
@@ -21,6 +21,12 @@ module AuthorizeHelper
     if object.nil?
       user.allowed_to?({ :controller => controller_name, :action => action, :id => id, :user_id => user_id }) rescue false
     else
+      if params.has_key?(:include_permissions)
+        Foreman::Logging.logger('permissions').debug do

This file has been truncated. show original

Then share log of the one request that was slow with me. Remember to revert the patch or reinstall foreman RPM package.

Additionally, what would help is enabling log_min_duration_statement to some higher value like 200ms temporarily, making a request and then sharing PostgreSQL log with the list of slow queries.

WerVa · September 10, 2026, 10:11am

Two separate reproductions, virtually identical results:

Completed 200 OK in 265516ms (Views: 155788.9ms | ActiveRecord: 109490.0ms | Allocations: 11358427)
Completed 200 OK in 265317ms (Views: 155351.9ms | ActiveRecord: 109739.5ms | Allocations: 11346288)

Permissions debug log (patch applied), one of the two runs:

taxonomy id expansion called 10903 times, only 6170.9ms total combined — count is the problem, not per-call cost.
Breakdown: 10050 calls are ForemanOpenscap (Policy + ScapContent), rest is SmartProxy/User/Domain/Subnet etc. in low hundreds.
All 10050 ForemanOpenscap calls: method=subtree_ids input=1 — called individually per-object, not batched. Clear N+1.
Zero cache hit/cache miss log lines anywhere for these calls.

PostgreSQL slow query log (log_min_duration_statement=200), same window:
Same query repeated dozens of times per run, 1–2.6s each:

SELECT "foreman_openscap_scap_contents".* FROM "foreman_openscap_scap_contents" WHERE (foreman_openscap_scap_contents.id IN (11)) AND "foreman_openscap_scap_contents"."id" = $1 ORDER BY foreman_openscap_scap_contents.title

Fetches the full row including a ~4.7MB scap_file bytea column, for the single scap_content (id=11) assigned to 255/306 hosts — repeatedly, uncached.

WerVa · September 10, 2026, 10:17am

It looks like that problem:

github.com/theforeman/foreman_openscap

Refs #39448 - improve performance on host index page (#614)

master ← ATIX-AG:refs_39448

opened 02:44PM - 30 Jun 26 UTC

sbernhard

+326 -19

During testing, we found out multiple issues so that the performance of the host… index page is really bad. Before this commit, using '/api/hosts?include_permissions=false&search=' took about 1min 30seconds. Simply returning "false" for "relevant?" decreased the access to 6seconds. With this commit, we are back at 9seconds - which is acceptable hopefully. Replace call to ".to_status" with ".status" to use the already calculated value of the complianace report. host_has_hostgroup_policies? is pretty fast because it answers a yes/no question with a cheap EXISTS query instead of loading and deduplicating all applicable policies in Ruby.

lzap · September 10, 2026, 10:33am

A very brief session with Luna LLM pinpointed host.policies.present change to host.combined_policies.present which pulls larger field scap_file:

Also finding some contributors like 8128a42 or 9b4995a. It does appear that the PR you mention is aiming to fix the problem. Well, at least we have our names we can ping: @aruzicka @Shimon_Shtein @Bernhard_Suttner

WerVa · October 5, 2026, 8:25am

Hey, any news on when you’ll be fixed? @aruzicka