Project

General

Profile

Actions

Bug #1088

closed

Overly aggressive query

Added by Dis Connect almost 13 years ago. Updated almost 13 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Facts
Target version:
Difficulty:
Triaged:
Fixed in Releases:
Found in Releases:

Description

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 (fact_names.name <> '--- !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 puppetmaster.fact_names.id 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..

Actions

Also available in: Atom PDF