Bug: Orgs and Locs + Job Templates are broken in 3.18 (probably 3.17 as well)

Support
17

Foreman Job Templates Slowness — Investigation Summary

Problem

Non-admin users with many org/location taxonomies experienced 15-60s page loads (and 500 timeouts) on /job_templates, /hosts, and /job_invocations/new. Admin loads were 1-2s.

Example user ajackman: 1,975 direct user-level taxonomies (1,814 Locations + 161 Organizations).

Root cause

1. N+1 in Taxonomix::ClassMethods#get_taxonomy_ids

def get_taxonomy_ids(taxonomy, method)
  Array(taxonomy).map { |t| t.send(method) + t.ancestor_ids }.flatten.uniq
end

Called by Authorizer on every .authorized(...). Each taxonomy in the user’s scope ran two separate DB queries (subtree_ids + ancestor_ids). For ajackman: 1,975 taxonomies × 2 methods × 2 taxonomy types = ~3,960 identical SQL queries per page load.

No open upstream Foreman issue tracks this specifically. PR #7451 added a 2-min Rails.cache wrapper but the default MemoryStore is per-Puma-worker, so most requests still pay the full cost.

Fixes applied

1. Monkey-patch initializer

/usr/share/foreman/config/initializers/taxonomy_child_ids_batch.rb replaces Taxonomix::ClassMethods#get_taxonomy_ids with a single batched SQL query using ancestry LIKE predicates joined by OR. Cost is O(1) DB round-trips regardless of taxonomy count.

Deployed to foreman-ui-01 and foreman-ui-02.

Results

Console benchmark (JobTemplate.authorized(:view_job_templates).to_a for ajackman):

Before After
Elapsed 7.99s 0.40s
Taxonomies queries ~3,960 ~20
Template count 24 24 ✓

Production (GET /job_templates for ajackman):

Metric Before After
Total 12,590 ms ~1,500 ms
Views 4,929 ms 615 ms
ActiveRecord 3,782 ms 146 ms
Allocations 7,469,799 1,200,081

~8x faster overall, 26x faster AR, 6x fewer allocations.

@aruzicka fixed, see above. Now to see if I can get the Hosts page to load faster.

1 Like