Looks like there are two queries that are extremely slow for some reason,
could you please run the following commands in the psql console:
EXPLAIN ANALYZE SELECT DISTINCT `hosts`.`id` FROM `hosts` LEFT OUTER JOIN
`host_status` ON `host_status`.`host_id` = `hosts`.`id` LEFT OUTER JOIN
`compute_resources` ON `compute_resources`.`id` =
`hosts`.`compute_resource_id` LEFT OUTER JOIN `hostgroups` ON
`hostgroups`.`id` = `hosts`.`hostgroup_id` LEFT OUTER JOIN
`operatingsystems` ON `operatingsystems`.`id` =
`hosts`.`operatingsystem_id` LEFT OUTER JOIN `nics` ON `nics`.`host_id` =
`hosts`.`id` LEFT OUTER JOIN `tokens` ON `tokens`.`host_id` = `hosts`.`id`
LEFT OUTER JOIN `models` ON `models`.`id` = `hosts`.`model_id` LEFT OUTER
JOIN `nics` `primary_interfaces_hosts_join` ON
`primary_interfaces_hosts_join`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join`.`primary` = 1 LEFT OUTER JOIN `domains` ON
`domains`.`id` = `primary_interfaces_hosts_join`.`domain_id` LEFT OUTER
JOIN `realms` ON `realms`.`id` = `hosts`.`realm_id` LEFT OUTER JOIN
`environments` ON `environments`.`id` = `hosts`.`environment_id` LEFT OUTER
JOIN `architectures` ON `architectures`.`id` = `hosts`.`architecture_id`
LEFT OUTER JOIN `images` ON `images`.`id` = `hosts`.`image_id` LEFT OUTER
JOIN `nics` `primary_interfaces_hosts` ON
`primary_interfaces_hosts`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts`.`primary` = 1 LEFT OUTER JOIN `nics`
`primary_interfaces_hosts_join_2` ON
`primary_interfaces_hosts_join_2`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join_2`.`primary` = 1 LEFT OUTER JOIN `subnets`
ON `subnets`.`id` = `primary_interfaces_hosts_join_2`.`subnet_id` AND
`subnets`.`type` = 'Subnet::Ipv4' LEFT OUTER JOIN `nics`
`primary_interfaces_hosts_join_3` ON
`primary_interfaces_hosts_join_3`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join_3`.`primary` = 1 LEFT OUTER JOIN `subnets`
`subnet6s_hosts` ON `subnet6s_hosts`.`id` =
`primary_interfaces_hosts_join_3`.`subnet6_id` AND `subnet6s_hosts`.`type`
= 'Subnet::Ipv6' LEFT OUTER JOIN `nics` `provision_interfaces_hosts` ON
`provision_interfaces_hosts`.`host_id` = `hosts`.`id` AND
`provision_interfaces_hosts`.`provision` = 1 LEFT OUTER JOIN
`discovery_rules` ON `discovery_rules`.`id` = `hosts`.`discovery_rule_id`
LEFT OUTER JOIN `host_salt_modules` ON `host_salt_modules`.`host_id` =
`hosts`.`id` LEFT OUTER JOIN `salt_modules` ON `salt_modules`.`id` =
`host_salt_modules`.`salt_module_id` LEFT OUTER JOIN `salt_environments` ON
`salt_environments`.`id` = `hosts`.`salt_environment_id` LEFT OUTER JOIN
`smart_proxies` ON `smart_proxies`.`id` = `hosts`.`salt_proxy_id` WHERE
`hosts`.`type` IN ('Host::Managed') AND ((`hosts`.`name` LIKE '%test%' OR
`hosts`.`comment` LIKE '%test%' OR `models`.`name` LIKE '%test%' OR
`hostgroups`.`name` LIKE '%test%' OR `hostgroups`.`title` LIKE '%test%' OR
`hostgroups`.`title` LIKE '%test%' OR `domains`.`name` LIKE '%test%' OR
`realms`.`name` LIKE '%test%' OR `environments`.`name` LIKE '%test%' OR
`architectures`.`name` LIKE '%test%' OR `compute_resources`.`name` LIKE
'%test%' OR `images`.`name` LIKE '%test%' OR `operatingsystems`.`name` LIKE
'%test%' OR `operatingsystems`.`description` LIKE '%test%' OR
`operatingsystems`.`title` LIKE '%test%' OR `operatingsystems`.`major` LIKE
'%test%' OR `operatingsystems`.`minor` LIKE '%test%' OR `nics`.`ip` LIKE
'%test%' OR `nics`.`ip` LIKE '%test%' OR `nics`.`mac` LIKE '%test%' OR
`subnets`.`network` LIKE '%test%' OR `subnets`.`name` LIKE '%test%' OR
`subnets`.`network` LIKE '%test%' OR `subnets`.`name` LIKE '%test%' OR
`hosts`.`uuid` LIKE '%test%' OR `nics`.`mac` LIKE '%test%' OR
`operatingsystems`.`name` LIKE '%test%' OR `operatingsystems`.`description`
LIKE '%test%' OR `operatingsystems`.`title` LIKE '%test%' OR
`operatingsystems`.`major` LIKE '%test%' OR `operatingsystems`.`minor` LIKE
'%test%' OR `discovery_rules`.`name` LIKE '%test%' OR `salt_modules`.`name`
LIKE '%test%' OR `salt_environments`.`name` LIKE '%test%' OR
`smart_proxies`.`name` LIKE '%test%')) ORDER BY `hosts`.`name` ASC LIMIT 20
OFFSET 0;
EXPLAIN ANALYZE SELECT COUNT(DISTINCT `hosts`.`id`) FROM `hosts` LEFT OUTER
JOIN `host_status` ON `host_status`.`host_id` = `hosts`.`id` LEFT OUTER
JOIN `compute_resources` ON `compute_resources`.`id` =
`hosts`.`compute_resource_id` LEFT OUTER JOIN `hostgroups` ON
`hostgroups`.`id` = `hosts`.`hostgroup_id` LEFT OUTER JOIN
`operatingsystems` ON `operatingsystems`.`id` =
`hosts`.`operatingsystem_id` LEFT OUTER JOIN `nics` ON `nics`.`host_id` =
`hosts`.`id` LEFT OUTER JOIN `tokens` ON `tokens`.`host_id` = `hosts`.`id`
LEFT OUTER JOIN `models` ON `models`.`id` = `hosts`.`model_id` LEFT OUTER
JOIN `nics` `primary_interfaces_hosts_join` ON
`primary_interfaces_hosts_join`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join`.`primary` = 1 LEFT OUTER JOIN `domains` ON
`domains`.`id` = `primary_interfaces_hosts_join`.`domain_id` LEFT OUTER
JOIN `realms` ON `realms`.`id` = `hosts`.`realm_id` LEFT OUTER JOIN
`environments` ON `environments`.`id` = `hosts`.`environment_id` LEFT OUTER
JOIN `architectures` ON `architectures`.`id` = `hosts`.`architecture_id`
LEFT OUTER JOIN `images` ON `images`.`id` = `hosts`.`image_id` LEFT OUTER
JOIN `nics` `primary_interfaces_hosts` ON
`primary_interfaces_hosts`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts`.`primary` = 1 LEFT OUTER JOIN `nics`
`primary_interfaces_hosts_join_2` ON
`primary_interfaces_hosts_join_2`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join_2`.`primary` = 1 LEFT OUTER JOIN `subnets`
ON `subnets`.`id` = `primary_interfaces_hosts_join_2`.`subnet_id` AND
`subnets`.`type` = 'Subnet::Ipv4' LEFT OUTER JOIN `nics`
`primary_interfaces_hosts_join_3` ON
`primary_interfaces_hosts_join_3`.`host_id` = `hosts`.`id` AND
`primary_interfaces_hosts_join_3`.`primary` = 1 LEFT OUTER JOIN `subnets`
`subnet6s_hosts` ON `subnet6s_hosts`.`id` =
`primary_interfaces_hosts_join_3`.`subnet6_id` AND `subnet6s_hosts`.`type`
= 'Subnet::Ipv6' LEFT OUTER JOIN `nics` `provision_interfaces_hosts` ON
`provision_interfaces_hosts`.`host_id` = `hosts`.`id` AND
`provision_interfaces_hosts`.`provision` = 1 LEFT OUTER JOIN
`discovery_rules` ON `discovery_rules`.`id` = `hosts`.`discovery_rule_id`
LEFT OUTER JOIN `host_salt_modules` ON `host_salt_modules`.`host_id` =
`hosts`.`id` LEFT OUTER JOIN `salt_modules` ON `salt_modules`.`id` =
`host_salt_modules`.`salt_module_id` LEFT OUTER JOIN `salt_environments` ON
`salt_environments`.`id` = `hosts`.`salt_environment_id` LEFT OUTER JOIN
`smart_proxies` ON `smart_proxies`.`id` = `hosts`.`salt_proxy_id` WHERE
`hosts`.`type` IN ('Host::Managed') AND ((`hosts`.`name` LIKE '%test%' OR
`hosts`.`comment` LIKE '%test%' OR `models`.`name` LIKE '%test%' OR
`hostgroups`.`name` LIKE '%test%' OR `hostgroups`.`title` LIKE '%test%' OR
`hostgroups`.`title` LIKE '%test%' OR `domains`.`name` LIKE '%test%' OR
`realms`.`name` LIKE '%test%' OR `environments`.`name` LIKE '%test%' OR
`architectures`.`name` LIKE '%test%' OR `compute_resources`.`name` LIKE
'%test%' OR `images`.`name` LIKE '%test%' OR `operatingsystems`.`name` LIKE
'%test%' OR `operatingsystems`.`description` LIKE '%test%' OR
`operatingsystems`.`title` LIKE '%test%' OR `operatingsystems`.`major` LIKE
'%test%' OR `operatingsystems`.`minor` LIKE '%test%' OR `nics`.`ip` LIKE
'%test%' OR `nics`.`ip` LIKE '%test%' OR `nics`.`mac` LIKE '%test%' OR
`subnets`.`network` LIKE '%test%' OR `subnets`.`name` LIKE '%test%' OR
`subnets`.`network` LIKE '%test%' OR `subnets`.`name` LIKE '%test%' OR
`hosts`.`uuid` LIKE '%test%' OR `nics`.`mac` LIKE '%test%' OR
`operatingsystems`.`name` LIKE '%test%' OR `operatingsystems`.`description`
LIKE '%test%' OR `operatingsystems`.`title` LIKE '%test%' OR
`operatingsystems`.`major` LIKE '%test%' OR `operatingsystems`.`minor` LIKE
'%test%' OR `discovery_rules`.`name` LIKE '%test%' OR `salt_modules`.`name`
LIKE '%test%' OR `salt_environments`.`name` LIKE '%test%' OR
`smart_proxies`.`name` LIKE '%test%'));
It may take a few minutes to run but it will show us what is taking the
time- there may be one table that is extremely painful to join on, or some
missing indexes.
On Tue, Nov 1, 2016 at 10:18 PM, 'Konstantin Orekhov' via Foreman users <
[email protected]> wrote:
> Here you go - https://gist.github.com/korekhov/
> 7ad0fddae6e330f1655305c626bb4808
>
> Please let me know if that's not enough and you need something else.
>
>
> --
> 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.
