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