On Wed, Sep 21, 2016 at 4:24 PM, sinux shen <[email protected]> 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:
>
can you please create an issue under the katello project, i believe the
katello packages should not be included during in the full text search.
thanks,
Ohad
>
> 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;
>
> 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]> 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]> 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].
>>> > 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.
>>>
>>> --
>>> 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.
>>>
>>
>> --
> 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.
>
--
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.
