Bug #1088

Overly aggressive query

Added by Dis Connect almost 8 years ago. Updated over 7 years ago.

Target version:
Bugzilla link:
Pull request:
Team Backlog:
Fixed in Releases:
Found in Releases:


Clicking the facts tab results in the entire foreman installation hanging for 1 minute while it runs this monstrosity:

SELECT `fact_values`.`id` AS t0_r0, `fact_values`.`value` AS t0_r1, `fact_values`.`fact_name_id` AS t0_r2, `fact_values`.`host_id` AS t0_r3, `fact_values`.`updated_at` AS t0_r4, `fact_values`.`created_at` AS t0_r5, `fact_names`.`id` AS t1_r0, `fact_names`.`name` AS t1_r1, `fact_names`.`updated_at` AS t1_r2, `fact_names`.`created_at` AS t1_r3, `hosts`.`id` AS t2_r0, `hosts`.`name` AS t2_r1, `hosts`.`ip` AS t2_r2, `hosts`.`last_compile` AS t2_r3, `hosts`.`last_freshcheck` AS t2_r4, `hosts`.`last_report` AS t2_r5, `hosts`.`updated_at` AS t2_r6, `hosts`.`source_file_id` AS t2_r7, `hosts`.`created_at` AS t2_r8, `hosts`.`environment` AS t2_r9, `hosts`.`mac` AS t2_r10, `hosts`.`sp_mac` AS t2_r11, `hosts`.`sp_ip` AS t2_r12, `hosts`.`sp_name` AS t2_r13, `hosts`.`root_pass` AS t2_r14, `hosts`.`serial` AS t2_r15, `hosts`.`puppetmaster_name` AS t2_r16, `hosts`.`puppet_status` AS t2_r17, `hosts`.`domain_id` AS t2_r18, `hosts`.`architecture_id` AS t2_r19, `hosts`.`operatingsystem_id` AS t2_r20, `hosts`.`environment_id` AS t2_r21, `hosts`.`subnet_id` AS t2_r22, `hosts`.`sp_subnet_id` AS t2_r23, `hosts`.`ptable_id` AS t2_r24, `hosts`.`medium_id` AS t2_r25, `hosts`.`build` AS t2_r26, `hosts`.`comment` AS t2_r27, `hosts`.`disk` AS t2_r28, `hosts`.`installed_at` AS t2_r29, `hosts`.`model_id` AS t2_r30, `hosts`.`hostgroup_id` AS t2_r31, `hosts`.`owner_id` AS t2_r32, `hosts`.`owner_type` AS t2_r33, `hosts`.`enabled` AS t2_r34, `hosts`.`managed` AS t2_r35, `hosts`.`puppetproxy_id` AS t2_r36, `hosts`.`use_image` AS t2_r37, `hosts`.`image_file` AS t2_r38 FROM `fact_values` LEFT OUTER JOIN `fact_names` ON `fact_names`.id = `fact_values`.fact_name_id LEFT OUTER JOIN `hosts` ON `hosts`.id = `fact_values`.host_id WHERE ( <> '--- !ruby/sym _timestamp') ORDER BY `hosts`.`name` ASC LIMIT 0, 20

Due to the various text columns, mysql must create on-disk temp tables for the results:
1 SIMPLE fact_names range PRIMARY,index_fact_names_on_id,index_fact_names_on_name index_fact_names_on_name 767 NULL 20 Using where with pushed condition; Using temporary; Using filesort
1 SIMPLE fact_values ref index_fact_values_on_fact_name_id index_fact_values_on_fact_name_id 4 1
1 SIMPLE hosts eq_ref PRIMARY,index_hosts_on_id PRIMARY 4 puppetmaster.fact_values.host_id 1

Somehow it doesn't seem reasonable to join and dump the largest tables in the database just to display a couple of facts from the first host... (I understand the need to display something when you open that tab but does anyone really need the 20 randomly-ordered values from the alphabetically-first host? Especially at the expense of the database..)

On a related note, it'd be nice if the whole app didn't hang up (causing report timeouts, ext-lookup timeouts, api timeouts..) when it spits up long-running queries like this..

Associated revisions

Revision 169e7ac6 (diff)
Added by Ohad Levy over 7 years ago

fixes #1088 - Overly aggressive query in facts tab


#1 Updated by Ohad Levy almost 8 years ago

ok, it seems this is mostly related to the order, which is order based on the host name.
so really, what we ask the db to do, is to fetch all hosts, sort them by name, then join the with facts and return only 20 records.

to solve this issue, we need to sort on the facts rather the host names.

Amos - any other ideas?

#2 Updated by Ohad Levy over 7 years ago

  • Status changed from New to Closed
  • % Done changed from 0 to 100

#3 Updated by Ohad Levy over 7 years ago

  • Assignee changed from Amos Benari to Ohad Levy
  • Target version set to 0.4

Also available in: Atom PDF