Too slow hostgroup and hosts editing for non admin user

Support
1

Hi!
I facing strange issues using foreman 3.1.3. It tooks up to 5 minutes to open host, hostgroup editing form for non-admin user. Admins can open the same forms in ~30 seconds.

I have been already faced this problem at Foreman v. 1.24.3. To workaround this I implement patch from this thread Foreman 1.24.2 - very slow hostgroup view and edit for non admin user it works like a charm. But now after upgrade to 3.1.3 this problem comes back.

I’m a little confused looking for the cause of the problem and any help would be greatly appreciated. Thanks.

2

A little more info about my problem. Sorry for not attaching the info sooner.
The only anomaly I have found when non-admin user tries edit hosts:

2022-07-12T09:37:53 [D|sql|71da816a]   SQL (161851.1ms)  SELECT "environments"."id" AS t0_r0, "environments"."name" AS t0_r1, "environments"."created_at" AS t0_r2, "environments"."updated_at" AS t0_r3, "taxonomies"."id" AS t1_r0, "taxonomies"."name" AS t1_r1, "taxonomies"."type" AS t1_r2, "taxonomies"."created_at" AS t1_r3, "taxonomies"."updated_at" AS t1_r4, "taxonomies"."ignore_types" AS t1_r5, "taxonomies"."ancestry" AS t1_r6, "taxonomies"."title" AS t1_r7, "taxonomies"."description" AS t1_r8, "locations_environments"."id" AS t2_r0, "locations_environments"."name" AS t2_r1, "locations_environments"."type" AS t2_r2, "locations_environments"."created_at" AS t2_r3, "locations_environments"."updated_at" AS t2_r4, "locations_environments"."ignore_types" AS t2_r5, "locations_environments"."ancestry" AS t2_r6, "locations_environments"."title" AS t2_r7, "locations_environments"."description" AS t2_r8 FROM "environments" LEFT OUTER JOIN "taxable_taxonomies" ON "taxable_taxonomies"."taxable_type" = $1 AND "taxable_taxonomies"."taxable_id" = "environments"."id" LEFT OUTER JOIN "taxonomies" ON "taxonomies"."type" = $2 AND "taxonomies"."id" = "taxable_taxonomies"."taxonomy_id" LEFT OUTER JOIN "taxable_taxonomies" "taxable_taxonomies_environments_join" ON "taxable_taxonomies_environments_join"."taxable_type" = $3 AND "taxable_taxonomies_environments_join"."taxable_id" = "environments"."id" LEFT OUTER JOIN "taxonomies" "locations_environments" ON "locations_environments"."type" = $4 AND "locations_environments"."id" = "taxable_taxonomies_environments_join"."taxonomy_id" WHERE (environments.id IN (1,2,3)) AND (environments.id IN (1,2,3)) AND ((("environments"."id" IN (SELECT "environments"."id" FROM "environments"          INNER JOIN "taxable_taxonomies"
 |           ON "environments"."id" = "taxable_taxonomies"."taxable_id" 
 |           INNER JOIN "taxonomies"
 |           ON "taxable_taxonomies"."taxonomy_id" = "taxonomies"."id" 
 |  WHERE "taxonomies"."id" IN ('50','3','72','29','27','25','28','109','44','66','65','45','52','46','64','73','26','69') )) AND ("environments"."id" IN (SELECT "environments"."id" FROM "environments"          INNER JOIN "taxable_taxonomies"
 |           ON "environments"."id" = "taxable_taxonomies"."taxable_id" 
 |           INNER JOIN "taxonomies"
 |           ON "taxable_taxonomies"."taxonomy_id" = "taxonomies"."id" 
 |  WHERE "taxonomies"."id" IN ('142','1','10','54','56','7','59','9','62','4','58','6','74','76','60','8','53','57','11','61','5','42','63','23','24','49','51','30','48','47') )))) ORDER BY "environments"."name" ASC  [["taxable_type", "ForemanPuppet::Environment"], ["type", "Organization"], ["taxable_type", "ForemanPuppet::Environment"], ["type", "Location"]]

161851.1ms!!! Thats too much :frowning:
In out setup we have a lot of locations (33) and organizations (18), only two environments. Is this a problem?

3

Wow you have a lot of taxonomies (orgs and locs). But I guess even more Puppet environments right? I wonder if some DB indexes are missing on the join table.

4

Thanks for an answer. Nope, in our case we have only 2 environments (develop and production) and ~15k hosts if that matters.
I made an extra investigate and figure out that everything goes wrong, when I updated Foreman to v3.0. On v 2.5.4 everything is fine. Was there any significant changes on DB schema or something like that in v3.0?
Can you please tell me how can I provide an extra information about indexes? Can I create these missing indexes manually?
Sorry I’m not so good in English. Hope you can understand me well :slight_smile: