There seem to be two things that stand out: 1. the host_salt_modules table is scanned completely rather then by index - it seems it needs a couple of indexes added (on host_id and on salt_module_id). I have opened http://projects.theforeman.org/issues/17196 to address this. 2. in the first query, the final join table is written to disk due to it's large size - i will look into reducing the fields that are included in the non-explicit search so that possible some of the joins can be reduced.
On Wed, Nov 2, 2016 at 11:42 PM, 'Konstantin Orekhov' via Foreman users < [email protected]> wrote: > The second one: > > 1 SIMPLE hosts ALL index_hosts_on_type NULL NULL NULL 1625 75.02 Using > where > 1 SIMPLE host_status ref index_host_status_on_host_id > index_host_status_on_host_id 4 foreman.hosts.id 1 100.00 Using index > 1 SIMPLE compute_resources eq_ref PRIMARY PRIMARY 4 foreman.hosts.compute_ > resource_id 1 100.00 NULL > 1 SIMPLE hostgroups eq_ref PRIMARY PRIMARY 4 foreman.hosts.hostgroup_id 1 > 100.00 NULL > 1 SIMPLE operatingsystems eq_ref PRIMARY PRIMARY 4 > foreman.hosts.operatingsystem_id 1 100.00 NULL > 1 SIMPLE nics ref index_by_host index_by_host 5 foreman.hosts.id 20 100.00 > NULL > 1 SIMPLE tokens ref index_tokens_on_host_id index_tokens_on_host_id 5 > foreman.hosts.id 1 100.00 Using index > 1 SIMPLE models eq_ref PRIMARY PRIMARY 4 foreman.hosts.model_id 1 100.00 > NULL > 1 SIMPLE primary_interfaces_hosts_join ref index_by_host index_by_host 5 > foreman.hosts.id 20 100.00 Using where > 1 SIMPLE domains eq_ref PRIMARY PRIMARY 4 foreman.primary_interfaces_ > hosts_join.domain_id 1 100.00 NULL > 1 SIMPLE realms eq_ref PRIMARY PRIMARY 4 foreman.hosts.realm_id 1 100.00 > NULL > 1 SIMPLE environments eq_ref PRIMARY PRIMARY 4 > foreman.hosts.environment_id 1 100.00 NULL > 1 SIMPLE architectures eq_ref PRIMARY PRIMARY 4 > foreman.hosts.architecture_id 1 100.00 NULL > 1 SIMPLE images eq_ref PRIMARY PRIMARY 4 foreman.hosts.image_id 1 100.00 > NULL > 1 SIMPLE primary_interfaces_hosts ref index_by_host index_by_host 5 > foreman.hosts.id 20 100.00 Using where > 1 SIMPLE primary_interfaces_hosts_join_2 ref index_by_host index_by_host 5 > foreman.hosts.id 20 100.00 Using where > 1 SIMPLE subnets eq_ref PRIMARY,index_subnets_on_type PRIMARY 4 > foreman.primary_interfaces_hosts_join_2.subnet_id 1 100.00 Using where > 1 SIMPLE primary_interfaces_hosts_join_3 ref index_by_host index_by_host 5 > foreman.hosts.id 20 100.00 Using where > 1 SIMPLE subnet6s_hosts eq_ref PRIMARY,index_subnets_on_type PRIMARY 4 > foreman.primary_interfaces_hosts_join_3.subnet6_id 1 100.00 Using where > 1 SIMPLE provision_interfaces_hosts ref index_by_host index_by_host 5 > foreman.hosts.id 20 100.00 Using where > 1 SIMPLE discovery_rules eq_ref PRIMARY PRIMARY 4 > foreman.hosts.discovery_rule_id 1 100.00 NULL > 1 SIMPLE host_salt_modules ALL NULL NULL NULL NULL 1 100.00 Using where; > Using join buffer (Block Nested Loop) > 1 SIMPLE salt_modules eq_ref PRIMARY PRIMARY 4 foreman.host_salt_modules. > salt_module_id 1 100.00 NULL > 1 SIMPLE salt_environments eq_ref PRIMARY PRIMARY 4 foreman.hosts.salt_ > environment_id 1 100.00 NULL > 1 SIMPLE smart_proxies eq_ref PRIMARY PRIMARY 4 > foreman.hosts.salt_proxy_id 1 100.00 Using where > > -- > You received this message because you are subscribed to the Google Groups > "Foreman users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/foreman-users. > For more options, visit https://groups.google.com/d/optout. > -- Have a nice day, Tomer Brisker Red Hat Engineering -- You received this message because you are subscribed to the Google Groups "Foreman users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/foreman-users. For more options, visit https://groups.google.com/d/optout.
