On 09/21/2016 10:48 AM, sinux shen wrote:
we actually did what you suggested, it was pretty much like the
following two join that caused the problem:
> 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"
each table has more than 500,000+ rows of records. I will try to get
the output when I get back to work tomorrow.
Went ahead and opened an issue here:
http://projects.theforeman.org/issues/16647
and a PR here: https://github.com/Katello/katello/pull/6338
-Justin
Thanks again for the help.
On Wednesday, September 21, 2016 at 9:47:02 PM UTC+8, Chris Duryee wrote:
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
<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
<https://groups.google.com/group/foreman-users>.
>>>> For more options, visit https://groups.google.com/d/optout
<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
<https://groups.google.com/group/foreman-users>.
>>> For more options, visit https://groups.google.com/d/optout
<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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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.
