On 09/21/2016 09:24 AM, sinux shen wrote:
> Hi, Lukas & Ohad,
>
> Thanks for the help, we do turned the slow log on and we found the heavy
> SQL query while search a specific host and now we realized that we should
> use auto completer to make the query more smart and lite, and here is the
> SQL that almost join all the other tables that was associated to that
> specific host, and in two of the table (katello_host_installed_packages and
> katello_installed_packages), we have 500,000+ rows for each, and this
> caused heavy load and slow query, just FYI:
>
> SELECT DISTINCT "hosts".id FROM "hosts"
> LEFT OUTER JOIN "models" ON "models"."id" = "hosts"."model_id"
> LEFT OUTER JOIN "hostgroups" ON "hostgroups"."id" = "hosts"."hostgroup_id"
> AND "hostgroups"."id" IN (14, 27, 19, 1, 2, 21, 9, 3, 10, 23, 11, 4, 18, 22)
> LEFT OUTER JOIN "nics" ON "nics"."host_id" = "hosts"."id" AND
> "nics"."primary" = 't'
> LEFT OUTER JOIN "domains" ON "domains"."id" = "nics"."domain_id" AND
> "domains"."id" IN (1)
> LEFT OUTER JOIN "realms" ON "realms"."id" = "hosts"."realm_id" AND 1=0
> LEFT OUTER JOIN "environments" ON "environments"."id" =
> "hosts"."environment_id" AND "environments"."id" IN (2, 3, 4, 8, 5)
> LEFT OUTER JOIN "architectures" ON "architectures"."id" =
> "hosts"."architecture_id"
> LEFT OUTER JOIN "compute_resources" ON "compute_resources"."id" =
> "hosts"."compute_resource_id" AND 1=0
> LEFT OUTER JOIN "images" ON "images"."id" = "hosts"."image_id"
> LEFT OUTER JOIN "operatingsystems" ON "operatingsystems"."id" =
> "hosts"."operatingsystem_id"
> LEFT OUTER JOIN "nics" "primary_interfaces_hosts" ON
> "primary_interfaces_hosts"."host_id" = "hosts"."id" AND
> "primary_interfaces_hosts"."primary" = 't'
> LEFT OUTER JOIN "nics" "interfaces_hosts" ON "interfaces_hosts"."host_id" =
> "hosts"."id"
> LEFT OUTER JOIN "taxonomies" ON "taxonomies"."id" = "hosts"."location_id"
> AND "taxonomies"."type" IN ('Location')
> LEFT OUTER JOIN "nics" "primary_interfaces_hosts_join" ON
> "primary_interfaces_hosts_join"."host_id" = "hosts"."id" AND
> "primary_interfaces_hosts_join"."primary" = 't'
> LEFT OUTER JOIN "subnets" ON "subnets"."id" =
> "primary_interfaces_hosts_join"."subnet_id" AND "subnets"."id" IN (5, 2, 1)
> LEFT OUTER JOIN "nics" "provision_interfaces_hosts" ON
> "provision_interfaces_hosts"."host_id" = "hosts"."id" AND
> "provision_interfaces_hosts"."provision" = 't'
> LEFT OUTER JOIN "smart_proxies" ON "smart_proxies"."id" =
> "hosts"."content_source_id" AND "smart_proxies"."id" IN (7, 11, 1)
> LEFT OUTER JOIN "katello_host_collection_hosts" ON
> "katello_host_collection_hosts"."host_id" = "hosts"."id"
> LEFT OUTER JOIN "katello_host_collections" ON
> "katello_host_collections"."id" =
> "katello_host_collection_hosts"."host_collection_id"
> LEFT OUTER JOIN "katello_host_installed_packages" ON
> "katello_host_installed_packages"."host_id" = "hosts"."id"
> LEFT OUTER JOIN "katello_installed_packages" ON
> "katello_installed_packages"."id" =
> "katello_host_installed_packages"."installed_package_id"
> LEFT OUTER JOIN "katello_content_facets" ON
> "katello_content_facets"."host_id" = "hosts"."id"
> LEFT OUTER JOIN "katello_content_views" ON "katello_content_views"."id" =
> "katello_content_facets"."content_view_id"
> LEFT OUTER JOIN "katello_content_facets" "content_facets_hosts_join" ON
> "content_facets_hosts_join"."host_id" = "hosts"."id"
> LEFT OUTER JOIN "katello_environments" ON "katello_environments"."id" =
> "content_facets_hosts_join"."lifecycle_environment_id"
> LEFT OUTER JOIN "katello_subscription_facets" ON
> "katello_subscription_facets"."host_id" = "hosts"."id"
> LEFT OUTER JOIN "discovery_rules" ON "discovery_rules"."id" =
> "hosts"."discovery_rule_id" AND 1=0
> LEFT OUTER JOIN "host_status" ON "host_status"."host_id" = "hosts"."id"
> WHERE "hosts"."type" IN ('Host::Managed') AND "hosts"."organization_id" IN
> (3) AND "hosts"."location_id" IN (4) AND (("hosts"."name" ILIKE
> '%orangewolf%' OR "hosts"."comment" ILIKE '%orangewolf%' OR "models"."name"
> ILIKE '%orangewolf%' OR "hostgroups"."name" ILIKE '%orangewolf%' OR
> "hostgroups"."title" ILIKE '%orangewolf%' OR "hostgroups"."title" ILIKE
> '%orangewolf%' OR "domains"."name" ILIKE '%orangewolf%' OR "realms"."name"
> ILIKE '%orangewolf%' OR "environments"."name" ILIKE '%orangewolf%' OR
> "architectures"."name" ILIKE '%orangewolf%' OR "compute_resources"."name"
> ILIKE '%orangewolf%' OR "images"."name" ILIKE '%orangewolf%' OR
> "operatingsystems"."name" ILIKE '%orangewolf%' OR
> "operatingsystems"."description" ILIKE '%orangewolf%' OR
> "operatingsystems"."title" ILIKE '%orangewolf%' OR
> "operatingsystems"."major" ILIKE '%orangewolf%' OR
> "operatingsystems"."minor" ILIKE '%orangewolf%' OR "nics"."ip" ILIKE
> '%orangewolf%' OR "nics"."ip" ILIKE '%orangewolf%' OR "nics"."mac" ILIKE
> '%orangewolf%' OR "taxonomies"."title" ILIKE '%orangewolf%' OR
> "subnets"."network" ILIKE '%orangewolf%' OR "subnets"."name" ILIKE
> '%orangewolf%' OR "hosts"."uuid" ILIKE '%orangewolf%' OR "nics"."mac" ILIKE
> '%orangewolf%' OR "operatingsystems"."name" ILIKE '%orangewolf%' OR
> "operatingsystems"."description" ILIKE '%orangewolf%' OR
> "operatingsystems"."title" ILIKE '%orangewolf%' OR
> "operatingsystems"."major" ILIKE '%orangewolf%' OR
> "operatingsystems"."minor" ILIKE '%orangewolf%' OR "smart_proxies"."name"
> ILIKE '%orangewolf%' OR "katello_host_collections"."name" ILIKE
> '%orangewolf%' OR "katello_installed_packages"."nvra" ILIKE '%orangewolf%'
> OR "katello_installed_packages"."name" ILIKE '%orangewolf%' OR
> "katello_content_views"."name" ILIKE '%orangewolf%' OR
> "katello_environments"."name" ILIKE '%orangewolf%' OR 1=0 OR 1=0 OR
> "katello_subscription_facets"."release_version" ILIKE '%orangewolf%' OR
> "katello_subscription_facets"."service_level" ILIKE '%orangewolf%' OR
> "katello_subscription_facets"."uuid" ILIKE '%orangewolf%' OR
> "discovery_rules"."name" ILIKE '%orangewolf%')) AND "hosts"."id" IN (874)
> ORDER BY "hosts"."name" ASC;
>
Thanks for obtaining this query. Can you try the following?
* sudo su - postgres
* psql foreman
* in psql console: explain (analyze, buffers) <query from above>
This will output an explain plan, if you send that output we can confirm
which comparison or join is causing issues.
> On Tuesday, September 20, 2016 at 3:24:02 PM UTC+8, ohad wrote:
>>
>>
>>
>> On Tue, Sep 20, 2016 at 3:16 AM, sinux shen <[email protected]
>> <javascript:>> wrote:
>>
>>> I just simply search a specific hostname in the search bar, for example
>>> “foo”, and then have to wait for a long time, normally 20~30 secs, I will
>>> try to turn the slow query on and see what happened.
>>>
>>
>> You should not use queries like foo unless you want to search across all
>> host associations, you should use the auto completer to use a more specific
>> term, e.g. name ~ foo
>>
>> when not defining the field to search on, we have to query across multiple
>> tables, which makes quite a few sql queries which are probably are not
>> required if you define a more specific search term.
>>
>> Ohad
>>
>>>
>>> Thanks
>>> -Sinux
>>>> On Sep 14, 2016, at 3:59 PM, Lukas Zapletal <[email protected]
>>> <javascript:>> wrote:
>>>>
>>>>> what was the search query that you used?
>>>>
>>>> And do you experience this when loading "intelligent completion" or when
>>>> doing actual query?
>>>>
>>>> Are you able to enable slow queries logging in your postgres and send us
>>>> the output?
>>>>
>>>> https://wiki.postgresql.org/wiki/Logging_Difficult_Queries
>>>>
>>>> Easiest is to set log_min_duration_statement to reasonable value (1
>>>> second).
>>>>
>>>> --
>>>> Later,
>>>> Lukas #lzap Zapletal
>>>>
>>>> --
>>>> 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] <javascript:>.
>>>> To post to this group, send email to [email protected]
>>> <javascript:>.
>>>> Visit this group at https://groups.google.com/group/foreman-users.
>>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>> --
>>> 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] <javascript:>.
>>> To post to this group, send email to [email protected]
>>> <javascript:>.
>>> Visit this group at https://groups.google.com/group/foreman-users.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
>
--
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.
