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 <sinux...@gmail.com
    >> <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 <lz...@redhat.com
    >>> <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 foreman-user...@googlegroups.com <javascript:>.
    >>>> To post to this group, send email to forema...@googlegroups.com
    >>> <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 foreman-user...@googlegroups.com <javascript:>.
    >>> To post to this group, send email to forema...@googlegroups.com
    >>> <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 foreman-users+unsubscr...@googlegroups.com <mailto:foreman-users+unsubscr...@googlegroups.com>. To post to this group, send email to foreman-users@googlegroups.com <mailto: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.


--
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.

Reply via email to