Red Hat Satellite engineering is moving the tracking of its product development work on Satellite to Red Hat Jira (issues.redhat.com). If you're a Red Hat customer, please continue to file support cases via the Red Hat customer portal. If you're not, please head to the "Satellite project" in Red Hat Jira and file new tickets here. Individual Bugzilla bugs will be migrated starting at the end of May. If you cannot log in to RH Jira, please consult article #7032570. That failing, please send an e-mail to the RH Jira admins at rh-issues@redhat.com to troubleshoot your issue as a user management inquiry. The email creates a ServiceNow ticket with Red Hat. Individual Bugzilla bugs that are migrated will be moved to status "CLOSED", resolution "MIGRATED", and set with "MigratedToJIRA" in "Keywords". The link to the successor Jira issue will be found under "Links", have a little "two-footprint" icon next to it, and direct you to the "Satellite project" in Red Hat Jira (issue links are of type "https://issues.redhat.com/browse/SAT-XXXX", where "X" is a digit). This same link will be available in a blue banner at the top of the page informing you that that bug has been migrated.
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 1426414 1435022
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:
Embargoed:


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

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.