Foreman 1.21: Search for puppetclass extremely slow or times out

Problem:

Searching for a puppet class using the Puppet Class page (http://{fqdn}/puppetclasses?search={classname} takes extremly long or times out

Page is showing spinning wheel, but either the page times out or search takes very long (40 seconds or more). The page itself does show the puppetclasses, and am able to navigate to all the pages.

Expected outcome:

Foreman should return the class(es) matching the search string within a few seconds

Foreman and Proxy versions:

foreman: 1.21.0
foreman-proxy: 1.21.0

Foreman and Proxy plugin versions:

Other relevant data:
forman logs, can’t find any error or anything useful

2019-03-19T16:39:39 [I|app|48955a72] Started GET "/puppetclasses?search=elasticsearch&page=1" for 10.10.135.2 at 2019-03-19 16:39:39 +0800
2019-03-19T16:39:39 [I|app|48955a72] Processing by PuppetclassesController#index as HTML
2019-03-19T16:39:39 [I|app|48955a72]   Parameters: {"search"=>"elasticsearch", "page"=>"1"}

mysql reports a slow query:

# Time: 190319 16:53:51
# User@Host: 
# Thread_id: 263451136  Schema: foreman  QC_hit: No
# Query_time: 35.533927  Lock_time: 0.000512  Rows_sent: 1  Rows_examined: 43271094
# Rows_affected: 0
SET timestamp=1552985631;
SELECT SQL_CALC_FOUND_ROWS `puppetclasses`.`id` AS t0_r0, `puppetclasses`.`name` AS t0_r1, `puppetclasses`.`created_at` AS t0_r2, `puppetclasses`.`updated_at` AS t0_r3, `environments`.`id` AS t1_r0, `environments`.`name` AS t1_r1, `environments`.`created_at` AS t1_r2, `environments`.`updated_at` AS t1_r3, `taxonomies`.`id` AS t2_r0, `taxonomies`.`name` AS t2_r1, `taxonomies`.`type` AS t2_r2, `taxonomies`.`created_at` AS t2_r3, `taxonomies`.`updated_at` AS t2_r4, `taxonomies`.`ignore_types` AS t2_r5, `taxonomies`.`ancestry` AS t2_r6, `taxonomies`.`title` AS t2_r7, `taxonomies`.`description` AS t2_r8, `locations_puppetclasses`.`id` AS t3_r0, `locations_puppetclasses`.`name` AS t3_r1, `locations_puppetclasses`.`type` AS t3_r2, `locations_puppetclasses`.`created_at` AS t3_r3, `locations_puppetclasses`.`updated_at` AS t3_r4, `locations_puppetclasses`.`ignore_types` AS t3_r5, `locations_puppetclasses`.`ancestry` AS t3_r6, `locations_puppetclasses`.`title` AS t3_r7, `locations_puppetclasses`.`description` AS t3_r8 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 `environment_classes` `environment_classes_puppetclasses_join` ON `environment_classes_puppetclasses_join`.`puppetclass_id` = `puppetclasses`.`id` LEFT OUTER JOIN `environments` `environments_puppetclasses_join` ON `environments_puppetclasses_join`.`id` = `environment_classes_puppetclasses_join`.`environment_id` LEFT OUTER JOIN `taxable_taxonomies` ON `taxable_taxonomies`.`taxable_id` = `environments_puppetclasses_join`.`id` AND `taxable_taxonomies`.`taxable_type` = 'Environment' LEFT OUTER JOIN `taxonomies` ON `taxonomies`.`id` = `taxable_taxonomies`.`taxonomy_id` AND `taxonomies`.`type` = 'Organization' LEFT OUTER JOIN `environment_classes` `environment_classes_puppetclasses_join_2` ON `environment_classes_puppetclasses_join_2`.`puppetclass_id` = `puppetclasses`.`id` LEFT OUTER JOIN `environments` `environments_puppetclasses_join_2` ON `environments_puppetclasses_join_2`.`id` = `environment_classes_puppetclasses_join_2`.`environment_id` LEFT OUTER JOIN `taxable_taxonomies` `taxable_taxonomies_puppetclasses_join` ON `taxable_taxonomies_puppetclasses_join`.`taxable_id` = `environments_puppetclasses_join_2`.`id` AND `taxable_taxonomies_puppetclasses_join`.`taxable_type` = 'Environment' LEFT OUTER JOIN `taxonomies` `locations_puppetclasses` ON `locations_puppetclasses`.`id` = `taxable_taxonomies_puppetclasses_join`.`taxonomy_id` AND `locations_puppetclasses`.`type` = 'Location' WHERE ((`puppetclasses`.`name` LIKE '%elasticsearch%' OR `environments`.`name` LIKE '%elasticsearch%' OR `taxonomies`.`name` LIKE '%elasticsearch%' OR `taxonomies`.`name` LIKE '%elasticsearch%')) AND `puppetclasses`.`id` IN (199, 599, 175, 181, 180, 179, 184, 458, 681) ORDER BY puppetclasses.name LIMIT 1;

You can try searching for name=elasticsearch. That’s probably what you intended and much more optimized by only searching for names.

I’ve opened a pull request that should hopefully fix the issue you are seeing, feel free to test it and see if it fixes it:
https://github.com/theforeman/foreman/pull/6591

ekohl, thanks for the tip on using a more efficient search string…

tbrisker: tested and indeed fixes the issue, thanks for picking this up so fast.

1 Like