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;