Project

General

Profile

Bug #11881

Searching for 'puppet' in Puppet Classes takes long

Added by Matthias Thubauville almost 4 years ago. Updated about 1 year ago.

Status:
Closed
Priority:
Low
Assignee:
-
Category:
Search
Target version:
Difficulty:
Triaged:
Bugzilla link:
Team Backlog:
Fixed in Releases:
Found in Releases:

Description

Searching for 'puppet' in the Puppet Classes Page/View takes quite long. (See debug outputs below)

According to Dominic this is due to the fact, that a free text search is performed. I can support this, as searching for 'name = puppet' is more or less instantaneous.

I guess one could make the default (with no field specified) search only in the name field or catch the 'puppet' special case, to prevent this. (Or leave it as it is, since it is actually working as intended, but with this side-effect ;) )


Output without sql debug:

09:25:17 [app] [I] 
 | 
 | Started GET "/puppetclasses?utf8=%E2%9C%93&search=puppet" for 10.11.12.13 at 09:25:17 +0200
09:25:17 [app] [I] Processing by PuppetclassesController#index as HTML
09:25:17 [app] [I]   Parameters: {"utf8"=>"✓", "search"=>"puppet"}
09:27:04 [foreman-tasks/dynflow] [I] start terminating client dispatcher...
09:27:04 [foreman-tasks/dynflow] [I] stop listening for new events...
09:27:04 [foreman-tasks/dynflow] [I] start terminating clock...
09:30:11 [app] [I] Connecting to database specified by database.yml
09:30:12 [app] [I] Connecting to database specified by database.yml
09:30:23 [sql] [W] Creating scope :completer_scope. Overwriting existing method Location.completer_scope.
09:30:23 [sql] [W] Creating scope :completer_scope. Overwriting existing method Location.completer_scope.
09:30:23 [sql] [W] Creating scope :completer_scope. Overwriting existing method Organization.completer_scope.
09:30:23 [sql] [W] Creating scope :completer_scope. Overwriting existing method Organization.completer_scope.
09:30:37 [foreman-tasks/dynflow] [I] start terminating client dispatcher...
09:30:37 [foreman-tasks/dynflow] [I] stop listening for new events...
09:30:37 [foreman-tasks/dynflow] [I] start terminating clock...
09:30:37 [foreman-tasks/dynflow] [I] start terminating client dispatcher...
09:30:37 [foreman-tasks/dynflow] [I] stop listening for new events...
09:30:37 [foreman-tasks/dynflow] [I] start terminating clock...
09:30:47 [app] [I]   Rendered puppetclasses/index.html.erb within layouts/application (55.8ms)
09:30:47 [app] [I]   Rendered common/_searchbar.html.erb (3.9ms)
09:30:47 [app] [I]   Rendered home/_user_dropdown.html.erb (6.0ms)
09:30:47 [app] [I] Read fragment views/tabs_and_title_records-4 (0.1ms)
09:30:47 [app] [I]   Rendered home/_organization_dropdown.html.erb (8.2ms)
09:30:47 [app] [I]   Rendered home/_location_dropdown.html.erb (8.9ms)
09:30:47 [app] [I]   Rendered home/_org_switcher.html.erb (19.4ms)
09:30:47 [app] [I]   Rendered home/_submenu.html.erb (11.8ms)
09:30:47 [app] [I]   Rendered home/_submenu.html.erb (6.2ms)
09:30:47 [app] [I]   Rendered home/_submenu.html.erb (2.8ms)
09:30:47 [app] [I]   Rendered home/_submenu.html.erb (6.9ms)
09:30:47 [app] [I]   Rendered home/_submenu.html.erb (4.3ms)
09:30:47 [app] [I]   Rendered home/_submenu.html.erb (3.8ms)
09:30:47 [app] [I]   Rendered home/_submenu.html.erb (5.0ms)
09:30:47 [app] [I] Write fragment views/tabs_and_title_records-4 (1.1ms)
09:30:47 [app] [I]   Rendered home/_topbar.html.erb (74.8ms)
09:30:47 [app] [I]   Rendered layouts/base.html.erb (76.6ms)
09:30:47 [app] [I] Completed 200 OK in 330519ms (Views: 133.6ms | ActiveRecord: 11704.7ms)

Output with sql debug:

09:52:18 [app] [I]
 |
 | Started GET "/puppetclasses?utf8=%E2%9C%93&search=puppet" for 10.12.13.14 at 09:52:18 +0200
09:52:18 [app] [I] Processing by PuppetclassesController#index as HTML
09:52:18 [app] [I]   Parameters: {"utf8"=>"✓", "search"=>"puppet"}
09:52:18 [sql] [D]   User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 4]]
09:52:18 [app] [D] Setting current user thread-local variable to USER
09:52:18 [sql] [D]    (0.4ms)  SELECT COUNT(*) FROM "taxonomies" WHERE "taxonomies"."type" IN ('Organization')
09:52:18 [sql] [D]   Organization Load (0.4ms)  SELECT "taxonomies".* FROM "taxonomies" WHERE "taxonomies"."type" IN ('Organization') AND "taxonomies"."id" = 3 ORDER BY title LIMIT 1
09:52:18 [app] [D] Setting current organization thread-local variable to ORG
09:52:18 [sql] [D]    (0.3ms)  SELECT COUNT(*) FROM "taxonomies" WHERE "taxonomies"."type" IN ('Location')
09:52:18 [sql] [D]   Location Load (0.3ms)  SELECT "taxonomies".* FROM "taxonomies" WHERE "taxonomies"."type" IN ('Location') AND "taxonomies"."id" = 4 ORDER BY title LIMIT 1
09:52:18 [app] [D] Setting current location thread-local variable to LOC
09:52:18 [sql] [D]   AuthSource Load (0.3ms)  SELECT "auth_sources".* FROM "auth_sources" WHERE "auth_sources"."id" = 3 LIMIT 1
09:52:18 [sql] [D]    (0.2ms)  SELECT id FROM "taxonomies" WHERE "taxonomies"."type" IN ('Location') LIMIT 1
09:52:18 [sql] [D]    (0.2ms)  SELECT id FROM "taxonomies" WHERE "taxonomies"."type" IN ('Organization') LIMIT 1
09:52:18 [sql] [D]   Puppetclass Load (0.2ms)  SELECT "puppetclasses".* FROM "puppetclasses" ORDER BY puppetclasses.name LIMIT 1
09:52:18 [sql] [D]    (0.2ms)  SHOW max_identifier_length
09:52:19 [sql] [D]   Puppetclass Load (123.5ms)  SELECT DISTINCT "puppetclasses".id, puppetclasses.name AS alias_0 FROM "puppetclasses" LEFT OUTER JOIN "environment_classes" ON "environment_classes"."puppetclass_id" = "puppetclasses"."id" LEFT OUTER JOIN "environments" ON "environments"."id" = "environment_classes"."environment_id" LEFT OUTER JOIN "hostgroup_classes" ON "hostgroup_classes"."puppetclass_id" = "puppetclasses"."id" LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hostgroup_classes"."hostgroup_id" LEFT OUTER JOIN "config_group_classes" ON "config_group_classes"."puppetclass_id" = "puppetclasses"."id" LEFT OUTER JOIN "config_groups" ON "config_groups"."id" = "config_group_classes"."config_group_id" LEFT OUTER JOIN "config_group_classes" "config_group_classes_puppetclasses" ON "config_group_classes_puppetclasses"."puppetclass_id" = "puppetclasses"."id" LEFT OUTER JOIN "environment_classes" "environment_classes_puppetclasses_join" ON "environment_classes_puppetclasses_join"."puppetclass_id" = "puppetclasses"."id" LEFT OUTER JOIN "lookup_keys" ON "lookup_keys"."id" = "environment_classes_puppetclasses_join"."lookup_key_id" AND environment_classes.lookup_key_id is NOT NULL WHERE (("puppetclasses"."name" ILIKE '%puppet%' OR "environments"."name" ILIKE '%puppet%' OR "hostgroups"."name" ILIKE '%puppet%' OR "config_groups"."name" ILIKE '%puppet%')) ORDER BY puppetclasses.name LIMIT 20 OFFSET 0
09:52:36 [sql] [D]   SQL (17149.6ms)  SELECT "puppetclasses"."id" AS t0_r0, "puppetclasses"."name" AS t0_r1, "puppetclasses"."created_at" AS t0_r2, "puppetclasses"."updated_at" AS t0_r3, "puppetclasses"."total_hosts" AS t0_r4, "puppetclasses"."hostgroups_count" AS t0_r5, "puppetclasses"."global_class_params_count" AS t0_r6, "puppetclasses"."lookup_keys_count" AS t0_r7, "environments"."id" AS t1_r0, "environments"."name" AS t1_r1, "environments"."created_at" AS t1_r2, "environments"."updated_at" AS t1_r3, "environments"."katello_id" AS t1_r4, "environments"."hosts_count" AS t1_r5, "environments"."hostgroups_count" AS t1_r6, "hostgroups"."id" AS t2_r0, "hostgroups"."name" AS t2_r1, "hostgroups"."created_at" AS t2_r2, "hostgroups"."updated_at" AS t2_r3, "hostgroups"."environment_id" AS t2_r4, "hostgroups"."operatingsystem_id" AS t2_r5, "hostgroups"."architecture_id" AS t2_r6, "hostgroups"."medium_id" AS t2_r7, "hostgroups"."ptable_id" AS t2_r8, "hostgroups"."root_pass" AS t2_r9, "hostgroups"."puppet_ca_proxy_id" AS t2_r10, "hostgroups"."use_image" AS t2_r11, "hostgroups"."image_file" AS t2_r12, "hostgroups"."ancestry" AS t2_r13, "hostgroups"."vm_defaults" AS t2_r14, "hostgroups"."subnet_id" AS t2_r15, "hostgroups"."domain_id" AS t2_r16, "hostgroups"."puppet_proxy_id" AS t2_r17, "hostgroups"."title" AS t2_r18, "hostgroups"."realm_id" AS t2_r19, "hostgroups"."compute_profile_id" AS t2_r20, "hostgroups"."content_source_id" AS t2_r21, "hostgroups"."grub_pass" AS t2_r22, "hostgroups"."content_view_id" AS t2_r23, "hostgroups"."lifecycle_environment_id" AS t2_r24, "config_groups"."id" AS t3_r0, "config_groups"."name" AS t3_r1, "config_groups"."created_at" AS t3_r2, "config_groups"."updated_at" AS t3_r3, "config_groups"."hosts_count" AS t3_r4, "config_groups"."hostgroups_count" AS t3_r5, "config_groups"."config_group_classes_count" AS t3_r6, "config_group_classes_puppetclasses"."id" AS t4_r0, "config_group_classes_puppetclasses"."puppetclass_id" AS t4_r1, "config_group_classes_puppetclasses"."config_group_id" AS t4_r2, "config_group_classes_puppetclasses"."created_at" AS t4_r3, "config_group_classes_puppetclasses"."updated_at" AS t4_r4, "lookup_keys"."id" AS t5_r0, "lookup_keys"."key" AS t5_r1, "lookup_keys"."created_at" AS t5_r2, "lookup_keys"."updated_at" AS t5_r3, "lookup_keys"."puppetclass_id" AS t5_r4, "lookup_keys"."default_value" AS t5_r5, "lookup_keys"."path" AS t5_r6, "lookup_keys"."description" AS t5_r7, "lookup_keys"."validator_type" AS t5_r8, "lookup_keys"."validator_rule" AS t5_r9, "lookup_keys"."is_param" AS t5_r10, "lookup_keys"."key_type" AS t5_r11, "lookup_keys"."override" AS t5_r12, "lookup_keys"."required" AS t5_r13, "lookup_keys"."lookup_values_count" AS t5_r14, "lookup_keys"."merge_overrides" AS t5_r15, "lookup_keys"."avoid_duplicates" AS t5_r16, "lookup_keys"."use_puppet_default" AS t5_r17 FROM "puppetclasses" LEFT OUTER JOIN "environment_classes" ON "environment_classes"."puppetclass_id" = "puppetclasses"."id" LEFT OUTER JOIN "environments" ON "environments"."id" = "environment_classes"."environment_id" LEFT OUTER JOIN "hostgroup_classes" ON "hostgroup_classes"."puppetclass_id" = "puppetclasses"."id" LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hostgroup_classes"."hostgroup_id" LEFT OUTER JOIN "config_group_classes" ON "config_group_classes"."puppetclass_id" = "puppetclasses"."id" LEFT OUTER JOIN "config_groups" ON "config_groups"."id" = "config_group_classes"."config_group_id" LEFT OUTER JOIN "config_group_classes" "config_group_classes_puppetclasses" ON "config_group_classes_puppetclasses"."puppetclass_id" = "puppetclasses"."id" LEFT OUTER JOIN "environment_classes" "environment_classes_puppetclasses_join" ON "environment_classes_puppetclasses_join"."puppetclass_id" = "puppetclasses"."id" LEFT OUTER JOIN "lookup_keys" ON "lookup_keys"."id" = "environment_classes_puppetclasses_join"."lookup_key_id" AND environment_classes.lookup_key_id is NOT NULL WHERE "puppetclasses"."id" IN (146, 141, 142, 143, 144, 145, 147, 148, 149, 150, 151, 152, 153) AND (("puppetclasses"."name" ILIKE '%puppet%' OR "environments"."name" ILIKE '%puppet%' OR "hostgroups"."name" ILIKE '%puppet%' OR "config_groups"."name" ILIKE '%puppet%')) ORDER BY puppetclasses.name
09:54:28 [foreman-tasks/dynflow] [I] start terminating client dispatcher...
09:54:28 [foreman-tasks/dynflow] [I] stop listening for new events...
09:54:28 [foreman-tasks/dynflow] [I] start terminating clock...
09:54:29 [foreman-tasks/dynflow] [I] start terminating client dispatcher...
09:54:29 [foreman-tasks/dynflow] [I] stop listening for new events...
09:54:29 [foreman-tasks/dynflow] [I] start terminating clock...
09:59:58 [sql] [D]    (0.8ms)  SELECT DISTINCT hostgroup_id FROM "hostgroup_classes" WHERE "hostgroup_classes"."puppetclass_id" IN (146, 141, 142, 143, 144, 145, 147, 148, 149, 150, 151, 152, 153)
09:59:58 [sql] [D]   Taxonomy Load (0.3ms)  SELECT id FROM "taxonomies" WHERE (taxonomies.id = 4 or taxonomies.ancestry like '4/%' or taxonomies.ancestry = '4') ORDER BY title
09:59:58 [sql] [D]    (0.4ms)  SELECT DISTINCT taxable_id FROM "taxable_taxonomies" WHERE "taxable_taxonomies"."taxable_type" = 'SmartProxy' AND "taxable_taxonomies"."taxonomy_id" IN (4, 6, 7, 8)
09:59:58 [sql] [D]   Taxonomy Load (0.2ms)  SELECT id FROM "taxonomies" WHERE (taxonomies.id = 3 or taxonomies.ancestry like '3/%' or taxonomies.ancestry = '3') ORDER BY title
09:59:58 [sql] [D]    (0.3ms)  SELECT DISTINCT taxable_id FROM "taxable_taxonomies" WHERE "taxable_taxonomies"."taxable_type" = 'SmartProxy' AND "taxable_taxonomies"."taxonomy_id" IN (3)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT id FROM "taxonomies" WHERE (taxonomies.id = 4 or taxonomies.ancestry like '4/%' or taxonomies.ancestry = '4') ORDER BY title
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT DISTINCT taxable_id FROM "taxable_taxonomies" WHERE "taxable_taxonomies"."taxable_type" = 'SmartProxy' AND "taxable_taxonomies"."taxonomy_id" IN (4, 6, 7, 8)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT id FROM "taxonomies" WHERE (taxonomies.id = 3 or taxonomies.ancestry like '3/%' or taxonomies.ancestry = '3') ORDER BY title
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT DISTINCT taxable_id FROM "taxable_taxonomies" WHERE "taxable_taxonomies"."taxable_type" = 'SmartProxy' AND "taxable_taxonomies"."taxonomy_id" IN (3)
09:59:58 [sql] [D]   SmartProxy Load (0.5ms)  SELECT "smart_proxies".* FROM "smart_proxies" INNER JOIN "features_smart_proxies" ON "features_smart_proxies"."smart_proxy_id" = "smart_proxies"."id" INNER JOIN "features" ON "features"."id" = "features_smart_proxies"."feature_id" WHERE "smart_proxies"."id" IN (2, 1, 3) AND "features"."name" IN ('Puppet') ORDER BY smart_proxies.name
09:59:58 [sql] [D]   Hostgroup Load (0.4ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (1)
09:59:58 [sql] [D]   Hostgroup Load (0.3ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT "hostgroups".* FROM "hostgroups" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [sql] [D]   CACHE (0.0ms)  SELECT "hostgroups".* FROM "hostgroLOC" WHERE "hostgroups"."id" IN (NULL)
09:59:58 [app] [I]   Rendered puppetclasses/index.html.erb within layouts/application (71.0ms)
09:59:58 [sql] [D]   Bookmark Load (0.5ms)  SELECT "bookmarks".* FROM "bookmarks" WHERE (((bookmarks.public = 't') OR (bookmarks.owner_id = 4 AND bookmarks.owner_type = 'User'))) AND (controller = 'puppetclasses') ORDER BY name
09:59:58 [app] [I]   Rendered common/_searchbar.html.erb (4.8ms)
09:59:58 [app] [I]   Rendered home/_user_dropdown.html.erb (2.3ms)
09:59:58 [app] [I] Read fragment views/tabs_and_title_records-4 (0.1ms)
09:59:58 [app] [I]   Rendered home/_topbar.html.erb (3.7ms)
09:59:58 [app] [I]   Rendered layouts/base.html.erb (5.7ms)
09:59:58 [app] [I] Completed 200 OK in 459352ms (Views: 81.2ms | ActiveRecord: 17280.6ms)


Related issues

Related to Foreman - Bug #16483: Searching for Puppetclass by config_group or hostgroup is brokenClosed2016-09-08

Associated revisions

Revision c3f54762 (diff)
Added by Kamjar Gerami almost 4 years ago

fixes #11881 - Searching for "puppet" in Puppet Classes takes long - Set only_explicit to host and config groups definitions in puppetclasses

Revision a01dffb5 (diff)
Added by Kamjar Gerami almost 4 years ago

fixes #11881 - Searching for "puppet" in Puppet Classes takes long - Set only_explicit to host and config groups definitions in puppetclasses

(cherry picked from commit c3f54762bc2f5f17c98831b8afb1277ae4be9d2e)

History

#1 Updated by Dominic Cleal almost 4 years ago

It appears to be doing a free text search over these fields:

(("puppetclasses"."name" ILIKE '%puppet%' OR "environments"."name" ILIKE '%puppet%' OR "hostgroups"."name" ILIKE '%puppet%' OR "config_groups"."name" ILIKE '%puppet%'))

I don't think host groups or config group names are particularly relevant on this page, so we should probably set only_explicit on these definitions.

Parameter names were excluded back in #4565.

#2 Updated by The Foreman Bot almost 4 years ago

  • Status changed from New to Ready For Testing
  • Pull request https://github.com/theforeman/foreman/pull/2816 added
  • Pull request deleted ()

#3 Updated by Dominic Cleal almost 4 years ago

  • Legacy Backlogs Release (now unused) set to 63

#4 Updated by Kamjar Gerami almost 4 years ago

  • Status changed from Ready For Testing to Closed
  • % Done changed from 0 to 100

#5 Updated by Dominic Cleal almost 3 years ago

  • Related to Bug #16483: Searching for Puppetclass by config_group or hostgroup is broken added

Also available in: Atom PDF