Bugzilla (bugzilla.redhat.com) will be under maintenance for infrastructure upgrades and will not be available on July 31st between 12:30 AM - 05:30 AM UTC. We appreciate your understanding and patience. You can follow status.redhat.com for details.
Bug 1417642 - showing Content -> Errata with "Installable" checkbox checked takes too much time and memory when on scale
Summary: showing Content -> Errata with "Installable" checkbox checked takes too much ...
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Satellite
Classification: Red Hat
Component: Errata Management
Version: 6.2.6
Hardware: Unspecified
OS: Unspecified
high
urgent
Target Milestone: Unspecified
Assignee: Partha Aji
QA Contact: jcallaha
URL:
Whiteboard: scale_lab
: 1417437 1425800 (view as bug list)
Depends On:
Blocks: 1399395 1435022 1426414
TreeView+ depends on / blocked
 
Reported: 2017-01-30 13:46 UTC by Jan Hutař
Modified: 2020-12-14 08:03 UTC (History)
18 users (show)

Fixed In Version: tfm-rubygem-katello-3.0.0.115-1
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
: 1426414 (view as bug list)
Environment:
Last Closed: 2017-05-01 13:58:21 UTC
Target Upstream Version:


Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
Foreman Issue Tracker 18376 0 High Closed showing Content -> Errata with "Installable" checkbox checked hakes too much time and memory when on scale 2020-06-04 23:59:18 UTC
Red Hat Product Errata RHBA-2017:1191 0 normal SHIPPED_LIVE Satellite 6.2.9 Async Bug Release 2017-05-01 17:49:42 UTC

Description Jan Hutař 2017-01-30 13:46:20 UTC
Description of problem:
With 5 errata and 6138 clients registered, showing Content -> Errata with "Installable" checkbox checked hakes too much time and memory


Version-Release number of selected component (if applicable):
satellite-6.2.6-2.0.el7sat.noarch


How reproducible:
always


Steps to Reproduce:
1. Have 6138 clients registered, each with 5 installable erratas
   (same 5 in our case)
2. Go to Content -> Errata


Actual results:
Takes more than 8 minutes to load the page, Ruby RSS memory goes from 4 to 10 GB


Expected results:
Should be faster

Additional info:

Comment 1 Jan Hutař 2017-01-30 13:48:14 UTC
2017-01-30 07:37:49 EST LOG:  duration: 122807.009 ms  execute <unnamed>: SELECT  "katello_errata".* FROM "katello_errata"  WHERE "katello_errata"."id" IN (SELECT DISTINCT "katello_errata"."id" FROM "katello_errata" INNER JOIN "katello_repository_errata" ON "katello_repository_errata"."erratum_id" = "katello_errata"."id" INNER JOIN "katello_content_facet_errata" ON "katello_content_facet_errata"."erratum_id" = "katello_errata"."id" INNER JOIN "katello_content_facets" ON "katello_content_facets"."id" = "katello_content_facet_errata"."content_facet_id" INNER JOIN "katello_content_facet_errata" "content_facet_errata_katello_errata" ON "content_facet_errata_katello_errata"."erratum_id" = "katello_errata"."id" INNER JOIN katello_content_facet_repositories on         katello_content_facet_repositories.content_facet_id = katello_content_facet_errata.content_facet_id INNER JOIN katello_repository_errata AS host_repo_errata ON           host_repo_errata.erratum_id = katello_errata.id WHERE "katello_repository_errata"."repository_id" IN (SELECT "katello_repositories"."id" FROM "katello_repositories"  WHERE (katello_repositories.id in (11,3,1,2,4,5,7,8,6,9,10) or katello_repositories.id in (5,6) or katello_repositories.id in (11,6,5,4,3,2,1,10,9,8,7))) AND "katello_repository_errata"."repository_id" IN (SELECT "katello_repositories"."id" FROM "katello_repositories"  WHERE (katello_repositories.id in (11,3,1,2,4,5,7,8,6,9,10) or katello_repositories.id in (5,6) or katello_repositories.id in (11,6,5,4,3,2,1,10,9,8,7)) AND "katello_repositories"."environment_id" IN (1)) AND "katello_content_facets"."host_id" IN (SELECT "hosts"."id" FROM "hosts"  WHERE "hosts"."type" IN ('Host::Managed')) AND (katello_content_facet_repositories.repository_id = host_repo_errata.repository_id) AND "katello_content_facets"."host_id" IN (15124, [...], 8844, 1))  ORDER BY "katello_errata"."updated" DESC, katello_errata.id DESC LIMIT 50 OFFSET 0
2017-01-30 07:42:16 EST LOG:  duration: 1016.397 ms  statement: COMMIT

Comment 4 Jan Hutař 2017-01-30 14:04:17 UTC
Maybe this is related or even duplicate of bug 1417437

Comment 5 Chris Duryee 2017-01-30 14:09:45 UTC
This does not look like a duplicate. I hit the same issue earlier with this query, I believe it's specific to checking the "installable" checbox.

Comment 7 Evgeni Golov 2017-01-30 20:02:21 UTC
one cheap win is adding this index:
 CREATE INDEX katello_content_facet_errata_ca_id_idx ON katello_content_facet_errata (content_facet_id);
which elliminates a full scan on katello_content_facet_errata while
  INNER JOIN "katello_content_facets" ON "katello_content_facets"."id" = "katello_content_facet_errata"."content_facet_id"

Comment 8 Evgeni Golov 2017-01-30 20:37:29 UTC
FTR, the worst offender (according to an explain analyze run though https://explain.depesz.com/) is
  AND "katello_content_facets"."host_id" IN 
   (select "hosts"."id" FROM "hosts"  WHERE "hosts"."type" IN ('Host::Managed'))
which itself is "fine", but given we run this thousands of times is kinda yuk.

Comment 9 Evgeni Golov 2017-01-30 20:49:35 UTC
actually, why are we even checking this? there will be no errata for Host::Hypervisor anyways...

Comment 10 Pavel Moravec 2017-02-01 08:00:30 UTC
/opt/theforeman/tfm/root/usr/share/gems/gems/katello-3.0.0.94/app/models/katello/erratum.rb :

    def self.installable_for_hosts(hosts = nil)
      query = Katello::Erratum.joins(:content_facet_errata).joins(:repository_errata).
        joins("INNER JOIN #{Katello::ContentFacetRepository.table_name} on \
        #{Katello::ContentFacetRepository.table_name}.content_facet_id = #{Katello::ContentFacetErratum.table_name}.content_facet_id").
        joins("INNER JOIN #{Katello::RepositoryErratum.table_name} AS host_repo_errata ON \
          host_repo_errata.erratum_id = #{Katello::Erratum.table_name}.id").
        where("#{Katello::ContentFacetRepository.table_name}.repository_id = host_repo_errata.repository_id")

      query = query.joins(:content_facets).where("#{Katello::Host::ContentFacet.table_name}.host_id" => [hosts.map(&:id)]) if hosts
      query.uniq
    end

That's the problematic query.

Comment 11 Tom McKay 2017-02-02 16:08:47 UTC
Created redmine issue http://projects.theforeman.org/issues/18376 from this bug

Comment 14 Satellite Program 2017-02-02 19:12:28 UTC
Upstream bug assigned to paji@redhat.com

Comment 18 Pradeep Kumar Surisetty 2017-02-03 20:30:04 UTC
This updated query improve timing a lot.  8 mins to 4.6 sec
Thanks

Comment 21 Mike McCune 2017-02-03 21:32:45 UTC
HOTFIX INSTRUCTIONS - SATELLITE 6.2.7 (RHEL 7)

Patch to improve the performance of Installable Errata queries

This patch addresses issues specified in https://bugzilla.redhat.com/show_bug.cgi?id=1417642 and https://bugzilla.redhat.com/show_bug.cgi?id=1377725 

Install instructions: 
  
1) Download the attached patch  to your Satellite Server.

2) Stop services

katello-service stop

3) Install patch
cd `rpm -ql tfm-rubygem-katello | head -n 1`
patch -p1 < ~/Bz-1377725-1417642.patch

4) katello-service start

5) continue with normal operations

Comment 23 Satellite Program 2017-02-22 21:12:22 UTC
Moving this bug to POST for triage into Satellite 6 since the upstream issue http://projects.theforeman.org/issues/18376 has been resolved.

Comment 24 Pradeep Kumar Surisetty 2017-02-23 00:50:27 UTC
*** Bug 1425800 has been marked as a duplicate of this bug. ***

Comment 29 Pradeep Kumar Surisetty 2017-02-23 18:02:55 UTC
*** Bug 1417437 has been marked as a duplicate of this bug. ***

Comment 30 Satellite Program 2017-02-23 21:11:17 UTC
Please add verifications steps for this bug to help QE verify

Comment 31 Pradeep Kumar Surisetty 2017-02-24 01:32:21 UTC
Verification steps

1) Register hosts at large scale. 
2) content -> errata 
3) Select an errata which has large Content Host Counts -> Content Hosts
4) "Installable" checkbox checked

check how long it takes to list applicable hosts for the specific errata

--Pradeep

Comment 32 jcallaha 2017-04-20 14:30:46 UTC
Verified in Satellite 6.2.9 Snap 4

On a system with just under 6k content hosts, most of which had install-able errata, the page loaded in an average of 4.5s (10 load average). See attached screenshot for verification.

Comment 34 errata-xmlrpc 2017-05-01 13:58:21 UTC
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://access.redhat.com/errata/RHBA-2017:1191


Note You need to log in before you can comment on or make changes to this bug.