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 < foreman-users@googlegroups.com> 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 foreman-users+unsubscr...@googlegroups.com. > To post to this group, send email to foreman-users@googlegroups.com. > 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 foreman-users+unsubscr...@googlegroups.com. To post to this group, send email to foreman-users@googlegroups.com. Visit this group at https://groups.google.com/group/foreman-users. For more options, visit https://groups.google.com/d/optout.
